Nested IF is not working correctly.

[nihar sharma]

Pay the house rent, 55% of salary between 5000 to 4100 and 2250 minimum, 50% between 5001 to 10800 and 2500 minimum, 45% between 10801 to 21600 and 4800 minimum, 40% of 21601 to above and 8500 minimum. I've used Nested if function like: "=IF(AND(a2>4100,a2<5000),a2*55%,IF(AND(a2*55%<2500),2500,IF(AND(a2>5001,a2<10800),a2*50%,IF(AND(a2*50%<2800),2800,IF(AND(a2>10801,a2<21600),a2*45%,IF(AND(a2*45%<5400),5400,IF(a2>21601,a2*40%,9700))))))))"

1st part of the function "=IF(AND(a2>4100,a2<5000),a2*55%,IF(AND(a2*55%<2500),2500" is working but other parts are not.

Can anybody solve this problem.

September-14.xls

Nihar Sharma

[Speshul]

Still looking at it, for for anyone else reading here it is in a more broken down view

=IF(AND(A2>4100,A2<5000),A2*55%,
IF(AND(A2*55%<2500),
2500,
IF(AND(A2>5001,A2<10800),
A2*50%,
IF(AND(A2*50%<2800),
2800,
IF(AND(A2>10801,A2<21600),
A2*45%,
IF(AND(A2*45%<5400),
5400,
IF(A2>21601,A2*40%,9700)))))))

[ben_hensel]

Since you're stepping up through the brackets, the ANDs are logically redundant.

Well, at any rate, I advise going through a lookup table rather than burying it all in IFs.

[Speshul]

=IF(AND(A2>4100,A2<5000),A2*55%,
IF(AND(A2*55%<2500),
2500,
IF(AND(A2>5001,A2<10800),
A2*50%,
IF(AND(A2*50%<2800),
2800,
IF(AND(A2>10801,A2<21600),
A2*45%,
IF(AND(A2*45%<5400),
5400,
IF(A2>21601,A2*40%,9700)))))))

If it is NOT greater than 21,601, it will not be 9700 because it is fitting into the criteria for GREEN
RED doesn't need to be an AND since there is only one expression

The 9700 will never happen, unless something between 21,600 or 21,601 is entered into column A

Solution, Less/greater Than or Equal To. And a couple less AND's to shorten it a bit

=IF(AND(A2>=4100,A2<=5000),A2*55%,
IF(A2*55%<=2500,
2500,
IF(AND(A2>=5001,A2<=10800),
A2*50%,
IF(A2*50%<=2800,
2800,
IF(AND(A2>=10801,A2<=21600),
A2*45%,
IF(A2*45%<=5400,
5400,
IF(A2>=21601,A2*40%,9700)))))))

Flattened:

=IF(AND(A2>=4100,A2<=5000),A2*55%,IF(A2*55%<=2500,2500,IF(AND(A2>=5001,A2<=10800),A2*50%,IF(A2*50%<=2800,2800,IF(AND(A2>=10801,A2<=21600),A2*45%,IF(A2*45%<=5400,5400,IF(A2>=21601,A2*40%,9700)))))))

[kvsrinivasamurthy]

This will satisfy all the conditions.

Please Login or Register  to view this content.

Minimum condition is not necesary.One example here
Eg;For A2 >5100 and <10800 , Minimum told is 2500.
For 5100, 5100*50%=2500.
Test for different values.
I feel i am clear in explanation.

[nihar sharma]

This was? It works. Thank you.