Multiple if statements in one cell

  • Thread starter Thread starter a.g.
  • Start date Start date
A

a.g.

How many if statements i can use in single cell? one cell accepts only 8 if
statements. I want to assign conditions as given below:
Service Percentage
11 23
12 25
13 27
14 29
.. .
.. .

upto 30 years service I design three cells.
Year service Rate of Pension Amount
in year service user will enter 11, 12,13 etc. and the rate of pension will
automatically be calculated according to years, if 11 years service then 23%
etc.

Please help me.

specifically tell me how many if statements can be used in one cell and if i
want to check 20 conditions then is it possible?
 
If you have max 29 choices then

=CHOOSE(A1-10,23,25,27,29,30,31,32,33,34,35,36,37,38,39,40,41,42,43,44,45)

return percentages shown in your table.
Replace percentage numbers to your real values.

You can use also VLOOKUP if you store your table in a separate sheet.

IF function can be embedded up to 7 levels. If you have more than 7
conditions you have to choose some other solution but there is no recipe, the
solution depends on the actual task.

Regards,
Stefi

„a.g.†ezt írta:
 
7 nested IF statements is the limit. However, that can be expanded by
assigning the formula a name in the same way you would name a range. If you
write a seven condition IF formula and then name that formula "Frmla1", you
now have 1 formula that contains 7 IF statements. Now create a name for the
next 7 IF statements "Frmla2". These 2 named formulas can now be used within
another formula in the cell like so:
= If (Frmla1, Frmla1, Frmla2)
You are now effectively evaluating 14 conditions with 1 IF statement. You
could theoretically continue naming formulas for say a hundred names, but
the catch is trying to keep track of the logic so the Mega Formula works
properly.

Mike F
 
If the percentages increase by two for each year, then assuming the first
"Year Service" is located in A2) you could put this formula in B2 ("Rate of
Pension") and copy down...

=2*A2+1

Of course, if those percentages are not fixed forever, you will need one of
the other solutions posted here to handle the variability.
 
Thanks a lot for prompt response,please provide me solution of this problem
which i clearly write here:
"I have to design pension calculation work sheet in which one column
is"Years of Service" and "Pension rate." In which it is to check that if
"Years of Service=11" then show the pension rate of pension as 23" user will
only enter the year suppose Mr. X Years of service is 11 then the excel will
automatically give the rate of pension that is 23.

The solution provided may be right but i can not understand the =chose
function.

Please help me out, i have to submit my assignment.

Thank you.
 
How many if statements i can use in single cell? one cell accepts only 8 if
statements. I want to assign conditions as given below:
Service Percentage
11 23
12 25
13 27
14 29
. .
. .

upto 30 years service I design three cells.
Year service Rate of Pension Amount
in year service user will enter 11, 12,13 etc. and the rate of pension will
automatically be calculated according to years, if 11 years service then 23%
etc.

Please help me.

specifically tell me how many if statements can be used in one cell and if i
want to check 20 conditions then is it possible?
Click to expand...
 
Please help me to have the following set of arguments work; the last one isthe one that cannot make it work; when the cell is empty i want it to giveme a "in progress" message but it does not because the "Completed Early" argument takes over. =IF(F12=E12,"Completed",IF(F12>E12,"Completed late",IF(F12
 
Please help me to have the following set of arguments work; the last one is the one that cannot make it work; when the cell is empty i want it to give me a "in progress" message but it does not because the "Completed Early"argument takes over. =IF(F12=E12,"Completed",IF(F12>E12,"Completed late",IF(F12
Click to expand...

this is the complete set of arguments; the last post did not have it all:

=IF(F12=E12,"Completed",IF(F12>E12,"Completed late",IF(F12<E12,"Completed early",IF(F12,"","In progress"))))
 
How many if statements i can use in single cell? one cell accepts only 8 if
statements. I want to assign conditions as given below:
Service Percentage
11 23
12 25
13 27
14 29
. .
. .

upto 30 years service I design three cells.
Year service Rate of Pension Amount
in year service user will enter 11, 12,13 etc. and the rate of pension will
automatically be calculated according to years, if 11 years service then 23%
etc.

Please help me.

specifically tell me how many if statements can be used in one cell and if i
want to check 20 conditions then is it possible?
Click to expand...

How about
=(B16*2)+1
11*2=22+1=23
 
Back
Top