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 variables. Basically:
=IF( X = (Y± percentage), “Within Range”, “Above Range” or “Below Range”))
How do I get Excel to recognise (say) ±10% of (say) 100, as a range of 90 to 110? If the formula result falls within 90 to 100, the answer returned is, “Within Range”; If above 110, answer returned is, “Above Range”, and if below 90, the answer returned is “Below Range”.
How do I get Excel to recognise ± as a range? 50±10 always results in 60, never the alternative 40.
Finally, why does Excel not include a ± function? It has plus, minus, greater than, less than functions etc., but no ± function, or not one I can find. I know I can type +- before a figure but Excel treats it as a fixed figure. It does not supply an answer ± either side
In short, I require Excel to accept the ± as a range, be it ±10% or ± of a figure eg. 100±20 = range 80 to 120. If formula results in an answer that falls between 80 to 120, answer returned is, "Within Range", "Correct" or whatever text required.
Any help will be greatly appreciated. Thanks.
- Hello Excel Community,
Thanks for the help I received,
I have finally worked out a solution to the problem I submitted earlier, and have added it in case anyone else might find it useful. Here's my Risk Reward formula:
=IF(P12>Ratio,"Reward",IF(OR(P12>0%,P12>(-Ratio)),"Mid Range",IF(P12<Ratio,"Risk")))
To explain: "P12" is just a reference cell. Use whatever cell you choose. "Ratio" is a Named Cell in which the Risk/Reward ratio is entered. This is changeable. The formula readjusts to whatever figure entered.
In the above, I have used a Risk/Reward ratio of 10%. If "P12" is above the 10% Risk/Reward ratio, it returns text "Reward". If below (-10%), "Risk", and if between, "Mid Range".
Use conditional formatting, if you wish, to further highlight the "Reward", "Risk" and "Mid Range" cell colours.
Any constructive comments appreciated. Philip. (Non Excel expert). Hi Philip8023
in C5
=XLOOKUP(x, CHOOSE(SEQUENCE(2), y-Var*100, y+Var*100+10^-8), {"Within Range","Above Range"}, "Below Range", -1 )in C6
=IFERROR( LOOKUP(x, CHOOSE({1,2}, y-Var*100, y+Var*100+10^-8), {"Within Range","Above Range"} ), "Below Range" )
Thank you Lz. for your reply. I can see how your solution would work, but it entails adding extra columns to an already busy spreadsheet. I have 40+ different assets on a currency spreadsheet. Each has its own value - one might be a few cents, another several hundred dollars. As they move up and down in price, their individual holding values change. Currently, this shows as a percentage gain or fall, with conditional cells, colour coded, to react to a sudden rise or fall in price ie. a sudden rise or fall in the percentage value.
I next wanted to introduce a variable Risk/Reward ratio, to take into account buy/sell fees. Example 5% to Buy and 5% to Sell: Buy an asset at $100 - true value $95 . Percentage ratio displays -5% (loss), but if "Risk/Reward" ratio is (say) 10% the Risk/Reward cell (adjoining) will display, "Within Range". Same when asset rises to $105. Percentage ratio displays +5% (gain); Risk/Reward cell displays, "Within Range". Thereafter, any rise above or below displays, "Above Range" or "Below Range", "Reward" or "Risk" - or whatever text chosen.
I submitted a solution on 08 June which works, well it does for me. One extra column has been added to follow the percentage gain/loss column. The new column header is "Risk/Reward" and a variable Risk/Reward ratio can be entered into it. In my solution example, the Named cell "Ratio" is the Risk/Reward variable. "P12" is a single row percentage gain/loss. Enter the formula into the new adjoining Risk/Reward cell - which in my example would be "Q12" - and then copy into other rows.
How much simpler, if Excel could add a ± variable. On paper this is easy: 10±5 = answer 5 or 20 ie. within a range. If a result is (say) 18 answer = "True" otherwise "False". Excel has no solution for such an easy problem, without using unnecessarily complicated formulae.
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
