Scoring values including NA() - Excel 365

Question

Let's say I have the following array of numbers:

$222.71 
$370.00 
$224.93 
#N/A
$230.00 
$175.00 
$160.00 
$248.00 
$150.00 

I would like to score/rate the numbers above using some arbitrary criteria. For example, if the value is within 15% of the minimum, it gets a score of 3; if it is within 50% of the minimum, it gets a score of 2; if it is above 50% of the minimum or NA(), it gets a score of 1.

I would like the formula to be flexible and easy to maintain, as there might be many more relative value-based conditions.

=LET(
    values, A1:A9,
    minValue, MIN(FILTER(values,ISNUMBER(values))),
    rank, IF(ISNUMBER(values), IF(values <= minValue * 1.15, 3, IF(values <= minValue * 1.5, 2, 1)), 1),
    rank)

The above works but it's messy to read and maintain.

Using SWITCH makes it more readable and easier to maintain but it looks to long:

=LET(
    values, A1:A9,
    minValue, MIN(FILTER(values,ISNUMBER(values))),
    rank, IFERROR(
        SWITCH(TRUE(),
        values <= minValue * 1.15, 3,
        values <= minValue * 1.5, 2,
        1),
    1),
    rank
)

Are the better, more elegant ways of doing it?

Answer 1

First Tip: take a look at the AGGREGATE function (and the related SUBTOTAL function): you can change minValue to be AGGREGATE(5, 6, values) instead of MIN(FILTER(values,ISNUMBER(values)))

Next tip is to use XLOOKUP, and build a lookup columns, by multiplying minValue by an array of your bounds. (Note that an Array separates Rows with ;, and Columns with ,)

Based on your data (a minValue of 150), this gives lookup columns of:

minValue*{1.5; 1.15}	{2; 3}
225	2
172.5	3

Doing an Approximate XLOOKUP on this, looking for the Next Largest Item (to get our <= results), and returning 1 when no match is found (i.e. >minvalue*1.5) can be done like this: (the second , 1 is what determines <= rather than the default of =)

XLOOKUP(values, minValue*{1.5; 1.15), {2; 3}, 1, 1)

This gives you a final function that looks like this, and can be easily expanded for however many "buckets" you want to include:

=LET(values, A1:A9,
    minValue, AGGREGATE(5, 6, values),
    IFERROR(
        XLOOKUP(values,
            minValue*{1.5; 1.15},
            {2;3},
            1,
            1),
        1
    )
)

Then, if you wanted to change from 1.5 and 1.15 to 1.75, 1.5 and 1.25 (and make the top result 4 instead of 3) then you would just have to extend the two Lookup arrays in the XLOOKUP

minValue*{1.75; 1.5; 1.25},
{2; 3; 4}

Much simpler than adding entire new conditions to a SWITCH!