sumproduct returning odd results




I'm receiving a strange result with my sumproduct on a few, but not
all, executions of the function.

Most work well, but in a few odd cases, the numbers come back close,
but not exact.

For instance, I have the following values.

C. 12.5
C. 1.3
C. 6.5

Now, there are more values above and below, but for this sumproduct,
these are the values it will utilize.

So, my function looks like this:

=SUMPRODUCT(('sheet3'!$A$2:$A$600="" & A19 &"")*('sheet3'!$G$2:$G

The function sits on another page. Within that page, I have a list of
unique values, with one of them being C. The sumproduct
function goes back to a different sheet (sheet 3) and looks for C. (The A19 reference would be the cell that my search value C. sits in; for dynamic purposes since this is not the only
value I'm searching for). If it finds it, it multiplies the result by
the value in the corresponding G cell. So, for the above, it is
finding 12.5, 1.3 and 6.5 respectively. The issue with this
particular result is it is returning 20.34 instead of just 20.3.
Now, there should not be any more than 1 decimal place. I don't have
any values in the G column which have more than 1 decimal place. In
addition, others cells add fine for other results. But, a few of
these happen to return really odd results and I can't seem to find a
pattern. Additionally, I've made sure that the G column values are
all formatted as decimals.

Any thoughts?




Increase the number of decimal places that can be seen in column G, to
check what values you have. Your second value, for example, may really
be 1.34, but you can't see that if you are only displaying 1dp.

Hope this helps.


Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question