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 missing something as it isnt recognizing it as a valid formula.
=SWITCH(Status,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(.01), (Q4/B4*100)=<MAX(24.99)),"Crippled","?")
Im sure its something simple any suggestions or heck if there is a better way to do this I am all ears. Still pretty new with excel and doing things like this. Thanks for any help.
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.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.
