Multiple If statements based on multiple criteria

[EJohnson99202]

Hello all -

I have a statement that has be stumped. I am looking for a true condition based on multiple criteria, however, the formula needs to stop at the first result if true.

I have four columns. Column A is Number of Days. Column B, C and D and Number of Test Days. What I need is for Column A to check against Columns B - D (starting with D) and stopping if a value is filled. Here are examples:

[TABLE="width: 735"]
<colgroup><col span="5"><col></colgroup><tbody>[TR]
[TD]#Days[/TD]
[TD]TestB[/TD]
[TD]TestC[/TD]
[TD]TestD[/TD]
[TD]Result[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]35[/TD]
[TD]48[/TD]
[TD]48[/TD]
[TD]34[/TD]
[TD]TRUE[/TD]
[TD]Reports True because 35 is greater than Test D[/TD]
[/TR]
[TR]
[TD]35[/TD]
[TD]48[/TD]
[TD]34[/TD]
[TD]48[/TD]
[TD]FALSE[/TD]
[TD]Reports False because formula stops after verifying Test D[/TD]
[/TR]
[TR]
[TD]35[/TD]
[TD]48[/TD]
[TD]34[/TD]
[TD][/TD]
[TD]TRUE[/TD]
[TD]Reports True becauseTest D is blank and 35 is greater than Test B[/TD]
[/TR]
</tbody>[/TABLE]


This is the formula I am using:

=IF(OR(D2="",C2="",A2>B2),"1",IF(C2="","",IF(A2>C2,"1",IF(D2="","",IF(A2>D2,"1","")))))


Thank you in advance!!

 

[JackDanIce]

Try:

=$A1>OFFSET($A1,0,COUNTA($B1:$D1))

 

[EJohnson99202]

Sorry, using =IF(AND(D2="",C2="",A2>B2),"1",IF(C2="","",IF(A2>C2,"1",IF(D2="","",IF(A2>D2,"1","")))))

 

[EJohnson99202]

Try:

=$A1>OFFSET($A1,0,COUNTA($B1:$D1))

Thank you for the reply but that does not work. I still get False if A is less than D. In this situation, True only occurs is A is less than B.

 

[JackDanIce]

For the example you gave, in E1, I get TRUE for row 1 (A1 = 35, D1 =34), FALSE for row 2 and TRUE for row 3 using suggested formula.

Changing A1 to 30 or D1 to 37 both return FALSE

 

[EJohnson99202]

Based on the formula I posted:

=IF(OR(D2="",C2="",A2>B2),"1",IF(C2="","",IF(A2>C2,"1",IF(D2="","",IF(A2>D2,"1","")))))

I am getting True for all three rows. Row 2, with Test D at 48, should report False as it is less than Column A of 35.

 

[admiral100]

Hi,

Maybe this : =IF(D2,A2>D2,IF(C2,A2>C2,IF(B2,A2>B2,FALSE)))

 

[EJohnson99202]

You sir, are amazing!! Thank you!!

 

[admiral100]

You are welcome , thanks for the feedback.