LinkBack URL
About LinkBacks
Hi,
i wana defina a formula with respect to the below condition, i want all these conditions and results in one formula,..
IS IT POSSIBLE..???!!!
Conditions | Value
<15 | 0
>15&<30 | 5
>30&<50 | 10
>=50% | 15
Hi,
i wana defina a formula with respect to the below condition, i want all these conditions and results in one formula,..
IS IT POSSIBLE..???!!!
Conditions | Value
<15 | 0
>15&<30 | 5
>30&<50 | 10
>=50% | 15
you can either do this with a table and do index/match or you can do nested if (or boolean expressions doing the same thing (some people rather do that as nested ifs get confusing sometimes)
ill post a book with all the different ways shortly
The Importance of INDEX - A GUIDE TO INDEX'S OTHER USES
<--- If a post helps hit the star
keep me posted about the book too
Pavan Shetty
pavanshetty.sp@live.in
here you go, there are other ways also.
edit i forgot one i have just added it to the book now
examples.xlsx
imo the best is the index without array
Last edited by twiggywales; 09-10-2012 at 04:44 AM.
Perfect..
Tnx Bro..
no problem remember to mark it solved and if you feel i helped a little star tap is always appreciated
twiggywales gave this as a nested IF way of doing it:
=IF(D4<=15,0,IF(AND(D4>15,D4<=30),5,IF(AND(D4>30,D4<=50),10,15)))
but it is not really necessary to check for ranges as long as you do the checks in order, so you could do it like this:
=IF(D4<15,0,IF(D4<30,5,IF(D4<50,10,15)))
and if your D4 actually contains percentage values, then you will have to divide the comparators by 100:
=IF(D4<0.15,0,IF(D4<0.30,5,IF(D4<0.50,10,15)))
Hope this helps.
Pete
good catch pete. i didnt think of that. i still think that the non array index is best but thanks
@Pete:
Nice one dude, it worked, also for the persent ranges i used like below,
=IF(D4<15%,0,IF(D4<30%,5,IF(D4<50%,10,15)))
but i hava a doube;
doesnt the above formula give a miscal,
like for eg, if i have a value 12, it lies below 15 but also lies below 50. so doesnt the equation takes the value directly as below 50 and past result..??
No. The formula sees that 12% is less than 15% so it returns the TRUE part of that function (i.e. 0) and doesn't bother to evaluate the later part of the formula.
Pete
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks