Forum Discussion
Solved
Need some help with a switch statement
I am trying to get a cell in a spreadsheet to evalute a formula that is coming up with a percentage and then based on ranges populate the cell with a text value this is what I have so far but I am mi...
Perhaps
=IFNA( LOOKUP( Q4 / B4 * 100, {0.01, 25, 50, 75, 96}, { "Crippled", "Heavy Damage", "Medium Damage", "Light Damage", "Unmanaged" } ), "?" )
Actually I think I may have figured it out this appears to be working correctly now
=SWITCH((Q4/B4*100)<>0,AND((Q4/B4*100)>=MIN(96), (Q4/B4*100)<=MAX(100)),"Undamged",AND((Q4/B4*100)>=MIN(75), (Q4/B4*100)<=MAX(95.99)),"Light Damage", AND((Q4/B4*100)>=MIN(50), (Q4/B4*100)<=MAX(74.99)),"Medium Damage", AND((Q4/B4*100)>=MIN(25), (Q4/B4*100)<=MAX(49.99)),"Heavy Damage",AND((Q4/B4*100)>=MIN(0.01), (Q4/B4*100)<=MAX(24.99)),"Crippled","?")
Again if there is a better way to do somethign like this I am all ears thanks again.
=SWITCH((Q4/B4*100)<>0,AND((Q4/B4*100)>=MIN(96), (Q4/B4*100)<=MAX(100)),"Undamged",AND((Q4/B4*100)>=MIN(75), (Q4/B4*100)<=MAX(95.99)),"Light Damage", AND((Q4/B4*100)>=MIN(50), (Q4/B4*100)<=MAX(74.99)),"Medium Damage", AND((Q4/B4*100)>=MIN(25), (Q4/B4*100)<=MAX(49.99)),"Heavy Damage",AND((Q4/B4*100)>=MIN(0.01), (Q4/B4*100)<=MAX(24.99)),"Crippled","?")
Again if there is a better way to do somethign like this I am all ears thanks again.
Perhaps
=IFNA(
LOOKUP(
Q4 / B4 * 100,
{0.01, 25, 50, 75, 96},
{
"Crippled",
"Heavy Damage",
"Medium Damage",
"Light Damage",
"Unmanaged"
}
),
"?"
)- Wow that is way simpler them my switch statement approach thank you will add that one to my list of functions formulas for reference. Looks like I took the long way to get there.
To make it more reliable from maintainability point of view you may put numbers and texts into some helper table within workbook and use any lookup function to get data from it.