reading number of decimal places

[Keggsy]

Hi all

I’m trying to automate some number manipulation, where I am trying to apply a rounding tolerance, e.g. if a number is 1.25, then the tolerance is 1.254 and 1.245.

I have used the formula =IF(A3=INT(A3),0,LEN(MID(A3-INT(A3),FIND(".",A3,1),LEN(A3)-FIND(".",A3,1)))) to extract the number of decimals which I can then use to apply the tolerance, then using the formulas =IF(C3="","",IF(C3=0,0.4,IF(C3=1,0.04,IF(C3=2,0.004,IF(C3=3,0.0004,IF(C3=4,0.00004)))))) for upper limit and =IF(C3=0,0.5,IF(C3=1,0.05,IF(C3=2,0.005,IF(C3=3,0.0005,IF(C3=4,0.00005))))) for the lower limit.

I then have cells which add the tolerance number to the upper limit, and subtract the number from the lower limit, so that there is now a limit – see attached excel file.

However, I am having a problem if the number I need is quoted as 1.0, as the initial formula doesn’t recognise the 0 following the decimal, even if change the number category in format cells - the tolerance for 1.0 is expressed as 1.4 - 0.5, instead of 1.04 - 0.95.

So, I would like to ask if there is a way of including a 0 following a decimal, maybe by reading the format cell number parameters, or even a better way of doing this all together?

Thanks for your time.

[#2]

@Keggsy , use CELL function to extract the format of cell, and then apply an IF to extract the decimals

=IF(CELL("format",A11)="G",IF(A11=INT(A11),0,LEN(MID(A11-INT(A11),FIND(".",A11,1),LEN(A11)-FIND(".",A11,1)))),VALUE(RIGHT(B11,LEN(B11)-1)))

but there is a big caution : -

Caution : For correct calculation as per latest changed cell values, you need to force recalculation of the Excel by pressing F9 function key or by going to FORMULAS>CALCULATION>CALCULATE NOW

see attached file

[#3]

@Keggsy , further, a safeguard may be added as red font :-

=IF(CELL("format",A14)="G",IF(A14=INT(A14),0,LEN(MID(A14-INT(A14),FIND(".",A14,1),LEN(A14)-FIND(".",A14,1)))),IF(LEFT(CELL("format",A14),1)="F",VALUE(RIGHT(B14,LEN(B14)-1)),"alert!"))

[#4]

@amit_bhola thank you very much, that seems to work. The formulas do need to be manually updated, however they do update automatically when the file is opened, so if it is missed then it will capture it then. 

[#5]

yeah.. i think workbook is recalculated at file close and open events...