sumproduct issues

morning all.

Ok, I'm using sumproduct for a specific problem and I'm getting back a
miniscule value of 5.17E-13 as my difference.

The equation is:
=sumproduct((a1:a400"")*(f1:f400))
I then do an IF equation, and it is:
=IF(a401=600,"ok",600-a401)
for my IF answer, I get back 5.17E-13.
I've checked all 800 cells in both columns A, and F. There are no equations
in any of them. If column A has cells that are blank, then the value in F
that's related to columns B or C will be ignored. I have corresponding
sumproduct equations for columns B and C as well, and both those do exactly
as needed.

All the other instances of these work fine on this workbook.

Is there a way that I can stop these miniscule value differences from
showing?
And yes, I had thought about using Round();I'd rather not.

Your helps are deeply appreciated.
Best.

Hi
That happens because of the calculations not being round up, that means that
you have 12 ceros before the last number, -0.0000000000001, if you don;'t
want to use round you can format the column to 2 decimals or no decimals

"Steve" wrote:

morning all.

Ok, I'm using sumproduct for a specific problem and I'm getting back a
miniscule value of 5.17E-13 as my difference.

The equation is:
=sumproduct((a1:a400"")*(f1:f400))
I then do an IF equation, and it is:
=IF(a401=600,"ok",600-a401)
for my IF answer, I get back 5.17E-13.
I've checked all 800 cells in both columns A, and F. There are no equations
in any of them. If column A has cells that are blank, then the value in F
that's related to columns B or C will be ignored. I have corresponding
sumproduct equations for columns B and C as well, and both those do exactly
as needed.

All the other instances of these work fine on this workbook.

Is there a way that I can stop these miniscule value differences from
showing?
And yes, I had thought about using Round();I'd rather not.

Your helps are deeply appreciated.
Best.

Eduardo,
Thank you for the response.

I am aware of why it happens-- which was why I'd stated I'd checked all 800
cells for other equations-- in the event of that being the issue.
The real issue, as I understand it, has to do with how Excel calculated the
data. For some reason it came up with a binary remainder, where there is
none-- it's an Excel glitch.
It's irritating because the two sumproduct equations that I'm using for
columns B and C work fine, and do not result in the e-13 response. My if
equation returns the true response, as anticipated.


And setting the decimal value to 0.00, 0.0, or 0 isn't viable in this case
either.

What I'd like is to simply prevent it from taking place altogether--
expecially since there's no mathematical reason for it.


Again, thank you for your time.
Best.

"Eduardo" wrote:

Hi
That happens because of the calculations not being round up, that means that
you have 12 ceros before the last number, -0.0000000000001, if you don;'t
want to use round you can format the column to 2 decimals or no decimals

"Steve" wrote:

morning all.

Ok, I'm using sumproduct for a specific problem and I'm getting back a
miniscule value of 5.17E-13 as my difference.

The equation is:
=sumproduct((a1:a400"")*(f1:f400))
I then do an IF equation, and it is:
=IF(a401=600,"ok",600-a401)
for my IF answer, I get back 5.17E-13.
I've checked all 800 cells in both columns A, and F. There are no equations
in any of them. If column A has cells that are blank, then the value in F
that's related to columns B or C will be ignored. I have corresponding
sumproduct equations for columns B and C as well, and both those do exactly
as needed.

All the other instances of these work fine on this workbook.

Is there a way that I can stop these miniscule value differences from
showing?
And yes, I had thought about using Round();I'd rather not.

Your helps are deeply appreciated.
Best.