Formula Help: multiple cells with a < or > then Return statement

[ldavis1227]

I want to know if any of the values in (I1, J1, K1) is (< then L1) return "Yes", if (> then L1) return "No".

I1 = .02
J1 = .08
K1 = 1.13
L1 = 0

Of note: each of these cell have a formula behind them also and the value will change.

 

[Jonmo1]

Correct the formula first, to have < instead of >
And repost it.

Based on the formula that was posted, I'd say NO, they would BOTH return "Yes"
Because in each row the AND is FALSE because not ALL 3 cells are >= the value in D

 

[ldavis1227]

Sheet1

	A	B	C	D	E
1	TAT for F3	TAT for G3	TAT for H3	TAT Req	Issue with TAT?
2	1	4	12	14	No
3	4	9	31	30	No

<colgroup><col style="width: 30px;"><col style="width: 64px;"><col style="width: 64px;"><col style="width: 64px;"><col style="width: 64px;"><col style="width: 64px;"></colgroup><tbody>
</tbody>

Spreadsheet Formulas

Cell Formula E2 =IF(AND(A1 <= D1, B1 <= D1, C1 <= D1), "No", "Yes") E3 =IF(AND(A2 <= D2, B2 <= D2, C2 <= D2), "No", "Yes") <tbody> </tbody>

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

 

[Jonmo1]

The formula in E2 is referring to the cells in Row 1 (A1 B1 C1 and D1)
Those cells contain your text strings "TAT for F3" and "TAT for G3" etc..

So E2 is just non sensicle.

E2 should be
=IF(AND(A2 <= D2, B2 <= D2, C2 <= D2), "No", "Yes")

E3 should be
=IF(AND(A3 <= D3, B3 <= D3, C3 <= D3), "No", "Yes")

 

[ldavis1227]

I would like the formula to tell if any of them are greater than the TAT requirement time. Maybe it's not possible to do what I'm looking for.

There is a possibility that all A:C fields will have data or just one.

 

[ldavis1227]

Thanks for trying to help but I'm giving up. It's too confusing and every time I try to fix the formula I type it wrong. I'm obviously off by a row and didn't catch it. I just needed to figure out how to automate this by close of business and I'm just going to have to tell them I can't figure it out in time.

At this point, I'm just wasting your time and I apologize.

 

[Jonmo1]

Don't give up.

It's just a matter of getting an accurate sample, and a clear explaination of what you want.

I think this will get what you're after.
In E2
=IF(MAX(A2:C2)<=D2,"No","Yes")

Excel Workbook
	A	B	C	D	E
1	TAT for F3	TAT for G3	TAT for H3	TAT Req	Issue with TAT?
2	1	4	12	14	No
3	4	9	31	30	Yes
Sheet2

 

[ldavis1227]

Thanks for not giving up on me and your tremendous amount of patience. I'm going to try it out and I'll let you know if that works!

 

[Jonmo1]

Glad to help, definately don't give up.

It CAN be done.

Again, it's just a matter of giving an accurate sample and a 'clear' definition of your expectations.
Stop switching between > or <, and "All" or "Any"
It can be done with any combination of those, we just need a final choice to be made.
Just describe in words what results you expect to see and why based on an accurate sample provided.

So if this latest effort still doesn't work.
Don't give up.
Take a deep breath
Walk around the office or something and then come back and try again.

 

[ldavis1227]

your formula worked in my test spreadsheet. Here is the Master I'm working on.

UR & Letter Review

	C	D	E	F	G	H	I	J	K	L	M
3	Date UR Request Received	Type of UR Request*	Type of Service Request; e.g., IP=Inpatient IOP=Intensive OP OP=Outpatient CTSS CD Res.	Date of Determination (If DTR, psychiatrist determination required)	Date Verbal &/or Written Notice to Provider (If DTR, verbal w/in one BD & written notice required)	Date Verbal (if IP) and/or Written Notice to Member	TAT for F3	TAT for G3	TAT for H3	TAT Req	Issue with TAT?
4	6/1/14	1			6/1/14	6/2/2014		0	1	0	No
5	6/1/14 8:00	2		6/5/14 8:30			4			3	No
6	6/1/14 0:00	3			6/5/14 0:00			4		3	No
7	6/1/14 0:00	4		6/2/14 0:00	6/5/14 0:00	6/13/2014	1	4	12	14	No
8	6/1/14 0:00	5		6/5/14 0:00	6/10/14 0:00	7/2/2014	4	9	31	30	No

<colgroup><col style="width: 30px;"><col style="width: 67px;"><col style="width: 73px;"><col style="width: 93px;"><col style="width: 73px;"><col style="width: 84px;"><col style="width: 76px;"><col style="width: 56px;"><col style="width: 56px;"><col style="width: 56px;"><col style="width: 45px;"><col style="width: 45px;"></colgroup><tbody>
</tbody>

Spreadsheet Formulas

Cell Formula I4 =IF(ISBLANK(F4)=TRUE, "",F4-C4) J4 =IF(ISBLANK(G4)=TRUE, "",G4-C4) K4 =IF(ISBLANK(H4)=TRUE, "",H4-C4) L4 =LOOKUP(D4,{1,2,3,4,5},{"0","3","3","14","30"}) M4 =IF(MAX(I4:K4)<=L4,"No","Yes") I5 =IF(ISBLANK(F5)=TRUE, "",F5-C5) J5 =IF(ISBLANK(G5)=TRUE, "",G5-C5) K5 =IF(ISBLANK(H5)=TRUE, "",H5-C5) L5 =LOOKUP(D5,{1,2,3,4,5},{"0","3","3","14","30"}) M5 =IF(MAX(I5:K5)<=L5,"No","Yes") I6 =IF(ISBLANK(F6)=TRUE, "",F6-C6) J6 =IF(ISBLANK(G6)=TRUE, "",G6-C6) K6 =IF(ISBLANK(H6)=TRUE, "",H6-C6) L6 =LOOKUP(D6,{1,2,3,4,5},{"0","3","3","14","30"}) M6 =IF(MAX(I6:K6)<=L6,"No","Yes") I7 =IF(ISBLANK(F7)=TRUE, "",F7-C7) J7 =IF(ISBLANK(G7)=TRUE, "",G7-C7) K7 =IF(ISBLANK(H7)=TRUE, "",H7-C7) L7 =LOOKUP(D7,{1,2,3,4,5},{"0","3","3","14","30"}) M7 =IF(MAX(I7:K7)<=L7,"No","Yes") I8 =IF(ISBLANK(F8)=TRUE, "",F8-C8) J8 =IF(ISBLANK(G8)=TRUE, "",G8-C8) K8 =IF(ISBLANK(H8)=TRUE, "",H8-C8) L8 =LOOKUP(D8,{1,2,3,4,5},{"0","3","3","14","30"}) M8 =IF(MAX(I8:K8)<=L8,"No","Yes") <tbody> </tbody>

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

Based on the type of request (D3) there are defined TAT requirements (pre-defined in L3 in days). The 0 is an actual TAT of 24 hours and the 3 is 72 hours. The rest were in days, which is why I converted them from hours to days.
Based on the type of request (D3) they may have a date to enter or they may leave it blank (F3:H3)
I need to track when TAT's are not met based on L3

I hope that explains better what I'm trying to accomplish.

 

[Jonmo1]

There we go, here's the root of the problem

The formula in L is NOT returning a number.
=LOOKUP(D4,{1,2,3,4,5},{"0","3","3","14","30"})
The quotes around those numbers make the result a TEXT string.

So for example in Row 5, 4 IS greater than the NUMBER 3, but it is NOT greater than the TEXT String "3"

Remove all those quotes in the lookup formula
=LOOKUP(D4,{1,2,3,4,5},{0,3,3,14,30})