Calculate % Change After Deducting Stake

[honkin]

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

=(NEW PRICE-OLD PRICE)/OLD PRICE*100

=(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

=(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

=(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

=(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

 

[maabadi]

You have two Ways:
1. Add Parenthesis:

=((BY2-1)-(V2-1))/(V2-1)*100

2. Use this formula:

=(BY2-V2)/(V2-1)*100

 

[honkin]

You have two Ways:
1. Add Parenthesis:

=((BY2-1)-(V2-1))/(V2-1)*100

2. Use this 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]

You're Welcome & Glad I can help.