If Statement with range of numbers

[captain_kck]

I am trying to figure out how to execute an if statement with the following scenario:

I have a spreadsheet which calculates a percentage based on data input. I would like for Excel to decypher if the percentage falls between a range of <90 but >=80 it is 15%, <80 but >=70 it is 35%, <70 but >=60 is 55%, and <60 is 75%.

Any help with this would be great!

Thanks,

KCK

 

[barry houdini]

Hello captain kck, welcome to MrExcel,

If data is in A1 try this formula in B1

=IF(A1="","",IF(A1<60,75%,IF(A1<70,65%,IF(A1<80,35%,IF(A1<90,15%,0)))))

format B1 as %

 

[Richard Schollar]

Hi & Welcome to the Board!

You can use a Lookup for this (you need to know the lower bounds):

=LOOKUP(A1,{0,60,70,80,90},{75%,55%,35%,15%,"Out Of Bounds"})

 

[Diablo II]

you need conditional formatting.

go to tools down to conditional formatting.
then use help if you dont know how to use it.

 

[captain_kck]

Thanks for the very quick reply!

I entered the following to reflect the cells I'm using:

=IF(F25="","",IF(F25<60,75%,IF(F25<70,55%,IF(F25<80,35%,IF(F25<90,15%,0)))))

But when I have the percentage change (the data field) the number always remains 75% and doesn't change to reflect the ranges in the if statement. Do I have something wrong?

 

[DiscipleRyan]

Had to wrap my head around that one. Try this.

=IF(B2<60,"75%",IF(AND(B2<90,B2>=80),"15%",IF(AND(B2<80,B2>=70),"35%",IF(AND(B2<70,B2>=60),"55%","not"))))

 

[barry houdini]

But when I have the percentage change (the data field) the number always remains 75% and doesn't change to reflect the ranges in the if statement. Do I have something wrong?

If F25 values are percentages try

=IF(F25="","",IF(F25<60%,75%,IF(F25<70%,55%,IF(F25<80%,35%,IF(F25<90%,15%,0)))))

 

[captain_kck]

That did it...thanks a million!

KCK

 

[idr]

I tried your expressions; however, something doesn't work out for me (except column U which is straightforward). Here's what I want to do - create an IF statement for three columns reflecting ranges of numbers, and populate with the number from column R, that falls within that range):
Excel 2007<table rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6" cellpadding="2.5px"><colgroup><col style="background-color: #E0E0F0" width="25px"><col><col><col><col></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th>
</th><th>R</th><th>S</th><th>T</th><th>U</th></tr></thead><tbody><tr><td style="color: #161120;text-align: center;">1</td><td style=";">Depth Total
</td><td style=";">depth 18000-20000 ft</td><td style=";">depth 20000-22000 ft</td><td style=";">depth 24000+ ft</td></tr><tr><td style="color: #161120;text-align: center;">2</td><td style="text-align: right;;">19366</td><td style="text-align: right;;">19366</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td></tr><tr><td style="color: #161120;text-align: center;">3</td><td style="text-align: right;;">18850</td><td style="text-align: right;;">18850</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td></tr><tr><td style="color: #161120;text-align: center;">4</td><td style="text-align: right;;">18125</td><td style="text-align: right;;">18125</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td></tr></tbody></table>

 

[idr]

Sorry for the noise, but I think I figured it out, i.e. for column S:
=IF(AND($R$2>=18000,$R$2<20000),$R$2,"")

and so on, just change the wanted range of numbers.

Thanks.