Nested Formula

[MarleneCunliff]

Good day

I need help pleas

IF(M4>80,"7",(M4>70,"6",(M4>60,"5")))

[#2 · Accepted solution]

Place the numbers in a two-column table, similar to this:

  A  B
1 60 5
2 70 6
3 80 7

And use a formula like this:
=VLOOKUP(M4,$A$1:$B$3,2,TRUE)

[#3]

@MarleneCunliff As a variant, and if you insist on using nested IF's, perhaps this is what you tried to achieve:

=IF(M4>80,"7",IF(M4>70,"6",IF(M4>60,"5","x")))

where "x" will be returned for any value in M4 less than or equal to 60. Remove the ,"x" part and it will return FALSE in such case.

[#4]

@Jan Karel Pieterse 

Thank you for your reply, I got it to work as per your directions, however some fields give me a #N/A value why is this as the array table does have the values it should.

[#5]

The table must be sorted in ascending order of the first column