Help with Sumproduct Formula with Pivot Table Data
 

Help please, I have a worksheet that is a pivot table and I take an average
of two columns excluding certain types which are in a third column. Column U
is Survey Score column V is Additional Survey Score and I exclude the type of
"PTA" survey scores which are in column B. The formula below works except
when the pivot table inserts (blank) into some of my cells. I've tried
deselecting the data with the (blank) in the cell but then my numbers come
out wrong. I also tried selecting that cell and clicking on the shift key
but it skews the average then.

=SUMPRODUCT((B11:B1143<"PTA")*(U11:V1143=0)*(U11 :V1143<"")*(U11:V1143))/MAX(1,SUMPRODUCT((B11:B1143<"PTA")*(U11:V1143=0) *(U11:V1143<"")))

Is there a way to fix this formula or is there another formula I should be
using to include the cells that are empty, text, or (blank) in it?

Thanks so much for your help,
Rose

Help with Sumproduct Formula with Pivot Table Data
 

It is not clear whether you perform average calculations on your data and the
create PIVOTTABLE or the other way around...

Pl. send the worksheet if possible to , so that I can
take a look

"Rose" wrote:

Help please, I have a worksheet that is a pivot table and I take an average
of two columns excluding certain types which are in a third column. Column U
is Survey Score column V is Additional Survey Score and I exclude the type of
"PTA" survey scores which are in column B. The formula below works except
when the pivot table inserts (blank) into some of my cells. I've tried
deselecting the data with the (blank) in the cell but then my numbers come
out wrong. I also tried selecting that cell and clicking on the shift key
but it skews the average then.

=SUMPRODUCT((B11:B1143<"PTA")*(U11:V1143=0)*(U11 :V1143<"")*(U11:V1143))/MAX(1,SUMPRODUCT((B11:B1143<"PTA")*(U11:V1143=0) *(U11:V1143<"")))

Is there a way to fix this formula or is there another formula I should be
using to include the cells that are empty, text, or (blank) in it?

Thanks so much for your help,
Rose

Help with Sumproduct Formula with Pivot Table Data
 

Thank you for your response I really appreciate your help. I've sent the
spreadsheet to you at the address below.

Best Regards,
Rose

"Sheeloo" wrote:

It is not clear whether you perform average calculations on your data and the
create PIVOTTABLE or the other way around...

Pl. send the worksheet if possible to , so that I can
take a look

"Rose" wrote:

Help please, I have a worksheet that is a pivot table and I take an average
of two columns excluding certain types which are in a third column. Column U
is Survey Score column V is Additional Survey Score and I exclude the type of
"PTA" survey scores which are in column B. The formula below works except
when the pivot table inserts (blank) into some of my cells. I've tried
deselecting the data with the (blank) in the cell but then my numbers come
out wrong. I also tried selecting that cell and clicking on the shift key
but it skews the average then.

=SUMPRODUCT((B11:B1143<"PTA")*(U11:V1143=0)*(U11 :V1143<"")*(U11:V1143))/MAX(1,SUMPRODUCT((B11:B1143<"PTA")*(U11:V1143=0) *(U11:V1143<"")))

Is there a way to fix this formula or is there another formula I should be
using to include the cells that are empty, text, or (blank) in it?

Thanks so much for your help,
Rose

Help with Sumproduct Formula with Pivot Table Data
 

Not received till now. Pl. resend


"Rose" wrote:

Thank you for your response I really appreciate your help. I've sent the
spreadsheet to you at the address below.

Best Regards,
Rose

"Sheeloo" wrote:

It is not clear whether you perform average calculations on your data and the
create PIVOTTABLE or the other way around...

Pl. send the worksheet if possible to , so that I can
take a look

"Rose" wrote:

Help please, I have a worksheet that is a pivot table and I take an average
of two columns excluding certain types which are in a third column. Column U
is Survey Score column V is Additional Survey Score and I exclude the type of
"PTA" survey scores which are in column B. The formula below works except
when the pivot table inserts (blank) into some of my cells. I've tried
deselecting the data with the (blank) in the cell but then my numbers come
out wrong. I also tried selecting that cell and clicking on the shift key
but it skews the average then.

=SUMPRODUCT((B11:B1143<"PTA")*(U11:V1143=0)*(U11 :V1143<"")*(U11:V1143))/MAX(1,SUMPRODUCT((B11:B1143<"PTA")*(U11:V1143=0) *(U11:V1143<"")))

Is there a way to fix this formula or is there another formula I should be
using to include the cells that are empty, text, or (blank) in it?

Thanks so much for your help,
Rose

Help with Sumproduct Formula with Pivot Table Data
 

Thanks, resent to you just now let me know if you receive.
Best Regards,
Rose

"Sheeloo" wrote:

Not received till now. Pl. resend


"Rose" wrote:

Thank you for your response I really appreciate your help. I've sent the
spreadsheet to you at the address below.

Best Regards,
Rose

"Sheeloo" wrote:

It is not clear whether you perform average calculations on your data and the
create PIVOTTABLE or the other way around...

Pl. send the worksheet if possible to , so that I can
take a look

"Rose" wrote:

Help please, I have a worksheet that is a pivot table and I take an average
of two columns excluding certain types which are in a third column. Column U
is Survey Score column V is Additional Survey Score and I exclude the type of
"PTA" survey scores which are in column B. The formula below works except
when the pivot table inserts (blank) into some of my cells. I've tried
deselecting the data with the (blank) in the cell but then my numbers come
out wrong. I also tried selecting that cell and clicking on the shift key
but it skews the average then.

=SUMPRODUCT((B11:B1143<"PTA")*(U11:V1143=0)*(U11 :V1143<"")*(U11:V1143))/MAX(1,SUMPRODUCT((B11:B1143<"PTA")*(U11:V1143=0) *(U11:V1143<"")))

Is there a way to fix this formula or is there another formula I should be
using to include the cells that are empty, text, or (blank) in it?

Thanks so much for your help,
Rose