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...
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.