Forumla for automatic scoring

**Rand49** · 04-24-2012, 09:21 AM

I'm not too clued up at all on using formulas in excel so not sure if this is possible........any help would be really appreciated please?!

What i am trying to do is score a set of values against a base value.

In Column A i have a set of numbers: 27.6, 27.3, 28.5, 23.9, 25.1, 27.7, 26.1 and 30.2 allocated to each of 8 people.

Based off a base value of 25.6 i want to assign each person a score as follows:

25.6 or lower = 10 points
25.7-26.1 = 8 points
26.2-26.6 = 7 points
26.7-27.1 = 6 points

and so on down to 1 point for 29.2-29.6.

Any score higher than 29.6 gets 0 points.

This will repeated over 9 other sets of data with a different base value each time.

**darknation144** · 04-24-2012, 09:24 AM

So you should probably use match() with index().

**twiggywales** · 04-24-2012, 09:31 AM

if function would also work be be a nightmare and very long

like for first couple of ranges

=if(A1<=25.6,10,if(and(A1>25.6,A1<=26.1),8,(CONTINUE ADDING IN THE FALSE TILL THE END)))

**twiggywales** · 04-24-2012, 09:39 AM

=IF(A1<=25.6,10,IF(AND(A1>25.6,A1<=26.1),8,IF(AND(A1>26.1,A1<=26.6),7,IF(AND(A1>26.6,A1<=27.1),6,IF(AND(A1>27.1,A1<=27.6),5,IF(AND(A1>27.6,A1<=28.1),4,IF(AND(A1>28.1,A1<=28.6),3,IF(AND(A1>28.6,A1<=29.1),2,IF(AND(A1>29.1,A1<=29.6),1,0)))))))))

there is your formula with ifs