PDA

View Full Version : [SOLVED] MID Formula help - when last digit value is 0



Glaswegian
02-14-2014, 04:10 AM
Hi

Long time no see! Stuck with a formula again.

I have sheet where users input one set of numbers, a second set is determined by a calculation and the third set is the difference between set 1 and set 2. Each digit is in a cell, rather than the full value being in a cell. The actual calculation for set 3 is done 'out of sight' and the full value is in its own cell. I'm using a mixture of LEFT, RIGHT and MID formulae to determine the values for the third set and place each digit in its own cell. All works fine unless the final digit of set 3 is a zero. So a result of 99.78 is no problem - I can bring back the individual digits to their own cells. But a result of 99.80 results in problems - it shows as 99..8. The decimal point is ignored as I have a separate cell for that, so I can concentrate on the numbers. But when the last digit is 0, my formula returns the decimal point and seems to ignore the zero. The calculation start point is simply

=AH15-AH16

and the cell is formatted to 2 decimal places.

For the values after the decimal point I'm using

=IF(P17="","",RIGHT(AH17,1))

and then

=IF(O17="","",MID(AH17,LEN(AH17)-1,1))


Formulae for digits preceeding the decimal point work fine.

Hopefully I've explained this clearly.

I was never very good at formulae so any help would be greatly appreciated. Thanks.

Paul_Hossler
02-14-2014, 05:50 AM
try this formula in the right places, maybe a helper column



=--TEXT(ROUND($A$1,2),"#0.00")


Or post a sample workbook

Paul

snb
02-14-2014, 07:01 AM
1/100:

=RIGHT(TEXT(A22,".00"),1)

1/10

=RIGHT(TEXT(A22,".0"),1)

Glaswegian
02-14-2014, 07:08 AM
Thanks to you both for your replies.

snb - that works a treat -thanks!