I am using SUMPRODUCT and passing the whole
INDIRECT reference twice.

If you want the sum of squares:

=SUMPRODUCT(INDEX(INDIRECT("A"&ROW()-7&":A"&ROW()-6),,1)^2)


--
Biff
Microsoft Excel MVP


"JB99" wrote in message
...
Excellent!!

Thanks so much, Biff - I was unaware that ROW() returned an array.

I actually called Microsoft support, paid them $49, and they are "looking
into it." After 3 hours, I still haven't heard anything.

I didn't just use SUM, because I actually want the sum of the squares of
each cell in the range - so I am using SUMPRODUCT and passing the whole
INDIRECT reference twice. I am using ROW() because this is a sliding
window
of values, so each new row of calculation needs to use a new set of cells
to
calculate the sum of the squares.

It's all working now - I really appreciate the help.

Jeff


"T. Valko" wrote:

=SUMPRODUCT(INDIRECT("A" & ROW()-7 & ":A" & ROW()-6))

It's because ROW returns an array. Even thought the array is a single
element it's still an array. INDIRECT passes the *array* of references to
SUMPRODUCT which it can't handle. I've seen others refer to this as
dereferencing.

Normally you can get around this by using either the N() or T()
functions.
N() for numeric values and T() for text values. However, in this case it
still doesn't work because for some reason N() is only recognizing the
first
element of the references:

A1 = 10
A2 = 20

Formula entered in A8:

=SUMPRODUCT(N(INDIRECT("A"&ROW()-7&":A"&ROW()-6)))

Returns 10

So, we solved one problem and stumbled upon another!

I can't figure out why N() isn't passing the whole array. You'd think it
should since SUMPRODUCT works with arrays.

However, all is not lost! This works:

=SUMPRODUCT(INDEX(INDIRECT("A"&ROW()-7&":A"&ROW()-6),,1))

Returns 30.

But why use that when you can use this:

=SUM(INDIRECT("A"&ROW()-7&":A"&ROW()-6))


--
Biff
Microsoft Excel MVP


"RagDyeR" wrote in message
...
You still haven't stated any particular calculation you're trying to
complete.

If you're simply commenting ... yes ... I agree it's strange.

If you're looking for a reason or explanation ... sorry, I don't have
one.
--

Regards,

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

"JB99" wrote in message
...
Thanks RagDyer,

Sorry if I wasn't clear:

Although
=SUM(INDIRECT("A" & ROW()-7 & ":A" & ROW()-6))
DOES work,

=SUMPRODUCT(INDIRECT("A" & ROW()-7 & ":A" & ROW()-6))
returns #VALUE!

Seems strange to me!


"RagDyer" wrote:

What exactly are you trying to do?

Since you say that you need to reference the current row,
AND
=SUM(INDIRECT("A" & ROW()-7 & ":A" & ROW()-6))
*DOES* work,

What *doesn't* work?
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may
benefit !
---------------------------------------------------------------------------
"JB99" wrote in message
...
I'm encountering a strange issue, I was hoping that some experts
here
might
be able to help.

If I enter the formula: =SUMPRODUCT(INDIRECT("A1:A2")), and put
values
in
A1
and A2, then this formula will correctly sum them (since there is no
2nd
array, SUMPRODUCT simply sums the values in the 1st arry.

However, if I enter the following formula into cell A8 (or any other
cell,
this is just to make an example):

=SUMPRODUCT(INDIRECT("A" & ROW()-7 & ":A" & ROW()-6))

Then I get a #VALUE! error.

There seems to be something about using SUMPRODUCT, INDIRECT, and
ROW
all
together that causes this, because if I replace SUMPRODUCT with a
simple
SUM,
i.e.:

=SUM(INDIRECT("A" & ROW()-7 & ":A" & ROW()-6))

Then I get an answer!

Or if I simply try:

=SUMPRODUCT(INDIRECT("A1:A2"))

However I'd like to be able to base this formula on the current row,
so
need
to use all 3 together... Any ideas?????

Thanks.