Formula to convert anything above decimal 7 to 9

[Hiten Vakharia]

Hi,

I would like to convert the decimal which is equal or higher than .70 to .99

For eg.
12.75 changes to 12.99
10.82 changes to 10.99
else all the other figures remains unchanged
10.39 does not change
19.69 does not change

Thanks in advance
Hiten

 

[Rick Rothstein]

I would like to convert the decimal which is equal or higher than .70 to .99

For eg.
12.75 changes to 12.99
10.82 changes to 10.99
else all the other figures remains unchanged
10.39 does not change
19.69 does not change

Here is one way to do it...

=IF(MOD(A1,1)>=0.7,INT(A1)+0.99,A1)

 

[Hiten Vakharia]

Here is one way to do it...

=IF(MOD(A1,1)>=0.7,INT(A1)+0.99,A1)

Many thanks, that worked.

Just one thing more, in addition to this I would like to change the last digit to 9 where there it is below 7
eg.
12.50 changes to 12.59 (last digit changes to 9)
12.75 changes to 12.99 (as per the above formula)
11.83 changes to 11.99 (as per the above formula)
23.52 changes to 23.59 (last digit changes to 9

Regards,
Hiten

 

[Tetra201]

Try

=IF(MOD(A1,1)>=0.7,INT(A1)+0.9,ROUNDDOWN(A1,1))+0.09

 

[markmzz]

Maybe this:

=IF(MOD(A1,1)>=0.7,INT(A1)+0.99,(INT(A1*10)+0.9)/10)

Or

=TRUNC(A1,MOD(A1,1)<0.7)+(MOD(A1,1)>=0.7)*0.9+0.09

Markmzz