Extracting values from cell where total has been rounded

[Glaswegian]

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.

[snb]

Please post a sample workbook.

[Glaswegian]

Hi

Thanks for your reply - workbook attached. All confidential data etc has been removed but you should get the idea.

[snb]

See the attachment

[Glaswegian]

Hi snb

Thanks for your help.

Perhaps I'm doing something wrong, but when I change the figures in column H, your figures do not seem to change. Is there something I should be doing or have done incorrectly?

[snb]

Make calculation 'automatic'.

[Glaswegian]

Hi

Yes my Calculation is always set to Automatic - that was the first thing I checked!

Edit - it is working from a calculation point, but your figures after the decimal point are not correct?

[Glaswegian]

Thought it might help if attached a fresh workbook with changed values

[snb]

It looks as if the sheet has to be kissed awake by:


Sub M_snb()
[sheet1!G21:P21] = [sheet1!G21:P21].Formula
End Sub

[Glaswegian]

Hi

Strange...doh! that produces an "Out of Stack" error message.

[snb]

I can't reproduce anything you are talking about.
I doubt whether we use the same workbook.
I used the last one you posted.
I hope you are not Apple oriented ?

[Glaswegian]

Hi

I suspect it was another workbook (which I thought I had closed) causing problems. I had to restart Excel - I'm using Excel 2010 on Windows 7 (just been upgraded). I'm at work so won't be able to reply again until Monday. No more crashes or errors messages though.

I do still have the issue where if the total is a round amount, i.e. no pence, then your formula does not seem to work. It works fine as long as there are figures after the decimal.

[yoslick11]

Hi, you are trying to read trailing zeroes. The best solution is to bring the last two digits before the period by multiplying by 100.
1st decimal: =LEFT(RIGHT(U42*100,2),1)
2nd decimal: =LEFT(RIGHT(U42*100,1),1)

[Glaswegian]

Many thanks to you both. Problem now solved!