Formula to move decimal point 2 places to the left

[Inxcel]

Hi All,

I need a formula to take a number from cell A2, and move the decimal point 2 places to the left.

So if I have the value 10 entered into the cell A2, it will give me 0.1

=LEFT(A2,LEN(A2)-2)&"."&RIGHT((SUBSTITUTE(A2,".00","")),2)

The formula above works fine, except if the value 0 is entered into A2. I returns an error due to it being 0.

Any help greatly appreciated.

---------

Some further information in case you are wondering what I am trying to do

The cell A2 only ever has 2 values entered into it - 10 or 0. (These equate to a tax rate).

I want to get the value 10 so that it becomes 1.1 (using the above formula, and then adding 1 to it.
I want to get the value 0 so that it becomes 1.0 (if the formula above simply returned a 0 I would be right, but it returns an error).

I expect someone will have a very simple solution, but I have been looking at it for too long, and have excel block.

Cheers
Inxcel

 

[oldbrewer]

have a lookup table in first column 0 then 10 - in second column 1.0 then 1.1 - call it mytable

then if A1 contains 0 in B1 put =vlookup(A1, mytable,2)

 

[Rick Rothstein]

To get the value 1 or 1.1 from 0 or 10 in cell A2, use this formula...

=1+A2/100

 

[Inxcel]

Rick, thanks for the formula. Simple & did the job.

Oldbrewer, I will keep this in mind for next time.

Cheers
Inxcel

 

[oldbrewer]

lookup tables and sumproduct solve most things.....