Check Decimal Places

[honkin]

hi

I am looking for a way to have Excel check the number of decimal places for a formula.

I currently have the following formula =IF(F10 > F8, (F8-F14)*10000, (F14-F8)*10000) which calculates pips for me based on whether the trade is a buy or a sell, but just realised that not all currency pairs have the same amount of decimal places.

My broker uses 5 decimal places for pairs, but there are a handful that only use 3 decimal places and I need the calculation to reflect that. So if it is a normal 5 decimal pair, the above will work fine, but if it is only a 3 decimal pair, the formula will need to be =IF(F10 > F8, (F8-F14)*100, (F14-F8)*100)

If someone could possibly help me, that would be greatly appreciated.

regards

honkin

 

[honkin]

OK, my apologies Vladimir. AS soon as I saw ROUND in there, it threw me.

The code does not appear to work correctly in at least 1 cell. Here is what I input in F8 - 88.23000 and in F10 - 87.67900. The answer from the following

=IF(F10>F8,(F10-F8)*10^(LEN(ROUND(MOD((F10-F8),1),5))-3),(F8-F10*10^(LEN(ROUND(MOD((F8-F10),1),5))-3)))

was -8679.67. The answer should be 55.1, which is F8 minus F10 and the decimal moved. The answer must always be a positive number, as you are subtracting the smaller from the larger.

So what there is in my template is an entry price, a stop price and 4 target prices.

For the stop calculation, if F10 is larger than F8, the the position is a Sell; if F8 is larger than F8, the position is a Buy. To calculate my stop it is simply either F8 - F10, or F10 - F8, displayed like 55.1 instead of 0.551

Final Target - now, for some reason, this one calculated correctly. Input was F8 88.23000 and F14 was 89.12300. The answer is 89.3 and worked fine.

=IF(F10>F8, (F8-F14)*10^(LEN(ROUND(MOD((F8-F14),1),5))-3), (F14-F8)*10^(LEN(ROUND(MOD((F14-F8),1),5))-3))

The 4 targets seem to work as well as the Final target, so for some reason it just does not calculate correctly for Stop

Thanks so much. It does appear to be getting there, but cannot work out why it is not calculating correctly in just that 1 cell

regards

honkin

 

[ZVI]

The code does not appear to work correctly in at least 1 cell. Here is what I input in F8 - 88.23000 and in F10 - 87.67900. The answer from the following

=IF(F10>F8,(F10-F8)*10^(LEN(ROUND(MOD((F10-F8),1),5))-3),(F8-F10*10^(LEN(ROUND(MOD((F8-F10),1),5))-3)))

was -8679.67. The answer should be 55.1, which is F8 minus F10 and the decimal moved. The answer must always be a positive number, as you are subtracting the smaller from the larger.

There is a typo in your formula (I've marked it in Red), here is the fixed one which returns 55.1:
=IF(F10>F8, (F10-F8)*10^(LEN(ROUND(MOD((F10-F8),1),5))-3), (F8-F10)*10^(LEN(ROUND(MOD((F8-F10),1),5))-3))

 

[ZVI]

Please pay your attention on fact that if F8 = 88.239 and F10 = 87.679 then formula returns 5.6 because F8 - F10 = 0.56 and factor = 10 in this case.
Not sure if it's correct logic for you or not, but it concerns to Gerry's warning as well.

 

[honkin]

understood Vladimir, but not sure why the other cells calculate correctly based on the same formula.

And just to correct you, the formula did not return 0.56, it returned -8679.67, which is miles from the correct result.

cheers and thanks for trying to assist

regards

honkin

 

[ZVI]

And just to correct you, the formula did not return 0.56, it returned -8679.67, which is miles from the correct result

In your formula of post #11 some parentheses are not on the correct place.
See my post #13 with fixing it.
Below is the cells layout and formula which returns correct result 55.1

Excel Workbook
	E	F
8	55.1	88.23
9
10		87.679
Sheet