CODEWITHSUNDEEP
excelif-statementvba
JSDave
JSDave

Reputation: 3

Display Value in cell Y based on greater than, less than of cell X

Here's the scenario. I have a large spreadsheet of candidates for NHS at my school that are given a score by several teachers, community members, etc. I average out their score and then based on that number they are given a score/value from a rubric. I am looking for a formula that will read the value of cell X (their average score) and display a specific value in cell Y(their rubric score). The following is the criteria:

value<2.0, display 0

value>2.0 value<3.0, display 1

value>3.0 value<3.5, display 2

value>3.5 value<3.75, display 3

value>3.75, display 4

I tried looking this up and the closest I found was a formula that I modified to look like this:

=IF(I10="AVERAGE_CHARACTER",IF(I10<2,0,IF(AND(I10>2,I11<3),1,IF(AND(I10>3,I11<3.5),2,IF(AND(I10>3.5,I11<3,75),3,IF(I11>3.75,4,0))))))

All it says is FALSE in the cell. Not sure if I'm using the wrong formula or have a typo in the formula. Thoughts? If there is an alternate or easier method, I'm open for suggestions.

Thanks!

source: http://www.excelforum.com/excel-formulas-and-functions/575953-greater-than-x-but-less-than-y.html

Upvotes: 0

Views: 1013

Answers (2)

Excel Hero
Excel Hero

Reputation: 14764

It's easy if you keep the thresholds and the rubric in separate arrays:

=LOOKUP(A1,{0,2,3,3.5,3.75},{0,1,2,3,4})

Upvotes: 1

Dirk Reichel
Dirk Reichel

Reputation: 7979

You might use something like: (value to be changed in A1)

=VLOOKUP(A1,{0,0;2,1;3,2;3.5,3;3.75,4},2)

or having a table like this: (value to be changed in C1)

  |   A   |   B   |
1 |   0   |   0   |
2 |   2   |   1   |
3 |   3   |   2   |
4 |  3.5  |   3   |
5 |  3.75 |   4   |

=VLOOKUP(C1,A1:B5,2)

Upvotes: 1

Related Questions