Difference in result between SUMIF and the usual addition

YasserKhalil
PlatinumLounger
Posts: 4911
Joined: 31 Aug 2016, 09:02

Difference in result between SUMIF and the usual addition

Post by YasserKhalil »

Hello everyone

I have a code that extracts the unique dates and sum the values corresponding to the dates .. No problem at the code
The code contains that line

Code: Select all

a(.Item(txt), 3) = Val(a(.Item(txt), 3)) + Val(a(i, 5))
That line do the SUM process ..

I have manually used SUMIF to make sure of the results and most of the results were the same except two dates. There are a little bit difference in the result of summation between the code and the SUMIF
for example: the sumif result is 98091.3679999999
while the result of the code is 98091.368
I know the difference is a lit bit and no worth but I would like to know why the difference occurs

By the way in the code I tried using Application.SUM and application.WorksheetFunction.SUM but the same problem is still there. So the SUMIF is more accurate or what?!

User avatar
HansV
Administrator
Posts: 78235
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Difference in result between SUMIF and the usual addition

Post by HansV »

Both Excel and VBA store floating point numbers in binary format. During conversion between decimal and binary, and during calculations, small rounding errors occur. They are unavoidable, and it is unpredictable where exactly they will occur.
If you know that your numbers are accurate to a certain number of decimal places, it is best to round the result of your calculations to that number of decimal places.
In your example, it looks like the result should have 3 decimal places. If you round 98091.3679999999 to 3 decimal places, you'll get 98091.368.
Best wishes,
Hans

YasserKhalil
PlatinumLounger
Posts: 4911
Joined: 31 Aug 2016, 09:02

Re: Difference in result between SUMIF and the usual addition

Post by YasserKhalil »

Thanks a lot Mr. Hans
But I didn't use any rounding functions in the code ..what if I need to get the exact result as SUMIF?

User avatar
HansV
Administrator
Posts: 78235
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Difference in result between SUMIF and the usual addition

Post by HansV »

Why would you want to get the same result as SUMIF? The correct result is most probably 98091.368
Best wishes,
Hans

YasserKhalil
PlatinumLounger
Posts: 4911
Joined: 31 Aug 2016, 09:02

Re: Difference in result between SUMIF and the usual addition

Post by YasserKhalil »

Do you mean that the original addition that used in VBA is more accurate than the SUMIF?

User avatar
HansV
Administrator
Posts: 78235
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Difference in result between SUMIF and the usual addition

Post by HansV »

That seems probable. You can get the same result from the formula using

=ROUND(SUMIF(...),3)
Best wishes,
Hans

YasserKhalil
PlatinumLounger
Posts: 4911
Joined: 31 Aug 2016, 09:02

Re: Difference in result between SUMIF and the usual addition

Post by YasserKhalil »

Thank you very much Mr. Hans
Best and Kind Regards