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

 

[Ragnar1211]

do you have an example of an amount in F8, F10, and F14 would be in the two different data types?

I just want to make sure I understand the values correctly

 

[honkin]

hi Gerry

The normal pairs would be something like this - 1.32187, whereas there are just a small handful that would be like this - 83.564

So, a full example of each might be this:

Example 1
F8 = 84.454
F10 = 84.987
F14 83.397

Example 2
F8 = 1.32567
F10 = 1.31444
F14 = 1.33211

cheers

honkin

 

[Ragnar1211]

I am not sure this is possible... at least to be 100% accurate.

Will there not be times where the 3 decimal amounts will look exactly like the 5 decimal amounts?

1.31300
1.31345

How can you tell the difference?

Also if the number has zero's at the end Excel will remove them from the amount since they are redundant, thus turning a 5 decimal place into a 3...

There may be better suggestions out there... but my best guess is that you have to manually classify each item.... if you want perfect accuracy.

 

[honkin]

hi Gerry

Understood. I had hoped I could just use an IF argument, but just knew of no expression to ask Excel to check the number of decimal places.

I guess in the long run I could just have 2 templates.

thanks for having a look at it for me Gerry

cheers

honkin

 

[ZVI]

The example of calculating the factor 100/10000:

Excel Workbook
	A	B
1	Value	Factor
2	123.45678	10000
3	123.456	100
Sheet

 

[Ragnar1211]

ZVI... Does that deal with the fact that excel will truncate numbers that have trailing zeros?

The number 123.11100 becomes 123.111, so even though it is a 5 decimal place value and should be multiplied by 10,000, it becomes a 3 decimal place value and gets multiplied by 100..

Unless I am missing something?

 

[ZVI]

ZVI... Does that deal with the fact that excel will truncate numbers that have trailing zeros?

The number 123.11100 becomes 123.111, so even though it is a 5 decimal place value and should be multiplied by 10,000, it becomes a 3 decimal place value and gets multiplied by 100..

Unless I am missing something?

Gerry, it does not deal with Excel's trancating right zero decimals
There is no idea how this can be done correctly
You can see in my example that for the case 123.456 it will always return factor =100 regardless of virtual right zero decimals presense

 

[honkin]

cheers Vladimir, but none of what you posted seems to address anything I have asked for. I do not wish to round numbers down. I wish Excel to know when a number has 3 decimal places and calculate according, and if 4 or 5 decimal places, calculate differently.

I would have thought that the following would be possible, though any code escapes me:

5 Decimal Places:
=IF(F10 > F8, (F8-F14)*10000, (F14-F8)*10000)

or

3 Decimal Places
=IF(F10 > F8, (F8-F14)*100, (F14-F8)*100)

or

4 Decimal Places
=IF(F10 > F8, (F8-F14)*1000, (F14-F8)*1000)

Surely Excel can be told to count decimal places, but it is just something that is beyond me as far as code goes. And the Round or Mround functions are as far from what I want to do as is possible. I need both 3, 4 and 5 decimal numbers to be recognised and acted upon.

I appreciate your reply, Vladimir, but it doesn't address the initial question I asked, which was related to having Excel check the number of decimal places and then act accordingly.

regards

honkin

 

[ZVI]

It is not rounding, it was just idea how to get factor 100 / 1000 / 10000 from the amount of significant decimal places in numbers.

As to your cells layouts the formuls could be:
=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))

But please take into account what Gerry has underlined about Excel's truncating numbers that have trailing zeros.