IF FUNCTION

  • Thread starter Thread starter vee
  • Start date Start date
V

vee

hi

Please could someone advise what is wrong with this formula:

=IF(B26<=0,"0%",IF(B26>=1<=4,"5%",IF(B26<=4,"5%",IF(B26>=5<=14,"25%",IF(B26<=14,"25%",IF(B26>=15<=24,"50%",IF(B26<=24,"50%",IF(B26>=25,"75%"))))))))


The problem is if B26<0 it works...but if B26=0 it evaluates to 5% not
to 0% as it should according to the formula.


Thanks
Vasen
 
Hi Vasen!

It's working OK for me, although I can't see why you're not using
VLOOKUP. Also, you haven't covered empty cell or text inputs to B26


--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
vee said:
hi

Please could someone advise what is wrong with this formula:
Click to expand...
=IF(B26<=0,"0%",IF(B26>=1<=4,"5%",IF(B26<=4,"5%",IF(B26>=5<=14,"25%",I
 
Breaking this down to a UDF shows your IF is wat=y too
complex:

Function Percents(dValue As Double) As String

Dim sReturn As String

Select Case dValue
Case Is <= 0: sReturn = "0%"
Case Is <= 4: sReturn = "5%"
Case Is <= 14: sReturn = "25%"
Case Is <= 24: sReturn = "50%"
Case Else: sReturn = "75%"
End Select

Percents = sReturn

End Function



IF(B26<=0,"0%,IF(B26<=4,"5%,IF(B26<=14,"25%",IF
(B26<=24,"50%","75%"))))


Patrick Molloy
Microsoft Excel MVP
 
Hi Vasen,

Your formula has a lot of oddities and can be corrected and simplified with

=IF(B26<=0,0%,IF(AND(B26>0,B26<=4),5%,IF(AND(B26>4,B26<=14),25%,IF(AND(B26>1
4,B26<=24),50%,75%))))

Firstly, you don't test and AND condition like B26>=1<=4 but as
AND(B26>0,B26<=4)
The, percentage is a value so you don't need it within quotes, just format
the cell as percentage to see 5% rather than 0.05
You don't test for values between 0 and 1 (and others), so I assume this
means that it can on ly be whole numbers, so tests like
IF(B26<=0,0%,IF(AND(B26>=1,B26<=4 are better covered by
IF(B26<=0,0%,IF(AND(B26>0,B26<=4 to catch all instances and to make more
readable

Because it seems to work only on whole numbers you could also use CHOOSE

=IF(B26>4,75%,CHOOSE(B26+1,0%,5%,25%,50%))

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
I believe you can simplify this even more. You do not need the AND
statements at all since the previous IF would have dealt with it if the
lower value in the AND was releveant, hence:

=IF(B26<=0,0%,IF(B26<=4,5%,IF(B26<=14,25%,IF(B26<=24),50%,75%))))

should work (assuming B26 is always numeric).


Bob Phillips said:
Hi Vasen,

Your formula has a lot of oddities and can be corrected and simplified with
Click to expand...
 
I agree, but I didn't go down that path because I wanted to espouse the
CHOOSE option , which I think is better.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
Hi Bob!

And I was shocked and stunned to find that it worked <vbg>

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
 
Back
Top