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

Hi Kevin,

The temporarily calculation stuck could be caused by multiple reasons like the unstable transformation process and the previous manual settings in this Excel file. When we enter the same numbers, the formula won't be triggered. Instead, when we enter the different numbers, it will force the formula to operate which will re-calculate properly. Glad that the Excel 2010 and 2016 work properly and this issue does not repeat in other workbooks which won't influence your normal work. We appreciate your understanding and thanks for all your time and effort on this.

Regards,

Jared

Answer 2

Jared,

OK well it's still a mystery then!

I'll keep an eye out to see if the error repeats at all.

In the mean time I'll upgrade my 2010 to 2016, that may help.

Thanks for your help.

Regards,

Kevin