Forum Discussion
Excel ± percentages or figures
Dear Community, I am trying to produce an Excel formula, where the answer hinges on a ±percentage. An “IF” formula that returns 3 variables would be ideal, but Excel (it appears) only allows 2...
Hello,
First need to create slabs for texts, depends on requirements multiple slabs should be created like
Above 120 Above Range
Equal and Above 80 Within Range
Below 80 Below range
Use IFS formula
=IFS(A12>=120,("Above Average"),A12>=80,("Within Range"),A12<80,("Below Range"),"TRUE",0)
Based on range easily construct conditional formula
I hope that this will solve your issue.
Thank you Sivakumarrj for your constructive reply. I'm not quite sure what you mean by ,"need to create slabs"? I can see where you're going to, but where in the overall spreadsheet would I place these slabs Maybe in three hidden cells (so not to obfuscate spreadsheet), for the formula to reference to? Or, by "slabs" are you simple referring to the text options entered into the formula ie. instead of "Within Range", I substitute "Within Target"? Importantly, you have me now researching the "IFS" function. Maybe the answer lies there? I'm fairly competent with Excel, but certainly no expert. Appreciate you taking time to reply. Philip.
- Slabs means range , for example Rank is decided by Marks scored in school/university,
Marks Range (slab can be used in taxation)
0-39 "Fail"
40 - 50 "IIIrd Grade"
60- 80 "IInd Grade"
Above 80 "Ist Grade"
if mark is in A1 Cell based on reference A1 conditional formula can be created like
=IFS(A1>=80,("Ist Grade"),A1>=60,("IInd Grade"),A1>=40,("IIIrd Grade"),A1<40,("Fail"),"TRUE",0)
Advantage of IFS is flexible to use and up to 127 conditions can be used in formula
Thanks- Thank you Sivakumarrj for your explanation of "slabs".
I've managed to find a solution which I posted 10 hours ago. Please take a look at it and give me your thoughts. It may not be the most elegant solution, and I 'm sure an Excel expert would laugh at it, but it works for me. With Kind Regards, Philip.