Formula to lookup a score that requires two different methodologies

[The_Snook]

Evening all,

I've pulled together a potential solution to calculate some scores but it contains a hard coded element which I'm not particularly keen on. I'd be interested to learn if there are any better practice solutions to this problem?

The attached shows what I'm working with and the green cells (G3:G7) show expected outcomes. The scoring system is essentially a sliding scale where maximum marks are returned for achieving 100%, with the points awarded decreasing as you move away from this point. A standard approximate VLOOKUP works up to 100%, but the approach needs to change once the value exceeds this (hence why I manually add on 0.05).

Thanks in advance,

Snook

[TMS]

If it works, why worry? Were there any values it doesn't return the value you expect?

You could make it shorter:

=VLOOKUP(F3+IF(F3<=1,0,0.05),$B$3:$C$43,2,1)

[The_Snook]

No, it correctly calculates what I am expecting. As I was writing it, I didn't like the use of the hard coded add on, even though I knew it would work. It feels more like a fudge than a solution to me.

It's more to minimise the risk of issues if others copy it but want to use an alternative scoring scale. I suppose that's my main issue, it works for this scale but would potentially need tweaking for others.

I'm probably overthinking it.

Cheers for the shorter solution, I'll use that.

Snook

[wk9128]

Cell G3 formula , drag down

=LOOKUP(F3+IF(F3<=1,,0.05)+1%%,$B$3:$B$43,$C$3:$C$43)

[TMS]

You're welcome. Thanks for the rep.

[Bo_Ry]

Please try

=100-CEILING(ABS(1-F3)*100,5)

[TMS]

@Bo_Ry: your formula needs adjusting for some of the values.

% Points TMS wk9128 Bo_Ry Compare
105.00% 90 90 90 95 2
110.00% 85 85 85 90 2
115.00% 80 80 80 85 2
120.00% 75 75 75 80 2
125.00% 70 70 70 75 2
130.00% 65 65 65 70 2
135.00% 60 60 60 65 2
140.00% 55 55 55 60 2
145.00% 50 50 50 55 2
150.00% 45 45 45 50 2
155.00% 40 40 40 45 2
160.00% 35 35 35 40 2
165.00% 30 30 30 35 2
170.00% 25 25 25 30 2
175.00% 20 20 20 25 2
180.00% 15 15 15 20 2
185.00% 10 10 10 15 2
190.00% 5 5 5 10 2
195.00% 0 0 0 5 2

[Bo_Ry]

@Bo_Ry: your formula needs adjusting for some of the values.

How about

=MAX(100-ABS(INT(F3*20-20+(F3>1))*5),)

[AliGW]

Another option:

=XLOOKUP(F3,$B$3:$B$43,$C$3:$C$43,"",IF(F3<1,-1,1))

[TMS]

@Ali: similar problem to Bo_Ry's solution.

% Points TMS wk9128 Bo_Ry AliGW Compare
105.00% 90 90 90 95 95 2
110.00% 85 85 85 90 90 2
115.00% 80 80 80 85 85 2
120.00% 75 75 75 80 80 2
125.00% 70 70 70 75 75 2
130.00% 65 65 65 70 70 2
135.00% 60 60 60 65 65 2
140.00% 55 55 55 60 60 2
145.00% 50 50 50 55 55 2
150.00% 45 45 45 50 50 2
155.00% 40 40 40 45 45 2
160.00% 35 35 35 40 40 2
165.00% 30 30 30 35 35 2
170.00% 25 25 25 30 30 2
175.00% 20 20 20 25 25 2
180.00% 15 15 15 20 20 2
185.00% 10 10 10 15 15 2
190.00% 5 5 5 10 10 2
195.00% 0 0 0 5 5 2

[AliGW]

Ugh!!!

Back to the drawing board ...

[nick.williams]

For the example scoring system, as Bo_Ry has pointed out it doesn't need a lookup as a short formula can determine the score.

@The_Snook If you want it to be flexible then it depends on the scope really. Clearly you want to allow for other users to change the points values. If it will always be of the same format i.e. peaking at 100% then I can't improve upon AliGW's solution. Although if it's possible to have values outside of the range 0-200% then you might want to add some bits to handle that (and assign 0 points, for example).

If you wanted the same sort of format but to allow for the possibility of the maximum score not being 100%, i.e. still peaking at a certain value then decreasing, then you could replace the IF(F3<1, with and INDEX and MATCH function which looks up the MAX values in column C. For some reason a firewall keeps blocking me when I try to post the formula.

@TMS, actually I think Bo_Ry and AliGW's solutions are correct and the others are wrong; they do not match column C.

Nick

[TMS]

@Nick:

@TMS, actually I think Bo_Ry and AliGW's solutions are correct and the others are wrong; they do not match column C.

And I think you are right. 🤭🤔

I was comparing the solutions to the original formula, not the table.

My apologies to Bo_Ry and AliGW. ☹️☹️☹️

[AliGW]

Thanks, Trevor - I thought I was losing the plot ... again!!!

[TMS]

@Bo_Ry: well, that matches my original expectations BUT please see posts #11 and #12 (my apology!)

[The_Snook]

Morning all,

Cheers for the additional solutions, I hadn't noticed the activity in the thread had continued.

It was interesting to see the calculated solution as I hadn't considered that approach.

Regards,

Snook

[AliGW]

You're welcome - thank you for the kind rep comment.