if function

**Lou** · 06-06-2010, 02:40 AM

Can anyone help me with a formular to give me the following results for the following;

if cell 1 contains a value between 0 and 18 then an "A" result is required.
If cell 1 contains a value betweenl 19 and 26 then a "B" result is requied
if cell 1 contains a value between 27 and 36 then a "C" result is required
and if cell 1 contains a value of greater than 37 then a "D" result is required.

IF(D3>0,D3<=18,A,IF(AND(D3>18,D3<=26,B,IF(AND(D3>27,D3<=36,C,IF(AND(D3>=37,D,)))))))

This doesn't work for me, what am I doing wrong?

**zbor** · 06-06-2010, 02:50 AM

First of all put "" around text.

Second: no need for double check...

Formula works as: IF function is TRUE then (something) ELSE (something else)

so...

IF(D3<=18, "A", IF(D3<=26, "B", IF( D3<=36, "C", "D")))

Edit: notice that if number is greater than 18 (i.e. 25) it will go to second IF. Now, don't need to check again is it greater than 18. All you need is to see is it less or equal than 26.

**zbor** · 06-06-2010, 02:53 AM

Also...

If cell is empty then you'll get A.
If you want to avoid it you can add one more IF

=IF(D3="", "", IF(D3<=18, "A", IF(D3<=26, "B", IF( D3<=36, "C", "D"))))

**zbor** · 06-06-2010, 02:55 AM

Another way:

=LOOKUP(D3,{0,18,26,36},{"A","B","C","D"})

Again:

=IF(D3="", "", LOOKUP(D3,{0,18,26,36},{"A","B","C","D"}))

Edit. which will give you error for negative results while prior will return A

**Lou** · 06-06-2010, 03:05 AM

Many thanks Zbor

**royUK** · 06-06-2010, 03:18 AM

If you are satisfied with the solution(s) provided, please mark your thread as Solved.

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save