Calculate % Change After Deducting Stake

honkin

Board Regular
Joined
Mar 20, 2012
Messages
205
Office Version
  1. 2016
Platform
  1. MacOS
hi

I need to be able to correctly calculate the % move of a price, subtracting the stake element from each price first.

I'll explain; the new price (BY2) is 4.50 and the original price (V2) was 8. I need to initially deduct the stake (1) from each cell first and then perform the standard calculation to determine % move.

This is the normal formula to work out % price change
Code:
=(NEW PRICE-OLD PRICE)/OLD PRICE*100

Code:
=(BY2-V2)/V2*100

As indicated, I need to have the stake of 1 removed from each price before calculation, but each way I have tried is incorrect

Here's what I tried initially

Code:
=(BY2-1)-(V2-1)/(V2-1)*100
This, unfortunately, does not give the correct answer. The answer should be -50, but I get -96.50

If I do it manually, it is simply this
Code:
=(3.50-7)/7*100
and the answer is -50. If I use the cell reference -1, as I did above, I have to add the additional brackets, but then the answer is incorrect
Code:
=(4.5-1)-(8-1)/(8-1)*100
That gives -96.50

I know it will be a simple matter of incorrect syntax, but can anyone see the issue?

cheers
 

Some videos you may like

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.

maabadi

Well-known Member
Joined
Oct 22, 2012
Messages
1,553
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
You have two Ways:
1. Add Parenthesis:
Excel Formula:
=((BY2-1)-(V2-1))/(V2-1)*100

2. Use this formula:
Excel Formula:
=(BY2-V2)/(V2-1)*100
 
Solution

honkin

Board Regular
Joined
Mar 20, 2012
Messages
205
Office Version
  1. 2016
Platform
  1. MacOS
You have two Ways:
1. Add Parenthesis:
Excel Formula:
=((BY2-1)-(V2-1))/(V2-1)*100

2. Use this formula:
Excel Formula:
=(BY2-V2)/(V2-1)*100
Ah brilliant maabadi
I had tried different combinations of the parentheses, but to no avail.
This works a treat
Thanks again
 

maabadi

Well-known Member
Joined
Oct 22, 2012
Messages
1,553
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
You're Welcome & Glad I can help.
 

Watch MrExcel Video

Forum statistics

Threads
1,126,928
Messages
5,621,641
Members
415,849
Latest member
PhoenixRising2015

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top