If the total = between 17-20 give it a score of 5, and so on

[Guest]

Not good with "if statments" can anyone help with the problem

If the total of one cell is between 17-20 than give it a score of 5 but if
the total is between 13-16 than give it a score of 4 and so on down to a
score of 1

 

[Ragdyer]

Try this to score the contents of cell A1:

=LOOKUP(A1,{0,1,5,9,13,17;0,1,2,3,4,5})

 

[Guest]

thank you that worked well, I don't quite know exactly how it worked but it
did.

HW

 

[Ragdyer]

It's a simple, self-contained, Lookup formula, where the values are included
within the formula itself, instead of being in a separate datalist, located
elsewhere on your sheet.

You could have just as well created this list, in an out-of-the-way location
of your sheet, say Y1 to Z6:

Y Z
1 0 0
2 1 1
3 5 2
4 9 3
5 13 4
6 17 5

And then used any one of these formulas:

=LOOKUP(A1,Y1:Z6)

=VLOOKUP(A1,Y1:Z6,2)

=INDEX(Z1:Z6,MATCH(A1,Y1:Y6))

And of course, all of the above formulas could also be constructed to be
self-contained, where no *outside* datalist would be necessary.


And finally, thank you for the feed-back.