OFFSET() not working within a SUMPRODUCT() when refering to a range in a pivot table
 

Hello all,

I am having a problem where I'm using 2 OFFSET() functions within a SUMPRODUCT() function. The first offset is working and refers to a range of 4 cells immediately above the cell I'm entering the formula in.

The second OFFSET() is not working. It refers to a range of 4 cells a calculated number of columns to left of the one I'm entering the formula in. The calculated column happens to be a Data column in a pivot table (there is no "Columns" field in the pivot table, so this column is just a Totals column).

When I step through this formula with the "Evaluate Formula" Formula Auditing Tool, the second OFFSET() returns {#VALUE!} error. If I isolate the problematic OFFSET() function and then wrap a SUM() function around it, it returns the correct value.

Does anyone know what is going on here? Is what I'm trying to do possible? Is there just some small error in my syntax?

Here are the formulas:

This is the non-working formula:
=IF(AND($AA9<"",$AB9<""),SUMPRODUCT((LoanDetail! $R$2:$R$998=$F9)*(LoanDetail!$E$2:$E$998)*(LoanDet ail!$F$2:$F$998))/$G9,IF(AND($AA9="",$AB9<""),SUMPRODUCT(OFFSET(M9,-$AB9,0,$AB9,1),OFFSET(M9,-$AB9,COLUMN($G9)-COLUMN(),$AB9,1))/$G9,""))

(For those of you who can see it, I've bolded the problematic OFFSET()function.)

And here is the working formula:
=SUM(OFFSET(M9,-$AB9,COLUMN($G9)-COLUMN(),$AB9,1))

(Notice that it is the exact same OFFSET() isolated then a SUM() wrapped around it.)




Thanks for any help anyone can provide,

Conan Kelly

OFFSET() not working within a SUMPRODUCT() when refering to a range in a pivot table
 

Hi

I would have thought that you needed 998 rather $AB9 as the height of the offset.

=IF(AND($AA9<"",$AB9<""),SUMPRODUCT((LoanDetail! $R$2:$R$998=$F9)*(LoanDetail!$E$2:$E$998)*(LoanDet ail!$F$2:$F$998))/$G9,IF(AND($AA9="",$AB9<""),SUMPRODUCT(OFFSET(M9,-$AB9,0,$AB9,1),OFFSET(M9,-$AB9,COLUMN($G9)-COLUMN(),998,1))/$G9,""))

--
Regards

Roger Govier


"Conan Kelly" wrote in message ...
Hello all,

I am having a problem where I'm using 2 OFFSET() functions within a SUMPRODUCT() function. The first offset is working and refers to a range of 4 cells immediately above the cell I'm entering the formula in.

The second OFFSET() is not working. It refers to a range of 4 cells a calculated number of columns to left of the one I'm entering the formula in. The calculated column happens to be a Data column in a pivot table (there is no "Columns" field in the pivot table, so this column is just a Totals column).

When I step through this formula with the "Evaluate Formula" Formula Auditing Tool, the second OFFSET() returns {#VALUE!} error. If I isolate the problematic OFFSET() function and then wrap a SUM() function around it, it returns the correct value.

Does anyone know what is going on here? Is what I'm trying to do possible? Is there just some small error in my syntax?

Here are the formulas:

This is the non-working formula:
=IF(AND($AA9<"",$AB9<""),SUMPRODUCT((LoanDetail! $R$2:$R$998=$F9)*(LoanDetail!$E$2:$E$998)*(LoanDet ail!$F$2:$F$998))/$G9,IF(AND($AA9="",$AB9<""),SUMPRODUCT(OFFSET(M9,-$AB9,0,$AB9,1),OFFSET(M9,-$AB9,COLUMN($G9)-COLUMN(),$AB9,1))/$G9,""))

(For those of you who can see it, I've bolded the problematic OFFSET()function.)

And here is the working formula:
=SUM(OFFSET(M9,-$AB9,COLUMN($G9)-COLUMN(),$AB9,1))

(Notice that it is the exact same OFFSET() isolated then a SUM() wrapped around it.)




Thanks for any help anyone can provide,

Conan Kelly

OFFSET() not working within a SUMPRODUCT() when refering to a range in a pivot table
 

BUMP

Hello All,

Sorry for bumping, but I'm still looking for a solution to this issue. Does anyone know what is going on?

Thanks again for everyone's help.

Conan


"Conan Kelly" wrote in message ...
Hello all,

I am having a problem where I'm using 2 OFFSET() functions within a SUMPRODUCT() function. The first offset is working and refers to a range of 4 cells immediately above the cell I'm entering the formula in.

The second OFFSET() is not working. It refers to a range of 4 cells a calculated number of columns to left of the one I'm entering the formula in. The calculated column happens to be a Data column in a pivot table (there is no "Columns" field in the pivot table, so this column is just a Totals column).

When I step through this formula with the "Evaluate Formula" Formula Auditing Tool, the second OFFSET() returns {#VALUE!} error. If I isolate the problematic OFFSET() function and then wrap a SUM() function around it, it returns the correct value.

Does anyone know what is going on here? Is what I'm trying to do possible? Is there just some small error in my syntax?

Here are the formulas:

This is the non-working formula:
=IF(AND($AA9<"",$AB9<""),SUMPRODUCT((LoanDetail! $R$2:$R$998=$F9)*(LoanDetail!$E$2:$E$998)*(LoanDet ail!$F$2:$F$998))/$G9,IF(AND($AA9="",$AB9<""),SUMPRODUCT(OFFSET(M9,-$AB9,0,$AB9,1),OFFSET(M9,-$AB9,COLUMN($G9)-COLUMN(),$AB9,1))/$G9,""))

(For those of you who can see it, I've bolded the problematic OFFSET()function.)

And here is the working formula:
=SUM(OFFSET(M9,-$AB9,COLUMN($G9)-COLUMN(),$AB9,1))

(Notice that it is the exact same OFFSET() isolated then a SUM() wrapped around it.)




Thanks for any help anyone can provide,

Conan Kelly

OFFSET() not working within a SUMPRODUCT() when refering to a range in a pivot table
 

Did you see Roger's answer?

--
Regards,

Peo Sjoblom


"Conan Kelly" wrote in message
...
BUMP

Hello All,

Sorry for bumping, but I'm still looking for a solution to this issue. Does
anyone know what is going on?

Thanks again for everyone's help.

Conan


"Conan Kelly" wrote in message
...
Hello all,

I am having a problem where I'm using 2 OFFSET() functions within a
SUMPRODUCT() function. The first offset is working and refers to a range of
4 cells immediately above the cell I'm entering the formula in.

The second OFFSET() is not working. It refers to a range of 4 cells a
calculated number of columns to left of the one I'm entering the formula in.
The calculated column happens to be a Data column in a pivot table (there is
no "Columns" field in the pivot table, so this column is just a Totals
column).

When I step through this formula with the "Evaluate Formula" Formula
Auditing Tool, the second OFFSET() returns {#VALUE!} error. If I isolate
the problematic OFFSET() function and then wrap a SUM() function around it,
it returns the correct value.

Does anyone know what is going on here? Is what I'm trying to do
possible? Is there just some small error in my syntax?

Here are the formulas:

This is the non-working formula:
=IF(AND($AA9<"",$AB9<""),SUMPRODUCT((LoanDetail! $R$2:$R$998=$F9)*(LoanDetail!$E$2:$E$998)*(LoanDet ail!$F$2:$F$998))/$G9,IF(AND($AA9="",$AB9<""),SUMPRODUCT(OFFSET(M9,-$AB9,0,$AB9,1),OFFSET(M9,-$AB9,COLUMN($G9)-COLUMN(),$AB9,1))/$G9,""))

(For those of you who can see it, I've bolded the problematic
OFFSET()function.)

And here is the working formula:
=SUM(OFFSET(M9,-$AB9,COLUMN($G9)-COLUMN(),$AB9,1))

(Notice that it is the exact same OFFSET() isolated then a SUM() wrapped
around it.)




Thanks for any help anyone can provide,

Conan Kelly

OFFSET() not working within a SUMPRODUCT() when refering to a range in a pivot table
 

Yes I did, I ignored it. I didn't want to take the time to explain why it
was incorrect.

Whay I want to know is why the 2nd OFFSET will work by itself with a SUM
wrapped around it, but it won't work inside of the sumproduct.

The second OFFSET is correct how I have in entered, but I don't want to take
the time to explain it.

Thanks,

Conan


"Peo Sjoblom" wrote in message
...
Did you see Roger's answer?

--
Regards,

Peo Sjoblom


"Conan Kelly" wrote in message
...
BUMP

Hello All,

Sorry for bumping, but I'm still looking for a solution to this issue.
Does anyone know what is going on?

Thanks again for everyone's help.

Conan


"Conan Kelly" wrote in message
...
Hello all,

I am having a problem where I'm using 2 OFFSET() functions within a
SUMPRODUCT() function. The first offset is working and refers to a range
of 4 cells immediately above the cell I'm entering the formula in.

The second OFFSET() is not working. It refers to a range of 4 cells a
calculated number of columns to left of the one I'm entering the formula
in. The calculated column happens to be a Data column in a pivot table
(there is no "Columns" field in the pivot table, so this column is just a
Totals column).

When I step through this formula with the "Evaluate Formula" Formula
Auditing Tool, the second OFFSET() returns {#VALUE!} error. If I isolate
the problematic OFFSET() function and then wrap a SUM() function around
it, it returns the correct value.

Does anyone know what is going on here? Is what I'm trying to do
possible? Is there just some small error in my syntax?

Here are the formulas:

This is the non-working formula:

=IF(AND($AA9<"",$AB9<""),SUMPRODUCT((LoanDetail! $R$2:$R$998=$F9)*(LoanDetail!$E$2:$E$998)*(LoanDet ail!$F$2:$F$998))/$G9,IF(AND($AA9="",$AB9<""),SUMPRODUCT(OFFSET(M9,-$AB9,0,$AB9,1),OFFSET(M9,-$AB9,COLUMN($G9)-COLUMN(),$AB9,1))/$G9,""))

(For those of you who can see it, I've bolded the problematic
OFFSET()function.)

And here is the working formula:
=SUM(OFFSET(M9,-$AB9,COLUMN($G9)-COLUMN(),$AB9,1))

(Notice that it is the exact same OFFSET() isolated then a SUM() wrapped
around it.)




Thanks for any help anyone can provide,

Conan Kelly

OFFSET() not working within a SUMPRODUCT() when refering to a range in a pivot table
 

Good Luck

--
Regards

Roger Govier


"Conan Kelly" wrote in message
...
Yes I did, I ignored it. I didn't want to take the time to explain
why it was incorrect.

Whay I want to know is why the 2nd OFFSET will work by itself with a
SUM wrapped around it, but it won't work inside of the sumproduct.

The second OFFSET is correct how I have in entered, but I don't want
to take the time to explain it.

Thanks,

Conan


"Peo Sjoblom" wrote in message
...
Did you see Roger's answer?

--
Regards,

Peo Sjoblom


"Conan Kelly" wrote in message
...
BUMP

Hello All,

Sorry for bumping, but I'm still looking for a solution to this
issue. Does anyone know what is going on?

Thanks again for everyone's help.

Conan


"Conan Kelly" wrote in
message
...
Hello all,

I am having a problem where I'm using 2 OFFSET() functions within a
SUMPRODUCT() function. The first offset is working and refers to a
range of 4 cells immediately above the cell I'm entering the formula
in.

The second OFFSET() is not working. It refers to a range of 4 cells
a calculated number of columns to left of the one I'm entering the
formula in. The calculated column happens to be a Data column in a
pivot table (there is no "Columns" field in the pivot table, so this
column is just a Totals column).

When I step through this formula with the "Evaluate Formula" Formula
Auditing Tool, the second OFFSET() returns {#VALUE!} error. If I
isolate the problematic OFFSET() function and then wrap a SUM()
function around it, it returns the correct value.

Does anyone know what is going on here? Is what I'm trying to do
possible? Is there just some small error in my syntax?

Here are the formulas:

This is the non-working formula:

=IF(AND($AA9<"",$AB9<""),SUMPRODUCT((LoanDetail! $R$2:$R$998=$F9)*(LoanDetail!$E$2:$E$998)*(LoanDet ail!$F$2:$F$998))/$G9,IF(AND($AA9="",$AB9<""),SUMPRODUCT(OFFSET(M9,-$AB9,0,$AB9,1),OFFSET(M9,-$AB9,COLUMN($G9)-COLUMN(),$AB9,1))/$G9,""))

(For those of you who can see it, I've bolded the problematic
OFFSET()function.)

And here is the working formula:
=SUM(OFFSET(M9,-$AB9,COLUMN($G9)-COLUMN(),$AB9,1))

(Notice that it is the exact same OFFSET() isolated then a SUM()
wrapped around it.)




Thanks for any help anyone can provide,

Conan Kelly

OFFSET() not working within a SUMPRODUCT() when refering to a range in a pivot table
 

Hello all,

Using XL 2002

I think I've found the problem.

Watching each calculation as I step through this formula with the "Evaluate Formula" formula auditing tool, I've notice that using COLUMN inside an OFFSET inside a SUMPRODUCT surrounds the results in braces ({}).

So COLUMN($G9)-COLUMN() evaluates like this:

COLUMN($G9)-COLUMN()
{7}-COLUMN()
{7}-{13}
{-6}

For some reason, the braces around the number is causing problems.

I ended up using a helper row where I placed all of the COLUMN() formulas that I needed and the used those cells instead of the COLUMN() functions in the formula.

Does anyone know why the COLUMN function ends up with braces around the results when used in this combination of functions? Or why does the braces trip up the OFFSET function?

It would be nice if I could do this w/o the helper row, so if anyone has any ideas there, I'm all ears.

Thanks for everyone's help,

Conan Kelly



"Conan Kelly" wrote in message ...
Hello all,

I am having a problem where I'm using 2 OFFSET() functions within a SUMPRODUCT() function. The first offset is working and refers to a range of 4 cells immediately above the cell I'm entering the formula in.

The second OFFSET() is not working. It refers to a range of 4 cells a calculated number of columns to left of the one I'm entering the formula in. The calculated column happens to be a Data column in a pivot table (there is no "Columns" field in the pivot table, so this column is just a Totals column).

When I step through this formula with the "Evaluate Formula" Formula Auditing Tool, the second OFFSET() returns {#VALUE!} error. If I isolate the problematic OFFSET() function and then wrap a SUM() function around it, it returns the correct value.

Does anyone know what is going on here? Is what I'm trying to do possible? Is there just some small error in my syntax?

Here are the formulas:

This is the non-working formula:
=IF(AND($AA9<"",$AB9<""),SUMPRODUCT((LoanDetail! $R$2:$R$998=$F9)*(LoanDetail!$E$2:$E$998)*(LoanDet ail!$F$2:$F$998))/$G9,IF(AND($AA9="",$AB9<""),SUMPRODUCT(OFFSET(M9,-$AB9,0,$AB9,1),OFFSET(M9,-$AB9,COLUMN($G9)-COLUMN(),$AB9,1))/$G9,""))

(For those of you who can see it, I've bolded the problematic OFFSET()function.)

And here is the working formula:
=SUM(OFFSET(M9,-$AB9,COLUMN($G9)-COLUMN(),$AB9,1))

(Notice that it is the exact same OFFSET() isolated then a SUM() wrapped around it.)




Thanks for any help anyone can provide,

Conan Kelly