# Excel formula does not equal 0...

#### Turambar

##### New Member
This is my first post so please bare with me.

I have a table in excel which basically shows the amount of money somebody owes me as a debt, the amount of extra money added on that they owe and then the amount they have paid and the amount I have written off.

I use a very simple formula to add together the amount they owe to the additional amounts and then take away the amount paid and the amount written off. As the sheet in question is for settled debts the total should always be zero. In addition I have the table se up as accounting so a zero value shows as £ - and I have conditional formatting set up to make a cell with a zero value green.

The issue that I have is that one of the cells does not evaluate to 0 but rather a figure with a long decimal place. The formula is =SUM(F27+J27-G27-K27)

The Values for the cells are: F27: 630.20, G27: 0, J27: 74.98, K27: 705.18

This means the formula is effectively: 630.20+74.98-0-705.18

This should of course equal Zero or in my case a dash. Unfortunately excel thinks it equals '0.000000000000113686837721616000'

I have done everything I can think of such as changing the formula around, removing the blank space, re-entering the figures etc. I now avail myself of the collective knowledge of the internet.

### Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.
Try

=ROUND(F27+J27-G27-K27,2)

Click on each cell in turn.
What does the value say just above column D?
You'll prob find one of the values is something like 630.200000000000113686837721616000 hence the long decimal.
Or simply use

=round(F27+J27-G27-K27,2)

I'm not sure why Excel is adding that string at the end of your number, but to get rid of it, you can use ROUND.

=ROUND(SUM(F27+J27-G27-K27),2)

That will round the answer to the nearest hundredth, so Excel will set the number to 0.00 (how it displays will depend on the display settings of that cell), rather than
0.000000000000113686837721616000.
If you want it to round to the nearest whole number, you would put ",0)" at the end instead of the ",2)".

It should allow your conditional formatting on cells equal to zero to work.

Rounding it as suggested does work, but then so does manually entering '0' as the value for the cell.

I want to know why excel is evaluating he formula incorrectly. Sorry if I wasn't clear in my initial question.

There are no long values in any of the cells. As I said, I have tried to re-enter each of the values manually and it still evaluates incorrectly.

What result do you get if you create a new sheet, enter the values manually then enter the formula?

Thank you for this link. I have turned on 'Set precision as displayed' in the workbook since all values should be no more than two decimal places.

This seems to be an odd bug though and one I would have expected Excel to iron out by now, especially for such simple situations such as mine!

Replies
3
Views
373
Replies
0
Views
164
Replies
2
Views
140
Replies
0
Views
381
Replies
6
Views
457

1,203,317
Messages
6,054,706
Members
444,742
Latest member
jmartin9247

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

### Which adblocker are you using?

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

### Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back