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.

sumproduct issues
 

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.

sumproduct issues
 

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.