im trying to set up a scoring system in excel where it scores a % achieved.
ive worked out all my percentages, I want to score them 1 – 5 as follows.

less than -5% = 1
between -5% & 0% = 2
0% to 10% = 3
10% to 20% = 4
greater than 25% = 5

im sure its really simple, been playing around for a good hour or so with no luck!

Nested if statements using < and > will do it

been trying ‘ifs’ but couldn’t get them to work, I can do simple if’s but skill are clearly lacking!

something like

=if(source < -0.5,1,=if(source <= 0, 2, if (source <=0.1, 3, if (source <= 0.2, 4, 5 ))))

(assuming your 5 score is for > 20% not 25% otherwise you have a no score region between 20 and 25%)

yeh 20% not 25%
didn’t know you could do multiple if’s like that

off to have a play!

sorted, thanks allthepies!

no probs 🙂

didn’t know you could do multiple if’s like that

Aye, that’s the nested part of nested ifs, you can only do about 7 unless you make it more complex, but by then a formula is insanely long anyway, and a macro probably makes more sense!

Or you could solve that scenario of to many categories using a lookup function with the final variable set to true and a carefully constructed lookup table. For those of us what don’t do vba it is a handy fix.

you can only do about 7 unless you make it more complex, but by then a formula is insanely long anyway,

I think the limit was removed in Excel 2007. It’s still there but it’s something like 128 nested ifs rather than 7. Nested ifs are probably the quickest and easiest way to deal with the OP’s problem.

Still there in 2007, I’ve reached it, and it’s what we use at work! I absolutely agree, and I use them because I’m shit at VBA, was merely observing it all gets a bit complex if you’re not careful!

Njee my method would work for you.

It’s too late at night to spell it out in detail but in essence you put the upper or lower end of the banding (I always end up doing this by trial and error as I can never recall whether lookup goes up or down on imprecise matches) in the first column of a lookup table and then your group/category in the next column then use the imprecise version of vlookup to look the % up in the table and return the category.

Nesting lots of if functions carries risk of zigging where you should zag so I tend to use this approach where I would need to use more than 4 nested functions

is this not a case where a vba function would be a hell of a lot simpler than loads of convolution nested ifs (are you missing a score for a percentage between 20 and 25?)

something like

Dim score as integer

Select Case p
Case Is < -5
score = 1
Case Is < 0
score = 2
Case Is < 10
score = 3
Case Is < 20
score = 4
Case Is > 25
score = 5
Case Else
score = 0
End Select

This is one step away from ‘hello world’