Return single number value based on range

JGrieve

New Member
Joined
Nov 7, 2019
Messages
2
Heya, im barely great at Excel, and im struggling to develop a formula.

I have one sheet that has a Numeric result in ONE cell - S2
The number is ANY value, with ANY decimal point value

I need to put a formula in another cell that defines a grade 1-5 based on its value S2's value.
*Each Peak / Mean score between the values below, get assigned the corresponding grade on the far left*

Im sure its an =IF(AND( kind of formula, but cannot get it to work.
Im looking for a single formula that will check all 5 grades parameters, and return the single grade number.


GradePeakMean
100
22.10.51
315.10.91
430.11.71
5503

<tbody>
</tbody>
 

Some videos you may like

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().

Special-K99

Well-known Member
Joined
Nov 7, 2006
Messages
8,342
With your example table in Sheet2 and Grade in A1

=INDEX(Sheet2!A$2:A$5,MATCH(S2,Sheet2!B$2:B$5,0),1)

or
=LOOKUP(S2,{0,2.1,15.1,30.1,50},{1,2,3,4,5})
 

JGrieve

New Member
Joined
Nov 7, 2019
Messages
2
With your example table in Sheet2 and Grade in A1

=INDEX(Sheet2!A$2:A$5,MATCH(S2,Sheet2!B$2:B$5,0),1)

or
=LOOKUP(S2,{0,2.1,15.1,30.1,50},{1,2,3,4,5})


I think the bottom one appears to work,
Using the lookup on the i did some sample scores in, and it appears to be returning the correct result.

Could you explain how the lookup works,
Like, How is that formula knowing that when the score fits between two of those numbers to return the correct grade.

Dont get me wrong, i am VERY grateful for your help, and thank you very much,
I would just like to learn how its doing it - cause at this stage, its quite close to Magic in my mind hahaha.

ScoreGrade resultGradePeakMean
01100
2339.35522.10.51
112315.10.91
314430.11.71
99955503
1.91
2.252

<colgroup><col><col><col span="4"></colgroup><tbody>
</tbody>
 

Special-K99

Well-known Member
Joined
Nov 7, 2006
Messages
8,342
They call this use of { } brackets an inline array and you're right, the LOOKUP matches S2 against the first set of brackets
{0,2.1,15,1,30.1,50} and this are regarded as ranges 0-2, 2.1-14.9999 etc
The number returned is the appropriate number from the second set of brackets
So if you had

LOOKUP(S2,{10,20,30,40,50},{6,7,8,9,10})
and S2 was 43 the result would be 9 because 43 lies between 40 and 50 the lowest of the two positions is returned, ie the 4th number. So the 4th number in the 2nd set is returned, ie 9.
There's not a lot of references to inline arrays on the net, I've picked up most of this from the forums.

Drawback with this approach is if the number ranges change then you need to amend the formula.
Also you need to cater for the minimum or you'll get an error.
For example if S2 was 5 in the above example, you would get an error cos the minimum number in the first set opf brackets is 10 or higher to match.
 

Watch MrExcel Video

Forum statistics

Threads
1,102,872
Messages
5,489,423
Members
407,687
Latest member
NeoSez

This Week's Hot Topics

  • Timer in VBA - Stop, Start, Pause and Reset
    [CODE=vba][/CODE] Option Explicit Dim CmdStop As Boolean Dim Paused As Boolean Dim Start Dim TimerValue As Date Dim pausedTime As Date Sub...
  • how to updates multiple rows in muliselect listbox
    Hello everyone. I need help with below code. code is only chaning 1st row in mulitiselect list box. i know issue with code...
  • Delete Row from Table
    I am trying to delete a row from a table using VBA using a named range to find what I need to delete. My Range is finding the right cell. In the...
  • Assigning to a variable
    I have a for each block where I want to assign the value in column 5 of the found row to the variable Serv. [CODE=vba] For Each ws In...
  • Way to verify information
    Hi All, I don't know what to call this formula, and therefore can't search. I have a spreadsheet with information I want to reference...
  • Active Cell Address – Inactive Sheet
    How to use VBA to get the cell address of the active cell in an inactive worksheet and then place that cell address in a location on the current...
Top