Formula
-
Hallo all I'm having a problem with some data that I've taken from SQL and copied into an Excel spreadsheet. Please try this and type the following into these cells:
A1 : -982.21
B1 : 908.66
C1 : -73.55
D1 : =IF(A1+B1=C1,"Correct","Incorrect")D1 shows "incorrect" which doesn't make sense since A1 + B1 does equal C1. This happens on a couple of rows, I've checked and they should show correct. I have also tried using the SUM function instead of adding A1+B1 and I even tried (IF C1-B1=A1) but it didn't work either. I have formatted the columns as decimal numbers. Am I missing something really simple here? Any clues would be appreciated Thanks
There are 10 types of people in the world, those who understand binary and those who dont.
-
Hallo all I'm having a problem with some data that I've taken from SQL and copied into an Excel spreadsheet. Please try this and type the following into these cells:
A1 : -982.21
B1 : 908.66
C1 : -73.55
D1 : =IF(A1+B1=C1,"Correct","Incorrect")D1 shows "incorrect" which doesn't make sense since A1 + B1 does equal C1. This happens on a couple of rows, I've checked and they should show correct. I have also tried using the SUM function instead of adding A1+B1 and I even tried (IF C1-B1=A1) but it didn't work either. I have formatted the columns as decimal numbers. Am I missing something really simple here? Any clues would be appreciated Thanks
There are 10 types of people in the world, those who understand binary and those who dont.
Rounding errors in the calculation are probably the cause. I'd imagine that excel uses decimal numeric type because it is used so much for financial calculations, while the FLOAT type in the database is just that.
Upcoming events: * Glasgow: Mock Objects, SQL Server CLR Integration, Reporting Services, db4o, Dependency Injection with Spring ... * Reading: Developer Day 5 Ready to Give up - Your help will be much appreciated. My website
-
Rounding errors in the calculation are probably the cause. I'd imagine that excel uses decimal numeric type because it is used so much for financial calculations, while the FLOAT type in the database is just that.
Upcoming events: * Glasgow: Mock Objects, SQL Server CLR Integration, Reporting Services, db4o, Dependency Injection with Spring ... * Reading: Developer Day 5 Ready to Give up - Your help will be much appreciated. My website
I think you're right. I used the SUM function on -982.21 and 908.66. I then increased the decimal places on the answer cell to 25 and I saw this -73.5500000000001000000000000 The previous values don't have anything after their first 2 decimal places so I don't know where that 1 is coming from. Will make a plan though. Thanks for your help ;)
There are 10 types of people in the world, those who understand binary and those who dont.
-
Hallo all I'm having a problem with some data that I've taken from SQL and copied into an Excel spreadsheet. Please try this and type the following into these cells:
A1 : -982.21
B1 : 908.66
C1 : -73.55
D1 : =IF(A1+B1=C1,"Correct","Incorrect")D1 shows "incorrect" which doesn't make sense since A1 + B1 does equal C1. This happens on a couple of rows, I've checked and they should show correct. I have also tried using the SUM function instead of adding A1+B1 and I even tried (IF C1-B1=A1) but it didn't work either. I have formatted the columns as decimal numbers. Am I missing something really simple here? Any clues would be appreciated Thanks
There are 10 types of people in the world, those who understand binary and those who dont.
If you haven't solved it already - Try:
=IF(ROUND(A1+B1-C1,5)=0,"Correct","Incorrect") -
If you haven't solved it already - Try:
=IF(ROUND(A1+B1-C1,5)=0,"Correct","Incorrect")