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.
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.