sumproduct - don't understand #VALUE result

[schwammrs]

Hi all--

I'm using this formula:

=SUMPRODUCT(--(Date>=B1),--(Date<E1),--(Category=A5),--(Out))

where Date, Category and Out are named ranges containing date values,
category names and dollar values respectively. So essentially, I'm adding
up all the "Out" values that fall within a certain date range and are equal
to a certain category.

If I set the ranges to be only the first 25 or 50 or so rows (all have data
in them), I get the total I expect.
If I set the ranges to be the last 25 or 50 or so rows (all are empty), I
get nothing, also as expected.

Here's where I start to be confused:
If I set the ranges to be the entire list of 700 rows, I get #VALUE. Huh?
Even if I set the ranges to just the first 300 rows (some with data, some
empty), I get #VALUE.

And even more confusin, if I remove the double unary from the Out part, e.g.
=SUMPRODUCT(--(Date>=B1),--(Date<E1),--(Category=A5),Out)
then it doesn't matter if the ranges are the first 25 or the last 25 or all
700 rows; I get the correct result, not #VALUE.

By the way, if I used actual ranges, e.g. S3:S703 instead of named ranges,
the same thing happens; using the double unary on all four components
sometimes gives me a #VALUE result whereas removing the double unary from
the fourth component (the only one that's a number already, not a condition)
is always correct.

Any ideas why this is happening. Obviously, I can just remove the double
unary to get my spreadsheet to work, but I don't like not understanding why
the first way doesn't always work. Obviously, I need the double unary (or
some other thing like 0+, etc) on the conditions to force them to be
numbers, and I don't NEED to use a double unary on something that's already
a number, but wouldn't a double unary in front of a number just keep it the
same number?

Thanks for any insight,
Karin
Excel2000

 

[RagDyeR]

I would guess that *all* your values in the "Out" range are *not true* XL
recognized numbers, but *text* values that just look like numbers.

One of the *advantages* of using the asterisk form is that anything that
*looks* like a number *will* calculate.
But, if the range contains *any* value that is not in a numerical form, it
will warn you by *not* calculating *anything*, and simply return an error.
This alerts you that you have contaminated (mixed) data, and you must check
your values.

On the other hand, if you *know* that you have "mixed" data in the range to
calculate, and you need (want) to continue calculating anyway, then you
should use the unary form.

The *bad* aspect of the unary form is that if you have "mixed" data, you
receive *no* warning.
The numeric values will compute ... the non-numeric will be by-passed ...
and you'll receive a return that *may* be completely erroneous, all without
your knowledge ... unless you physically check yourself.

You might consider yourself lucky that *all* your values are non-numeric,
where the unary form cannot calculate *anything*.
Think what you would do if you had "mixed" data, and you *did* receive a
return that was *wrong*.

In today's world of almost constant data downloading from the web, the
veracity of these downloaded values are almost always questionable as to
form and content.

For this reason, I always use the asterisk form unless I'm aware of a
condition where the unary becomes necessary.
And those cases are few and far between.
--

HTH,

RD
=====================================================
Please keep all correspondence within the Group, so all may benefit!
=====================================================


Hi all--

I'm using this formula:

=SUMPRODUCT(--(Date>=B1),--(Date<E1),--(Category=A5),--(Out))

where Date, Category and Out are named ranges containing date values,
category names and dollar values respectively. So essentially, I'm adding
up all the "Out" values that fall within a certain date range and are equal
to a certain category.

If I set the ranges to be only the first 25 or 50 or so rows (all have data
in them), I get the total I expect.
If I set the ranges to be the last 25 or 50 or so rows (all are empty), I
get nothing, also as expected.

Here's where I start to be confused:
If I set the ranges to be the entire list of 700 rows, I get #VALUE. Huh?
Even if I set the ranges to just the first 300 rows (some with data, some
empty), I get #VALUE.

And even more confusin, if I remove the double unary from the Out part, e.g.
=SUMPRODUCT(--(Date>=B1),--(Date<E1),--(Category=A5),Out)
then it doesn't matter if the ranges are the first 25 or the last 25 or all
700 rows; I get the correct result, not #VALUE.

By the way, if I used actual ranges, e.g. S3:S703 instead of named ranges,
the same thing happens; using the double unary on all four components
sometimes gives me a #VALUE result whereas removing the double unary from
the fourth component (the only one that's a number already, not a condition)
is always correct.

Any ideas why this is happening. Obviously, I can just remove the double
unary to get my spreadsheet to work, but I don't like not understanding why
the first way doesn't always work. Obviously, I need the double unary (or
some other thing like 0+, etc) on the conditions to force them to be
numbers, and I don't NEED to use a double unary on something that's already
a number, but wouldn't a double unary in front of a number just keep it the
same number?

Thanks for any insight,
Karin
Excel2000

 

[Bernard Liengme]

Hello:
Are you sure about this?
I filled a1:A5 with 1's and B1:B5 with 1,2,3,4,5
The four formulas
a) =SUMPRODUCT($A$1:$A$5,$B$1:$B$5)
b) =SUMPRODUCT($A$1:$A$5,1*($B$1:$B$5))
c)=SUMPRODUCT($A$1:$A$5,--($B$1:$B$5))
d) =SUMPRODUCT(($A$1:$A$5)*($B$1:$B$5))
gave the expected value of 15
When I entered an apostrophe in front of the 3, formula (a) gave the result
12 while (b) thru (d) gave 15. And, as expected, replacing the 3 by the
letter 'a' produced the result 12 again with (A) and #VALUE! with the
others.

Surely multiplication by 1 ( the * form) and twice changing the sign (the
double unary negation form) each perform an arithmetic operation; so each
will force a quasi-number to be numeric. I would like to know more about why
* and -- are considered to have different behaviours.
Thanks

 

[Ragdyer]

Bernard, you're right ... when you use the unary in conjunction with the
numerical range.

However, most, if not all of the formulas one sees posted, do *not* include
the unary with that numerical range.
The conventional form of the unary is:
=SUMPRODUCT(--(A1:A50=E1),--(B1:B50=E2),D150)
A prime example of suggested formulas.

The conventional asterisk form *automatically* forces the numerical range
into an arithmetic operation.
=SUMPRODUCT((A1:A50=E1)*(B1:B50=E2)*D150)

In re-reading the OP, I would re-think that when he says that his formula
*works* when he eliminates the unary from the numerical range, what he
*really* means is that he *doesn't* get an error message.

I'll bet if he took the time to check his 700 rows, he'd find that even
though he *did* get a numerical answer, the returned value was *wrong*,
since the elimination of the arithmetic operator from the numerical range
*allowed* Sumproduct to *by-pass* contaminated (mixed) data values.

My contention, is that by using the *conventional* asterisk form, where all
ranges are automatically exposed to an operator, the user is aware of the
fact that an error message is *warning* him that his data is compromised.

I believe the OP's confusion here is testimony to what I'm advocating.

 

[Bernard Liengme]

"got ya!"
thanks