Multiple IF statments?

**gballard** · 02-28-2013, 07:53 PM

Is there a way to write a single formula that will take into account multiple rules? Basically, say I have a value in cell A1 that is 17% and I want to write a formula that takes into account the following things:

If A1 is >80% = 0
If A1 is between 50-79.99% = 25
If A1 is between 25-49.99% = 50
If A1 is between 0-24.99 = 75%

So the formula would produce the number 75%, since the value in A1 is 17%.

Is there a way to write that into one formula, perhaps with multiple IF and OR statements? I'm not really sure how I can string those together. Any help is much appreciated.

**TMS** · 02-28-2013, 07:59 PM

If A1 is >80% = 0
If A1 is between 50-79.99% = 25
If A1 is between 25-49.99% = 50
If A1 is between 0-24.99 = 75%

Try:

=IF(A1>80%,0,IF(A1>=50%,25,IF(A1>=25%,50,75)))

Regards, TMS

**alansidman** · 02-28-2013, 08:01 PM

Try this:

=if(and(A1>0,A1<.25),.75,if(and(A1>=.25,A1<.5),.5,if(A1>=.5,A1<=.8),.25,0)))

**gballard** · 02-28-2013, 08:22 PM

I keep getting the "You've entered too many arguments for this function" error, and then it highlights the last number in the formula. Do you have any idea why that might be?

**TMS** · 02-28-2013, 08:28 PM

For which formula? They both do much the same, just in a slightly different way.

Regards, TMS

**TMS** · 02-28-2013, 08:29 PM

Probably the second one:

=if(and(A1>0,A1<.25),.75,if(and(A1>=.25,A1<.5),.5,if(AND(A1>=.5,A1<=.8),.25,0)))

Regards, TMS

**gballard** · 02-28-2013, 08:43 PM

You guys rock. Thanks a bunch.

**alansidman** · 02-28-2013, 08:44 PM

Thanks Trevor. Missed the last And operation.

Alan

**TMS** · 02-28-2013, 08:54 PM

@Alan: no problem. It's good practice playing spot the deliberate mistake

@gballard: you're welcome.

If you are satisfied with the solution(s) provided, please mark your thread as Solved.

New quick method:
Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

Or you can use this way:

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save

You may also want to consider thanking those people who helped you by clicking on the little star at the bottom left of their reply to your question.