Excel - Calulation incorrect when multiplying number with different formats

Question

Hi,

I have a very simple calculation to carry out however excel seems to have got the answer wrong for me.

I have a labour rate of £22 per hour in a cell and an output rate of 0.15 in another. When multiplying these 2 figures together the answer should be £3.30 however excel shows £2.70.

If I re-enter .15 in the spreadsheet in the browser version it re-calcs to £3.30 correctly however in excel desktop re-entering .15 the result of £2.70 does not change! 

It is not a decimal point error as I have checked this and not a calculation error as I have pressed F9 to re-calc and made sure auto calc was on in the options.

The only thing I can think of is;

1. Original spreadsheet was created in excel 2010 then edited in 2016
2. I previously used goal seek on the output rate cell (the 0.15)
3. Formats for the cells are different. Accounting for the rate and final calculation and number for the output rate.

It is puzzling what happened and could have been embarrassing if the I had not spotted this before submitting to a client.

Any help / comments appreciated.

Regards,

Kevin

Answer 1

Jared,

Copying the cells and pasting into a new sheet in the workbook does force a re-calc whether the formatting is cleared or not.

It just seems like a mystery as to why the sheet behaves the way it does, i.e. not re-calcing after you enter the same number and nothing happens when you press F9.

As I said earlier maybe it is because the workbook was originated in Excel 2010 and transferred to 2016 or because I had previously used goal seek on the factoring cell?

I wonder if there is any way someone from Microsoft is able to interrogate the sheet to see what is going on with it?

Many thanks

Kevin

Answer 2

Hi Kevin,

As I have no permission to download the Excel file via the link you provided, you could insert the original Excel file as attachment in the Private Message I 've sent to you for further investigation. You can get it via: private message.

Note: make sure you've removed all the private information.

After sharing the required information in PM, please notify us on the forum so that we can help you in time.

Thanks,

Jared

Answer 3

Jared,

File has been uploaded in the PM.

Many thanks

Kevin

Answer 4

Hi Kevin,

Thanks for your information.

When I open the file with Excel 2016 on my side, the Cost per m2 cell shows 2.70. After I enter the same number 22 and 0.15 in the two cells, the Cost per m2 cell keeps the same.

When I change the 22.00 and 0.15 to other numbers, the 2.70 cell works correctly and shows 3.30 after I re-enter the same number. This also happens when I double click the 2.70 cell and click enter after it shows =+C5*C7.

It seems that the formula works properly on my side and the root cause could be related to the transformation process from Excel 2010 to 2016 as you mentioned.

Regards,

Jared

Answer 5

Jared,

I'm pleased that you can replicate the error!

My issue is that this nearly caught me out in a commercial situation, it was just luck I spotted it.

Is there anything that can be done? Or can microsoft look into it?

Many thanks

Kevin