Conditional move of decimal point in a formula?

[robdav]

I have numbers in a column where the decimal place is in a different position e.g. 1.97696 and 12719.50. I’m trying to right a conditional formula which can detect where the decimal point is and move it before doing the calculation.

So for example, the first number 1.97696 would need to be 19769.60 and 12719.50 stays as it is because the decimal point is in the right place.

I don’t think I can do it with a custom number format on the one column.

Any help would be appreciated.

Thanks

Rob

 

[mole999]

curious as to why the decimal would be in the wrong place ?

 

[jasonb75]

Moving the decimal point with formatting of any kind will not change the value in the cell, only the appearance. Any calculation performed would still be done on the original value.

How do we know that 1.97696 should be 19769.60 and not 1976.96? Should it always be 5 digits before the decimal point? If yes, then you could use something like

=(LEFT(SUBSTITUTE(1.97696,".","")&"0000000",7)/100) as a correction factor in your calculations.

 

[robdav]

Well I suppose they aren't but in order to compare apples with apples, in a calcultion, the decimal point needs to be in the same place. Does that make sense?

 

[robdav]

Moving the decimal point with formatting of any kind will not change the value in the cell, only the appearance. Any calculation performed would still be done on the original value.

How do we know that 1.97696 should be 19769.60 and not 1976.96? Should it always be 5 digits before the decimal point? If yes, then you could use something like

=(LEFT(SUBSTITUTE(1.97696,".","")&"0000000",7)/100) as a correction factor in your calculations.

Correct, there should always be 5 digits before the decimal point. How would I apply your formula to the whole column where some might already be in the correct format and others not, would I need a helper column? Thanks.

 

[robdav]

Correct, there should always be 5 digits before the decimal point. How would I apply your formula to the whole column where some might already be in the correct format and others not, would I need a helper column? Thanks.

I'm there, many thanks for your help!

 

[jasonb75]

A helper column would be the easiest way, the formula should always give the correct result based on a desired 00000.00 format, anything already in the correct format should remain unchanged.

 

[mole999]

Well I suppose they aren't but in order to compare apples with apples, in a calcultion, the decimal point needs to be in the same place. Does that make sense?

IMHO by changing the actual value, you will be comparing differences of exponential levels making 0.1 = to 1 and 10 and 100 which obviously they are not