Excel 2007 : Help needed to write a subtraction formula

Hello,

there may be a more elegant way, but try this in C1

=ABS(A1-B1)*("1"&REPT("0",LEN(TEXT(ABS(A1-B1),"general"))-1))

copy to C5 and see if it suits.

cheers,

Hello & thanks for your time, I thought you'd solved it but when I tried throwing a few random numbers in such as the following entries I didn't get the desired result;

1.03446 - 1.03542 = 960 (the answer I need in this instance is 9.6)

What are your thoughts?

Thanks again

Hi fxtrader,

Try this formula in C1

Please Login or Register  to view this content.

It seems to me you are trying to get numbers between 1 and 10.

Hope that is what you're looking for.

Thanks again!

The anwer will not always be betwee 1 - 10. The numbers I'm using in the calculation are currency prices and I'm trying to calculate the difference between the two in "pips". Pips are the unit of measure in currency trading. e.g. 125.145 - 120.00 = 514.5 pips

Any suggestions?

I guess I need some more examples.
It seems Absolute Value of the difference times 100 should work in your last example.

On http://www.informedtrades.com/22740-...onal-pips.html
the explaination given is based on how many digits are displayed. Is that correct?

Hi Marvin, some examples would be;

1.32650 - 1.32850 = 20
1.03445 - 1.03802 = 35.7
124.550 - 121.000 = 355
124.550 - 124.700 = 15
79.839 - 80.00 = 16.1
1.58556 - 1.56406 = 215

Thanks for your time

The explanation on the link is correct.

Hi,

It looks like a PIP is dependent on how many decimals are given in the problem. Excel looks at numbers like your 121.000 from above and makes it 121. This makes the problem hard for programs that use numbers. I believe to do this problem you will need to pass a text string to the function and see how many letters/digits are to the right of the decimal to see what the base value of the PIP is. See the link I attached two posts above.

Thanks for your assistance Marvin, I'm a bit too green with excel to understand what you mean however I appreciate your time. I can make the formula work but it seems that I need to manually change it depending on how many decimal places I use. I'm trying to find a way to avoid this. Please forgive my ignorance.

fxtrader, please stop quoting whole posts. It's unnecessary clutter, makes the thread harder to read and creates work for the moderators.

There goes your opportunity to get free help on this forum.