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

 

[BSWJON]

Hi everyone,

Just trying to attempt a similar formula. I have values in column U. I require the formula to enter:

"4" for column U values >=23 to <=26
"3" for column U values <=22

Column B either states K1 or K2, with this in mind:

If column B= "K1" and U= >=28, = "3"

I have tried this formula:

=IF(U2<=22,"3",IF(AND(U2>=23,U2<=26),"4",IF(AND(U2>=28,B2="K1),"3")))

However I get the infamous error message and it highlights the final "3".

Does anyone have any suggestions?

Cheers

Jon

 

[kvsrinivasamurthy]

Choose suitable formula
=IF(U2<=22,"3",IF(AND(U2>=23,U2<=26),"4",IF(AND(U2>=28,B2="K1"),"3")))

=IF(U2<=22,3,IF(AND(U2>=23,U2<=26),4,IF(AND(U2>=28,B2="K1"),3)))

 

[BSWJON]

Choose suitable formula
=IF(U2<=22,"3",IF(AND(U2>=23,U2<=26),"4",IF(AND(U2>=28,B2="K1"),"3")))

=IF(U2<=22,3,IF(AND(U2>=23,U2<=26),4,IF(AND(U2>=28,B2="K1"),3)))

Thanks for that, it works for everything but the very end whereby it says False for anything where U: >=28...

 

[kvsrinivasamurthy]

What is the expected value for U>28

 

[BSWJON]

3

 

[BSWJON]

seemingly behaving but I need to add an extra clause whereby if B="K2",AB>=3600,V>-27, value = 4. It doesn't like my formula though (addition is at the end):

=IF(V2300<=22,3,IF(AND(V2300>=23,V2300<=26,AB2300>=3600),4,IF(AND(V2300>=23,V2300<=27,AB2300<=3200),3,IF(AND(V2300>=28,B2300="K1"),3)IF(AND(V2300>=27,B2300="K2",AB>=3600),4))))

Does anyone have any suggestions?

 

[krzalmeria]

I tried the formula above but it does not work. I want to return the value of the following for the range below:
100% for scores 95%-100%
80% for scores 90%-94%
70% for scores 85%-89%
60% for scores 81%-84%
50% for scores below 80%

 

[BSWJON]

I tried the formula above but it does not work. I want to return the value of the following for the range below:
100% for scores 95%-100%
80% for scores 90%-94%
70% for scores 85%-89%
60% for scores 81%-84%
50% for scores below 80%

Which formula did you try? the one at the top of the thread should work for what you need if you adapt it to have correct cell values. I.e if score is in cell A1:

=IF(A1<=80,50,IF(AND(A1>=81,A1<=84),60,IF(AND(A1>=85,A1<=89),70,IF(AND(A1>=90,A1<=94),80,IF(AND(A1>=95,A1<=100),100)))))

Try this, I often have to go through a process of trial and error with formulas though, so let us know if this one doesn't work.

 

[krzalmeria]

Hi, i tried the formula above and the formula just yielded a constant answer of 50%

 

[BSWJON]

Hi, i tried the formula above and the formula just yielded a constant answer of 50%

My apologies. This weblink explains it better than I can:

MS Excel: How to use the Nested IF Functions (WS)