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>
 

Special-K99

Well-known Member
Joined
Nov 7, 2006
Messages
8,327
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,327
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.
 

Forum statistics

Threads
1,081,969
Messages
5,362,484
Members
400,677
Latest member
champchamp

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top