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.

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.

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.

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.

=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.

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.

I can tell you one thing, people get far better support by volunteers here
than by paying MS
They simply have no clues about the latest development of tricky formulas,
just look at how sparse help is about these things, look at the help for
SUMPRODUCT which is only about what it was first intended to do A1*B1+A2*B2
and so on. I would be surprised if you get a correct answer from them, in
fact it would be interesting to see what they say. If you get an answer
could you post it here?



--


Regards,


Peo Sjoblom


"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.

And if the answer is not as good as Biff's, can you claim your money
back ?

Pete

On Nov 2, 6:47 pm, "Peo Sjoblom" wrote:
I can tell you one thing, people get far better support by volunteers here
than by paying MS
They simply have no clues about the latest development of tricky formulas,
just look at how sparse help is about these things, look at the help for
SUMPRODUCT which is only about what it was first intended to do A1*B1+A2*B2
and so on. I would be surprised if you get a correct answer from them, in
fact it would be interesting to see what they say. If you get an answer
could you post it here?

--

Regards,

Peo Sjoblom

"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.- Hide quoted text -

- Show quoted text -

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.

You bring up a good point, Peo.

Seems to me that MS *should* have support personnel that can solve/answer
these types of requests for support and it shouldn't take hours or days.

Are they hiring? I need a job!

--
Biff
Microsoft Excel MVP


"Peo Sjoblom" wrote in message
...
I can tell you one thing, people get far better support by volunteers here
than by paying MS
They simply have no clues about the latest development of tricky formulas,
just look at how sparse help is about these things, look at the help for
SUMPRODUCT which is only about what it was first intended to do
A1*B1+A2*B2 and so on. I would be surprised if you get a correct answer
from them, in fact it would be interesting to see what they say. If you
get an answer could you post it here?



--


Regards,


Peo Sjoblom


"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.

Just to add...

I'm sure the OP's experience isn't uncommon.

The person answering the phone at MS doesn't necessarily need to know the
answer but they should take the information and be able to route the call to
someone that knows the answer.

--
Biff
Microsoft Excel MVP


"T. Valko" wrote in message
...
You bring up a good point, Peo.

Seems to me that MS *should* have support personnel that can solve/answer
these types of requests for support and it shouldn't take hours or days.

Are they hiring? I need a job!

--
Biff
Microsoft Excel MVP


"Peo Sjoblom" wrote in message
...
I can tell you one thing, people get far better support by volunteers here
than by paying MS
They simply have no clues about the latest development of tricky
formulas, just look at how sparse help is about these things, look at the
help for SUMPRODUCT which is only about what it was first intended to do
A1*B1+A2*B2 and so on. I would be surprised if you get a correct answer
from them, in fact it would be interesting to see what they say. If you
get an answer could you post it here?



--


Regards,


Peo Sjoblom


"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.

Are you joking about that "need a job"?

Is retirement "getting to you"?

I'm seriously getting to (thinking about) that point.

I'm afraid that I *need* a place to go to every morning!

Hear too many "bad" things about acquaintances who have gone that route.


--

Regards,

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

"T. Valko" wrote in message
...
You bring up a good point, Peo.

Seems to me that MS *should* have support personnel that can solve/answer
these types of requests for support and it shouldn't take hours or days.

Are they hiring? I need a job!

--
Biff
Microsoft Excel MVP


"Peo Sjoblom" wrote in message
...
I can tell you one thing, people get far better support by volunteers here
than by paying MS
They simply have no clues about the latest development of tricky formulas,
just look at how sparse help is about these things, look at the help for
SUMPRODUCT which is only about what it was first intended to do
A1*B1+A2*B2 and so on. I would be surprised if you get a correct answer
from them, in fact it would be interesting to see what they say. If you
get an answer could you post it here?



--


Regards,


Peo Sjoblom


"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.