You don't need to be an 'investor' to invest in Singletrack: 6 days left: 95% of target - Find out more
I want to return a result of LOW if the result in another cell is 25 or less, MEDIUM if 26-50, and HIGH if 51 or more. Is there a better alternative to nested IF, particularly as I may want to change the criteria?
I thought LOOKUP may help but not sure how to use with a range of values. I'm guessing some kind of array but cognitive ability impaired by a head cold and last nights cider 🤪
Nested IF, but don't hardcode the values, instead have your 'banding' values in a separate set of cells and use fixed cell references to those cells within your nested IF.
IF(A1<=$M$1,"LOW",(if(a1<=$M$2,"MEDIUM","High")))
A1 is the value you're checking, M1 has 25 in it, M2 has 50 in it. You can change M1 and M2 to be whatever you like as you go
Brackets probably need fixing/checking too
🤷🏻♂️
I rarely use just an IF in excel. usually include an IFERROR() as a lead-in to avoid unsightly glitches.
in nesting things I also find that alt+enter let’s me format the excel guff for easier reading
liking conditional formatting.
liking using reference cells for the conditional values.
also wondering about SWITCH() https://www.statology.org/case-statement-in-excel/
Thanks IHN that works! I was nearly there. Conditional formatted to green/amber/red too 👍 Thanks all.
Nested IF (or IFS) is the easiest way to go, as said.
However, if you hated IF or had more results/bands then you could use a CHOOSE and XMATCH combo:
=CHOOSE(XMATCH(A1,$M$1:$M$3,1),"LOW","MEDIUM","HIGH")
where the value to test is in cell A1
and 25, 50 etc. is listed in cells M1 and M2. Leave M3 blank.
Sumif?
Would it not be easier to just vlookup a close match then you can very easily change your values and outputs?
Eg
=vlookup(a1,b1:c3,2,true)
B1=0 C1= low
B2=26 C2= med
B3=51 C3= high
Barely gets simpler than conditional formatting