# Calculate % Change After Deducting Stake

#### honkin

##### Board Regular
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

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.

##### Well-known Member
You have two Ways:
Excel Formula:
``=((BY2-1)-(V2-1))/(V2-1)*100``

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

#### honkin

##### Board Regular
You have two Ways:
Excel Formula:
``=((BY2-1)-(V2-1))/(V2-1)*100``

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

##### Well-known Member
You're Welcome & Glad I can help.

Replies
2
Views
2K
Replies
2
Views
66
Replies
20
Views
578
Replies
1
Views
113
Replies
161
Views
995

### Forum statistics

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.

### Which adblocker are you using?

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

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