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,

From your description, I noticed that the calculation works fine in Excel Online and you perform the calculation of the spreadsheet which is created in Excel 2007 in Excel 2016. So to check if the issue is related to the Excel clients of the different versions, I suggest you create a new spread with the formats which are the same as the old one’s via Excel 2016 to do a test and let us know the outcome.

Moreover, for further troubleshooting, I’d like to know the formulas of the values you set in the spreadsheet. Some detailed information about them are appreciated.

Thanks,

Cliff

Answer 2

Cliff,

Many thanks for the swift response.

I have tried to re-create the error in excel 2016 and 2010 but cannot. The calculation works perfectly. I really have hit on an obscure error however it could have been potentially damaging for me if not spotted!

I attach a link to the file in order you can look at it properly.

https://xxx-my.sharepoint.com/personal/kevin\_xxx\_onmicrosoft\_com/\_layouts/15/guestaccess.aspx?guestaccesstoken=ZW60iE5PlpkRBL%2bgMHSOQEM8nD6q%2fEx2Z6jVUCu%2f%2b%2bI%3d&docid=09dd24c0eb6704000a7609a41e030af53&rev=1&expiration=2016-08-27T07%3a43%3a17.000Z

If you open the wash and wear, eggshell and matt or eggshell light and space tab you can see that the cost per m2 is £2.70. It is a simple calculation of the multiplication of 2 cells. It should be £3.30.

If the workbook is edited online and the factor cell C8 is changed the value will change (even if you use the same value 0.15), however if this is edited in excel desktop (either 2010 or 2016) with the same value 0.15 in C8 then nothing happens, it should recalculate to 3.30, most peculiar.

Any help appreciated!

Regards,

Kevin

Answer 3

Hi Kevin,

Sorry for my delay. I'd like to know if you set the calculation formula in Cost per m2 column as below:

If yes, you could first select the part of the table in desktop Excel app > Home > click the eraser icon on the Editing section > Clear formats. Then recreate the formula to check if the multiplication result shows properly. If yes, you can change the format of the cells then.

If not, you can try to create a new Excel sheet and copy the part in the picture above after clearing the formats to check the outcome.

Besides, to protect your privacy, i've masked your private information.

Regards,

Jared

Answer 4

Jared,

Thank for the reply.

The formula I entered had a plus sign, that the only difference i.e. =+B6*B8

I tried clearing the formats, it did not re-calc, even nothing after pressing F9.

I then tried copying the cells to a new workbook, when you paste into a blank workbook the cells re-calc perfectly. This is the same either before or after clearing the formats.

Somehow the calculation has got 'stuck' in the sheet.

Have you downloaded the sheet and tried typing 0.15 in the hours/m2 cell. Nothing happens, although in theory it should re-calc.

Many thanks

Kevin

Answer 5

Hi Kevin,

As you mentioned the cells work properly after pasted them to a new workbook. Therefore, the issue could be related to the custom settings on this specific sheet. You may try to create a new sheet in the workbook, and paste the problematic sheet after cleared all formatting in it to the new sheet to check if this could be the workaround.

Regards,

Jared