Nested If Formula With Multiple Conditions

[lprc]

Hello!
Please, can someone tell me how can I make formula for:
If D2 ; E2 ; F2 >= 4 and G2 >=5 , than H2 = 18, but IF D2 ; E2 ; F2 >= 4 and G2 >=7 , that H2 = 27. If any of these numbers are less than the condition, than H2 = 0 .

That would be unspeakably helpful for me ! :)

[LalitPandey87]

Try this formula:

=IF(AND(D2>=4,E2>=4,F2>=4),IF(AND(G2>=5,G2<7),18,IF(G2>=7,27,0)),0)

:cheers:

[Excel Fox]

Try this


=IF(AND(D2>=4,E2>=4,F2>=4,G2>=7),27,IF(AND(D2>=4,E2>=4,F2>=4,G2>=5),18,0))

[LalitPandey87]

Hi Excel Fox,

I have tried this formula and it's giving only one result in multiple test which is 18. I think because of G2>=5 and G2>=7 conditons. Correct me if i am wrong.

[Excel Fox]

Lalit, corrected it. It was because I used the smaller valued conditional first, rather than the larger one. Swapped it :)

[LalitPandey87]

Another try:

=IF(AND(D2>=4,E2>=4,F2>=4),IF(G2>=7,27,IF(G2>=5,18,0)),0)

[Rick Rothstein]

Another (slightly shorter) formula that appears to work...

=IF(OR(COUNTIF(D2:F2,"<4"),G2<5),0,18+9*(G2>=7))

[lprc]

None of these formulas are working for me . :( , but thanks anyways. :)

[LalitPandey87]

Formula given by me and by experts are based on what you have asked.
If it is not working then why don't share your file for better clearity.

[lprc]

Here is the file.

[Rick Rothstein]

Here is the file.

None of these formulas are working for me . :( , but thanks anyways. :)
I posted this formula earlier...

=IF(OR(COUNTIF(D2:F2,"<4"),G2<5),0,18+9*(G2>=7))

Please describe in what way it is not working for you.

Also, I notice you have the letter 'n' appearing at some locations within Columns D:F... how did you want the formula to handle them (being they are not numbers)... Ignore them? Treat them as if they were 0? Something else?