Help me with this MS EXCEL formula please?

Question

Hi,

I need help!! I am creating an excel formula and require the correct formula that will produce a score when a certain figure is input.

so the scale I have to work with is

0 points if < £2.71
2 points if < £2.81
3 points if < £2.91
4 points if < £3.01
5 points if < £3.11
6 points if < £3.21
7 points if < £3.31
8 points if < £3.41
9 points if < £3.51
10 points if £3.51 or over

I have tried using the =if(a1<2.70, "0", if a1<2.81, "2" etc

(Where a1 is the input value)

but for some reason I just can't get it to work when I try to submit the formula

HELP!!!

Answer 1

If you split up the nesting, you can beat the nesting limit:

=IF(A1<3.11, IF(A1<2.71,0, IF(A1<2.81,2, IF(A1<2.91,3, IF(A1<3.01,4,5)))), IF(A1<3.21,6, IF(A1<3.31,7, IF(A1<3.41,8, IF(A1<3.51,9,10)))))

Answer 2

The IF function in excel only allows for up to seven nested if's so you can't do that. The example above using FLOOR will not produce correct results for many value (try with 2.88, 2.50, 3.12, etc)

So, you either need to use a mathematical equasion as I've done below or use the VLOOKUP or HLOOKUP functions.

Try:

=IF(A1<2.71,0,IF(A1>=3.51,10, INT(A1-2.71)*10)+2) )

You can also see the help pages in excel for vlookup or hlookup.

Answer 3

You need to nest the IF starements - Excel syntax is a little confusing. The end result will look something like:

=IF(A1<2.7,"0",(IF(A1<2.81,"2",(IF(... and so on.

Make sure you close all the brackets at the end.

Answer 4

=IF(A1>3.51, "10", IF(A1<2.71, "0", FLOOR(A1*10, 1))-26)

Try that. Floor rounds down.

Answer 5

Create an array off to the side:
M. . . . .N
0. . . . .0
2.71. . .2
2.81. . .3
2.91. . .4
3.01. . .5
3.11. . .6
3.21. . .7
3.31. . .8
3.41. . .9
3.51. . .10

Then, alongside your figures, type this formula.
=VLOOKUP(A1, $M$1:$N$10, 2)

This assumes your data starts in A1 and you typed the array in M1:N10; change if necessary.

Answer 6

=IF(A1<2.71,"0",IF(A1<2.81,"2", IF(A1<2.91,"3",IF(A1<3.01,"4","5"))))

let me know if this helps.