I'm looking for an improvement to the capabilities of formulas --specifically with testing against multiple options. If you wanted to test against 6 possibilities, you'd need something like the following:
if(y0 > y6,if(y0 > y5,if(y0 > y4,if(y0 > y3,if(y0 > y2, if(y0 > y1, y0, y1),if(y1 > y2, y1, y2)),if(y1 > y3,if(y1 > y2, y1, y2),if(y2 > y3, y2, y3))),if(y1 >y4,if(y1 > y3,if(y1 > y2, y1, y2),if(y2 > y3, y2, y3)),if(y2 > y4,if(y2 > y3,y2, y3),if(y3 > y4, y3, y4)))),if(y1 > y5,if(y1 > y4,if(y1 > y3,if(y1 > y2, y1,y2),if(y2 > y3, y2, y3)),if(y2 > y4,if(y2 > y3, y2, y3),if(y3 > y4, y3,y4))),if(y2 > y5,if(y2 > y4,if(y2 > y3, y2, y3),if(y3 > y4, y3, y4)),if(y3 >y5,if(y3 > y4, y3, y4),if(y4 > y5, y4, y5))))),if(y1 > y6,if(y1 > y5,if(y1 >y4,if(y1 > y3,if(y1 > y2, y1, y2),if(y2 > y3, y2, y3)),if(y2 > y4,if(y2 > y3,y2, y3),if(y3 > y4, y3, y4))),if(y2 > y5,if(y2 > y4,if(y2 > y3, y2, y3),if(y3 > y4, y3, y4)),if(y3 > y5,if(y3 > y4, y3, y4),if(y4 > y5, y4, y5)))),if(y2 >y6,if(y2 > y5,if(y2 > y4,if(y2 > y3, y2, y3),if(y3 > y4, y3, y4)),if(y3 >y5,if(y3 > y4, y3, y4),if(y4 > y5, y4, y5))),if(y3 > y6,if(y3 > y5,if(y3 > y4,y3, y4),if(y4 > y5, y4, y5)),if(y4 > y6,if(y4 > y5, y4, y5),if(y5 > y6, y5,y6))))))
YUCK!
The formula I'd like to be able to type instead:
=switch(a,case(>6,"SIX"),case(>5,"FIVE"),case(>4," FOUR"),case(>3,"THREE"),case(>2,"TWO"),case(>1,"ON E"),case(default,"Less Than One"))