Calculate % Change After Deducting Stake

honkin

Active Member
Joined
Mar 20, 2012
Messages
371
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
 

Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.
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
 
Upvote 0
Solution
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
 
Upvote 0

Forum statistics

Threads
1,213,485
Messages
6,113,931
Members
448,533
Latest member
thietbibeboiwasaco

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
Back
Top