Need help with IF formulas...

[flufferbears]

Hi! I have a problem with my formula. I want to make this into formula form. But I am having difficulty because the formula will be too long if I try to fix it using an OR statement. Is there any way I could set a range? In this current set up, the result value is always 1, I want it to be FALSE when there is no input value. TIA!

Age..................... Result value
0-10 .........................1
11-20........................ 2
21-30 ........................3
31-40 ........................4
41-50 ........................5
51-60........................ 6
>60 ...........................7


=IF('Raw sheet'!G2<=10, "1", IF('Raw sheet'!G2<=20, "2", IF('Raw sheet'!G2<=30, "3", IF('Raw sheet'!G2<=40, "4", IF('Raw sheet'!G2<=50, "5", IF('Raw sheet'!G2<=60, "6", IF('Raw sheet'!G2>=60, "7")))))))

 

[Brian from Maui]

Try using VLOOKUP set for a approximate match (False)

Construct a two column table, first column housing age, then value.

=IF(A1="","False",VLOOKUP(A1,B1:C10,2)

Age column would look somethin like 1,11,21,31,41,51,61

 

[vds1]

Suppose you have age number in G2 then try the below formula

=IF(G2="",FALSE,LOOKUP(G2,{0,11,21,31,41,51,61},{1,2,3,4,5,6,7}))

 

[flufferbears]

Thanks for the prompt reply. Isn't there any other way than using VLOOKUP for this type of situation?

 

[Brian from Maui]

What's wrong with VLOOKUP or LOOKUP? Much cleaner than those nested IF's.

 

[vds1]

If you dont like lookups here is one more way..

=IF(G2="","FALSE",SUMPRODUCT(--(G2>={0,11,21,31,41,51,61})))