Replacement for nested if functions in Excel and more
---------- Forwarded message ----------
| age<=5 | -1 |
| 5<age<10 | 0 |
| 10=<age<20 | 1 |
| 20=<age<=25 | 2 |
26<age | 3 |
If you have attempted this：IF（L3<=5,-1,IF(AND(,L3>5,L3<10),0,IF(AND(L3>=10,L3<20,1,IF(AND(L3>=20,L3=<25),2,3)))), it's kind of a painful way, and there are also limitation of 7 nested or so I heard.
Any alternatives？？？First thing come to mind is some lookup method, see attached first sheet, then I suspected there are more better way of doing it. So I came across this tip, it actually suggested 6 ways to do it. So I gave some tries, the result is quite some interesting learning experience. Pls. refer to each sheet of the attached excel file. The other thing bugged me for quite a while was the last method using Boolean Multiplication, a natural improvement was sumproduct, however I could not get sumproduct(boolean array, score) work, it always returns 0 in Excel 2010, as confirmed in another blog post I found online. After some try and errors, only the 9th sumproduct works. It's very interesting to see how this method can be hacked to simulate typical select from where SQL query in Excel.
