Excel formula does not equal 0...

Turambar

New Member
Joined
Sep 8, 2014
Messages
4
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

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.
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)
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
What result do you get if you create a new sheet, enter the values manually then enter the formula?
 
Upvote 0

Forum statistics

Threads
1,223,428
Messages
6,172,043
Members
452,444
Latest member
ShaImran193

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?

Disable AdBlock

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
Back
Top