Thread
:
IF FUNCTION
View Single Post
#
7
Posted to microsoft.public.excel.programming
Norman Harker
external usenet poster
Posts: 162
IF FUNCTION
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
Reply With Quote
Norman Harker
View Public Profile
Find all posts by Norman Harker