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.
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.