CODEWITHSUNDEEP
arraysvbaexcelexcel-formula
Chrisftw
Chrisftw

Reputation: 119

VBA array formula to get description where score between two values

I have the following cells of score ranges and descriptions:

Statement  Critical  Significant Average  Minor Non-critical
Grades     0.8       0.6         0.4      0.2   0

I have a data-set where grades will range from 0 to 1 and would like to create a string variable for each observation that will print the above statements based on the grades.

So, for a random cell X1 my formula is:

={IF(X1>=A2:A6;B2:B6)}

But this array formula returns False if the value is 0.3. Any ideas?

Upvotes: 1

Views: 88

Answers (1)

Robin Mackenzie
Robin Mackenzie

Reputation: 19289

You can use a standard combination of INDEX and MATCH to solve this problem. The formula would be (per screenshot below):

=INDEX($F$2:$J$2,MATCH(B8,$F$3:$J$3,-1))

Where

  • $F$2:$J$2- the list of 'statements' e.g. critical, significant
  • B8 - the grade in question
  • $F$3:$J$3 - the range of grade ranges
  • -1 - as an argument to the MATCH function is 'greater than'

Screenshot:

enter image description here

In that example, note that a score where 0.4 < score <= 0.6 gives Significant. However you might want that range to return Average in which case the whole MATCH function needs an offset e.g.:

=INDEX($F$2:$J$2,MATCH(B8,$F$3:$J$3,-1)+1)

But note for a grade of 0 this will give a #REF! error unless you create a new 'statement' e.g. 'less than critical' with a score of -1.

Also, note that your array formula:

={IF(X1>=A2:A6;B2:B6)}

Is not quite right - normally you enter a formula and then use ctrlshift+enter to create an array formula which in your case would be like this:

{=IF(X1>=A2:A6;B2:B6)}

But that doesn't work for me - I think it is easier for you to just the INDEX and MATCH approach unless you absolutely need an array formula.

Upvotes: 1

Related Questions