IF function won't do want I want

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I'm trying to get an IF function to give me the choice of 3 different
percentage options from one cell. This is what I put in as my function in
cell BG37:
=IF(AA11<=30,S11*0.18,IF(AA11=31<90,S11*0.22,IF(AA11>90,S11*0.26)))

Where I want cell BG37 to multiply cell AA11 by 18% if cell S11 is < or = to
30
or
Cell BG37 to multiply cell AA11 by 22% if cell S11 is = to 31 but <90
lastly
Cell BG37 to multip;y cell AA11 by 26% if cell S11 > or = to 90

The way I have it, the 1st and 3rd function work but the second one shows
FALSE if cell S11 is between 31 and 89.
 
Assuming that the middle condition should be AA11>30 and <90, then:
=IF(AA11<=30,0.18,IF(AA11<90,0.22,0.26))*S11
You only need two IFs; the third condition (>=90) is selected when the
first two tests both fail.
 
Assuming that the middle condition should be AA11>30 and <90, then:
=IF(AA11<=30,0.18,IF(AA11<90,0.22,0.26))*S11
You only need two IFs; the third condition (>=90) is selected when the
first two tests both fail.
 
Loren said:
This is what I put in as my function in cell BG37:
=IF(AA11<=30,S11*0.18,IF(AA11=31<90,S11*0.22,IF(AA11>90,S11*0.26)))
Click to expand...

Write this:

=if(AA11<=30, S11*0.18, if(AA11<90, S11*0.22, S11*0.26))
Where I want cell BG37 to multiply cell AA11 by 18% if cell S11 is < or = to
30 or
Cell BG37 to multiply cell AA11 by 22% if cell S11 is = to 31 but <90
lastly
Cell BG37 to multip;y cell AA11 by 26% if cell S11 > or = to 90
Click to expand...

(I presume you got AA11 and S11 mixed up above. Your IF() logic says
that you want to multiply S11 by 0.18 if AA11 is < or = to 30, for
example.)

This is implied by the order in which Excel processes the IF()
conditions. If AA11>30 and fails the first condition, it is implicit
in the second condition that AA11>30 and AA11<90.
The way I have it, the 1st and 3rd function work but the second one shows
FALSE if cell S11 is between 31 and 89.
Click to expand...

Because you miswrote the "and" condition in the second IF(). Although
it is not necessary this time, if you ever want to write such a
condition in the future, it would be written AND(AA11>30,AA11<90).
 
Loren said:
This is what I put in as my function in cell BG37:
=IF(AA11<=30,S11*0.18,IF(AA11=31<90,S11*0.22,IF(AA11>90,S11*0.26)))
Click to expand...

Write this:

=if(AA11<=30, S11*0.18, if(AA11<90, S11*0.22, S11*0.26))
Where I want cell BG37 to multiply cell AA11 by 18% if cell S11 is < or = to
30 or
Cell BG37 to multiply cell AA11 by 22% if cell S11 is = to 31 but <90
lastly
Cell BG37 to multip;y cell AA11 by 26% if cell S11 > or = to 90
Click to expand...

(I presume you got AA11 and S11 mixed up above. Your IF() logic says
that you want to multiply S11 by 0.18 if AA11 is < or = to 30, for
example.)

This is implied by the order in which Excel processes the IF()
conditions. If AA11>30 and fails the first condition, it is implicit
in the second condition that AA11>30 and AA11<90.
The way I have it, the 1st and 3rd function work but the second one shows
FALSE if cell S11 is between 31 and 89.
Click to expand...

Because you miswrote the "and" condition in the second IF(). Although
it is not necessary this time, if you ever want to write such a
condition in the future, it would be written AND(AA11>30,AA11<90).
 
Back
Top