hi

Please could someone advise what is wrong with this formula:

=IF(B26<=0,"0%",IF(B26=1<=4,"5%",IF(B26<=4,"5%",I F(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

Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
"vee" wrote in message
om...
hi

Please could someone advise what is wrong with this formula:


=IF(B26<=0,"0%",IF(B26=1<=4,"5%",IF(B26<=4,"5%",I F(B26=5<=14,"25%",I
F(B26<=14,"25%",IF(B26=15<=24,"50%",IF(B26<=24,"5 0%",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

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

-----Original Message-----
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,

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

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

Firstly, you don't test and AND condition like B26=1<=4 but as
AND(B260,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(B260,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(B264,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)

"vee" wrote in message
om...
hi

Please could someone advise what is wrong with this formula:


=IF(B26<=0,"0%",IF(B26=1<=4,"5%",IF(B26<=4,"5%",I F(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

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" wrote in message
...
Hi Vasen,

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


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

Firstly, you don't test and AND condition like B26=1<=4 but as
AND(B260,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(B260,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(B264,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)

"vee" wrote in message
om...
hi

Please could someone advise what is wrong with this formula:



=IF(B26<=0,"0%",IF(B26=1<=4,"5%",IF(B26<=4,"5%",I F(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

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)

"Lorne" wrote in message
...
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" wrote in message
...
Hi Vasen,

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



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

Firstly, you don't test and AND condition like B26=1<=4 but as
AND(B260,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(B260,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(B264,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)

"vee" wrote in message
om...
hi

Please could someone advise what is wrong with this formula:




=IF(B26<=0,"0%",IF(B26=1<=4,"5%",IF(B26<=4,"5%",I F(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 Bob!

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

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia

Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
"Bob Phillips" wrote in message
...
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)

"Lorne" wrote in message
...
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" wrote in
message
...
Hi Vasen,

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




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

Firstly, you don't test and AND condition like B26=1<=4 but as
AND(B260,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(B260,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(B264,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)

"vee" wrote in message
om...
hi

Please could someone advise what is wrong with this formula:





=IF(B26<=0,"0%",IF(B26=1<=4,"5%",IF(B26<=4,"5%",I F(B26=5<=14,"25%",I
F(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