Number in cell A3, positive/negative numbers, returns count of decimal places entered.
No check done for errors.
=IF(LEN(A3)=LEN(TRUNC(A3)),0,LEN(ABS(A3))-LEN(TRUNC(ABS(A3)))-1)
'---
Jim Cone
Portland, Oregon USA
List Files excel add-in - with hyperlinks
Thanks, this is great.
One slight problem would be if 2 zeros are entered after the decimal place, eg 10.00
Is there a way round this?
Look in the formula bar.
Why not just format the cell with two decimal places?
'---
Jim Cone
Formatting for 2 decimal places would work in most cases but there would be a problem if 3 or more decimal places are entered. Excel would round the number and this would alter the accuracy of the data.
Formatting a cell to two decimal places does not change the actual value that excel uses.
Formatting only changes the text that appears on the screen.
Excel uses the value in the formula bar.
Formatting to two decimal places "could" indicate to the user that a whole number is not sufficient.
One way to ensure acurate data entry is to display an Input box or Textbox to the user and validate the entry before using it. That requires some knowledge of the VBA programming language.
'---
Jim Cone