TOGGLE between percentage and absolute

[ray3228]

Hi all,

i have a large dataset containing prices of some items from 2017 to 2024. (2022, 2023, and 2024 prices are predicted).
i need to analyse how these historical and predicted prices compare to the current (2021) price.
this has to be done both in absolute terms (e.g. 2021 = $100, 2022 = $120) and percentage terms (e.g. 2021 = base, 2022 = 120% of 2021).

is there a way to create a toggle button (and macro?) that allows me to toggle between absolute and percentage terms for this?

for example:
absolute version - 2020 = $80; 2021 = $100; 2022 = $120
percentage version - 2020 = 80%; 2021 = 100%; 2022 = 120%

thanks in advance.

[MrShorty]

I'm sure one could create a toggle button with macro to do this, but I would probably not choose that approach. It's probably mostly a question of programmer's and/or user's preference, and you haven't really indicated what preferences you have.

If I were doing this, I would probably:

1) Choose a cell for the toggle. Probably a cell at the top of the column, but this depends on exactly how your sheet is arranged.
2) Choose expected values for the toggle and what they should mean. I would probably simply choose a simple boolean TRUE/1 or FALSE/0 for my toggle values, but you can use whatever makes sense to you. If necessary, apply data validation to this cell so that only the two appropriate values can be entered.
3) Then, a simple IF() function in the column where the calculation is performed. =IF(absolutereferencetotogglecell=condition,absolutecalculation,percentagecalculation) or whatever makes sense from the toggle values I want to use.

That's how I would be inclined to do this, but, as noted, a lot of that is programmer's preference. If you would prefer a button with macro solution, that can be done as well.

Does that help?

[ray3228]

Yes, that is actually quite helpful. Thanks for this! Toggles can get complicated and laggy.

[AliGW]

Administrative Note:

Welcome to the forum.

Although we value your privacy as much you do, it could be important that members have a rough idea of your location as the solutions they offer may be affected by your locale. For instance, you might in the future post questions which are related to your regional settings.

With this in mind, please update your profile to something more precise (something such as UK, Europe, USA, UAE, etc. will suffice).

Thank you for helping us to help you.