Variance Range Formula

[Kay5291]

Good morning!

I would be grateful for some assistance on a formula i am trying to create.

I need to return a score that is based on a variance number. I am attaching a picture of the data for clarity.

Capture.PNG


So i am calculating a score between 1 and 5 based on this range:

If the variance is unfavorable by 15% or more, "1"
If the variance is unfavorable by 10% - 14.99%, "2"
If the variance is unfavorable by 5% - 9.99%, "3"
if the variance range is from 0 (no variance) to favorable of 5%, "4"
If the variance is favorable at 5% or greater, "5"

I hope this makes sense between the above and the attached snapshot. I have tried many many variations of IF/AND but am striking out.

Thanks in advance!

[johnmpl]

Hi!

If you have the percentage in D2, try:
=MATCH(D2,-{9E+307,15,10,5,0,-5}%+1%%)

Blessings!

[Kay5291]

Thanks for the response -

this is returning "backwards" results. So for a positive variance it is returning a 1, which is the worse on the scoring scale, and for negative variance it is returning a high number. Also it is returning a 6 instead of a 5??? Not sure why. Any further edits?? (See pic for results).

Thanks!

Capture2.PNG

[Sam Capricci]

deleted, Kay did as requested.

[Sam Capricci]

Deleted, duplicate post.

[Kay5291]

For anyone still following - I solved.

=IF(G20>=0.15,1,IF(AND(G20<0.1499,G20>0.1001),2,IF(AND(G20<0.1,G20>0.0501),3,IF(AND(G110<0.05,G20>-0.049),4,IF(G20<=-0.05,5)))))

Thanks for the assistance. I will mark as solved. And change the title.

K

[Sam Capricci]

thank you.

[johnmpl]

Hi again, Kay!

Ok... I'd understood "backwards". You could try:
=MATCH(G20,{9E+307,15,10,5,-4.99}%-1%%,-1)

Blessings!