query on if statement

If the mark is above 95%, then the grade should be 1
If the mark is between 85 - 95%, then the grade should be 2
If the mark is below 75%, then it should be grade 3
If the mark ( value ) is blank or zero, then the grade should be blank...no value needed.

That doesn't seem to cover the option. What grade to you get between 75% - 85%?

Maybe:

Formula:

Please Login or Register  to view this content.

Regards, TMS

This should work, but what happens if the value is below 75% and above 0?
=IF(OR(A1="",A1=0),"",LOOKUP(A1,{0.75,0.85,0.95},{3,2,1}))

EDIT: I should read posts better instead of hurrying (unlike what Trevor did - took his time and read it) now that I reread it I see you want below 75 as 3, in that case with a gap like that mine won't work and I'd recommend TMS' solution.

so based on a reread of the OPs original request and some tweaking to my formula and not knowing what to do with the values between 75% and 85% I came up with these...
=IF(OR(A1="",A1=0),"",LOOKUP(A1,{0.001,0.75,0.85,0.95},{3,"",2,1})) or to handle any errors =IFERROR(IF(OR(A1="",A1=0),"",LOOKUP(A1,{0.001,0.75,0.85,0.95},{3,"",2,1})),"")
For what it's worth.

then this should work for you... =IF(OR(A1="",A1=0),"",LOOKUP(A1,{0.009,0.75,0.85,0.95},{4,3,2,1}))

Are values are in percentage or in numbers.

If Percentage then =IFERROR(IF(OR(A6="",A6=0),"",LOOKUP(A6,{0.001,0.75,0.85,0.95},{4,3,2,1})),"")
If Numbers then =IFERROR(IF(OR(A6="",A6=0),"",LOOKUP(A6,{1,75,85,95},{4,3,2,1})),"")

You're welcome.