nested IF function help

[cpisthedbb]

Hi,

New to the forum, and fairly novice Excel user so bear with me if my questions really easy to answer


In cell A1 I have either "N/A" or a value. I want to put a number in cell A2 dependant on the value in A1. It would be as follows

N/A = 1
1-2 = 1
3-4 = 0.5
4+ = 0

Anyone any ideas what this would look like in a formula as I'm struggling to get this to work?

 

[snjpverma]

Does this work?

=IF(N(A1),IF(A1<3,1,IF(A1<5,0.5,0)),1)

 

[cpisthedbb]

That's great works a treat. Out of interest what does the N do in the formula?

 

[snjpverma]

N is a test to see if A1 is a number of a text.

 

[Peter_SSs]

It looks a bit like your numerical values would be whole numbers (since you haven't given a return for, say, 2.5 or 0.75). If so, another option might be

=LOOKUP(N(A1),{0,3,5},{1,0.5,0})

 

[cpisthedbb]

N is a test to see if A1 is a number of a text.

That's great, thanks for your help.

 

[cpisthedbb]

It looks a bit like your numerical values would be whole numbers (since you haven't given a return for, say, 2.5 or 0.75). If so, another option might be

=LOOKUP(N(A1),{0,3,5},{1,0.5,0})

Yes the values would be whole numbers in this case, thanks for your help though.

 

[Peter_SSs]

Yes the values would be whole numbers in this case, thanks for your help though.

No problem.
So, as well as the lookup formula I suggested, the previous suggestion could also be shortened to this

=IF(N(A1)<3,1,IF(A1<5,0.5,0))