help with nested if function

[blushikari]

Hi

Could someone help me with the basic concept of an IF function. I need help with a nested if function as I am getting used to them. Basically how do you create a formula in D1 where IF C1 is between 0 and 2.5 it adds 1, if C1 is between 2.5 and 5 it adds it by 5, If c1 is between 5 – 10 then it adds 10, if C1 is between 10 – 20 then it adds 15 and if over 20 then it adds 20 to the cell.

Any help would be much appreciated.

 

[petertenthije]

=C1+IF(C1<2,5;1;IF(C1<5;5;IF(C1<10;10;IF(C1<20;15;20))))


In my experience understanding a formula is easier if you add breaks between individual IF formulas by using alt-enter. In that case the formula becomes:

=C1+
IF(C1<2,5;1;
IF(C1<5;5;
IF(C1<10;10;
IF(C1<20;15;
20))))


Keep in mind that due to your language settings, you might have to replace the comma (",") that indicates the decimal point with a period ("."). Also, you may have to replace the semicolon (";") that divides the formulas with a comma (",").

 

[mole999]

IF generally return the 1st TRUE value, the first part of your needs looks like =IF(AND(C1>=0,C1<2.5),C1+1) you use the AND to allow you to set the criteria.

You then add the next set =IF(AND(C1>=0,C1<2.5),C1+1,IF(AND(C1>=2.5,C1<5),C1+5)) and build and test each stage

 

[Peter_SSs]

You can probably also do this without an IF at all. Depending on what happens if C1 is exactly on one of those changeover points (eg C1=5) you may be able to use something like this.

=C1+LOOKUP(C1,{0,2.5,5,10,20},{1,5,10,15,20})

 

[blushikari]

Hi all

Wow thanks very much. Really nicely explained by all. I am trying to get to grips with them. So thanks a lot.

 

[blushikari]

If I do the following however it says I have added to many arguments for this function. I would have thought this would work, what am I doing wrong



=IF(AND(Z3>=0,Z3<2.5),Z3+1,IF(AND(Z3>=2.5,Z3<5),Z3+3),if(and(z3>=5.0,z3<10),z3+7))

 

[mole999]

delete
=IF(AND(Z3>=0,Z3<2.5),Z3+1,IF(AND(Z3>=2.5,Z3<5),Z3+3),if(and(z3>=5.0,z3<10),z3+7))
add

=IF(AND(Z3>=0,Z3<2.5),Z3+1,IF(AND(Z3>=2.5,Z3<5),Z3+3,IF(AND(Z3>=5,Z3<10),Z3+7)))

 

[Peter_SSs]

If Z3 cannot be 10 or greater, you could also use my structure which is, in my view, easier to maintain/modify.

=Z3+LOOKUP(Z3,{0,2.5,5},{1,3,7})


If Z3 can be 10 or greater, what result do you want the formula to return?