Glaswegian
06-13-2014, 01:12 AM
Hi
I'm having a problem extracting figures from a cell.
I have a calculation that produces a total - this total is then rounded to 2 decimal places (because the true value of the calculation is 5 decimal places). I need to extract only 2 digits after the decimal place. I can do this fine in most cases but I have a problem where the rounded value is anything like .50 or where it is a whole number.
I'm using the following formulae:
=IF(U42="","",IF(LEN(U42)<3,"0",MID((U42),LEN(U42)-1,1))) - for the first number after the decimal,
=IF(U42="","",IF(LEN(U42)<2,"0",RIGHT(U42,1))) - for the second number after the decimal.
For example, if the final total is 2,011.00, the "11" is returned as the final 2 digits when it should be "00".
If the number is a whole number then the LEN function is the one that's the problem (I think) as it does not see any decimal or the zeros after that.
I've no doubt there is a better way of doing this and would greatly appreciate any assistance.
I'm having a problem extracting figures from a cell.
I have a calculation that produces a total - this total is then rounded to 2 decimal places (because the true value of the calculation is 5 decimal places). I need to extract only 2 digits after the decimal place. I can do this fine in most cases but I have a problem where the rounded value is anything like .50 or where it is a whole number.
I'm using the following formulae:
=IF(U42="","",IF(LEN(U42)<3,"0",MID((U42),LEN(U42)-1,1))) - for the first number after the decimal,
=IF(U42="","",IF(LEN(U42)<2,"0",RIGHT(U42,1))) - for the second number after the decimal.
For example, if the final total is 2,011.00, the "11" is returned as the final 2 digits when it should be "00".
If the number is a whole number then the LEN function is the one that's the problem (I think) as it does not see any decimal or the zeros after that.
I've no doubt there is a better way of doing this and would greatly appreciate any assistance.