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:

#Days	TestB	TestC	TestD	Result
35	48	48	34	TRUE	Reports True because 35 is greater than Test D
35	48	34	48	FALSE	Reports False because formula stops after verifying Test D
35	48	34		TRUE	Reports True becauseTest D is blank and 35 is greater than Test B

<colgroup><col span="5"><col></colgroup><tbody>
</tbody>

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.