Forum Discussion
Functions and formulars
Hi all, yes I am struggling to find the right formular for a problem, or to get it to work. I have a number in cell B14, which i want to return a value in cell B13. if B14 gives a value between 30 ...
Hello WBT,
You can use this nested IF formula in B13: =IF(B14<9,"Yellow",IF(B14<=16,"Orange",IF(B14<=33,"Purple",IF(B14<=49,"Pink",IF(B14<=60,"Blue","Other"))))). This works like this: less than 9 = Yellow, 9 to 16 = Orange, 17 to 33 = Purple, 34 to 49 = Pink, 50 to 60 = Blue, anything else = Other.
A cleaner alternative is =CHOOSE(MATCH(B14,{0,9,17,34,50},1),"Yellow","Orange","Purple","Pink","Blue") where MATCH finds which range B14 falls into and CHOOSE returns the corresponding color word. This version is shorter and easier to maintain if you want to add more ranges later.