Thread
:
Help with Sumproduct Formula with Pivot Table Data
View Single Post
#
2
Posted to microsoft.public.excel.worksheet.functions
Sheeloo
external usenet poster
Posts: 793
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
Reply With Quote
Sheeloo
View Public Profile
Find all posts by Sheeloo