Nested if problem

Closed
abidaidbr9 Posts 1 Registration date Tuesday October 8, 2013 Status Member Last seen October 8, 2013 - Oct 8, 2013 at 06:42 AM
Mazzaropi Posts 1978 Registration date Monday August 16, 2010 Status Contributor Last seen November 18, 2022 - Oct 8, 2013 at 07:14 AM
What is the problem in my formula
IF Salary is less than 5000 than house rent Salary*55% but not less than 2500
IF Salary is less than 5001-10800 than house rent Salary*50% but not less than 2800
IF Salary is less than 10801-21600 than house rent Salary*45% but not less than 5400
IF Salary is equal to or more 21601 than house rent Salary*40% but not less than 9700



=IF(B25<5000,MAX(2500,B25*55%),IF(10800>=B25>5000,MAX(2800,B25*50%),IF(21600>=B25>10800,MAX(5400,B25*45%),IF(B25>21600,MAX(9700,B25*40%),""))))

2 replies

venkat1926 Posts 1864 Registration date Sunday June 14, 2009 Status Contributor Last seen August 7, 2021 810
Oct 8, 2013 at 07:12 AM
quote
IF Salary is less than 5001-10800
unquote
this does not mean anything

you mean salaray >=5001 and <10800

rewrite you conditiona

if salary is entered in A1
try this formula

=IF(A1<5000,MAX(2500,A1*0.55),IF(AND(A1>5000,A1<=10800),MAX(2800,A1*0.5),IF(AND(A1>10600,A1<=21600),MAX(5400,A1*0.45),IF(A1>21600,MAX(9700,A1*0.4)))))

modify to suity where you entered salary
0
Mazzaropi Posts 1978 Registration date Monday August 16, 2010 Status Contributor Last seen November 18, 2022 146
Oct 8, 2013 at 07:14 AM
abidaidbr9, Good morning.

To answer exactly what you asked I suggest you use:

=IF(B25<5000,MAX(2500,B25*55%),IF(AND(10800>=B25,B25>5000),MAX(2800,B25*50%),IF(AND(21600>=B25,B25>10800),MAX(5400,B25*45%),IF(B25>21600,MAX(9700,B25*40%),""))))

BUT, if you create a table with your data, it will be easier and pratical when you need to change data or to know the rules.
In this case you will just use VLOOKUP function.
It's just a suggestion.

Tell us if it worked for you.

Greetings from Brazil.
Best regards.
0