Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to convert OLAP data (PIVOT Table) to formula | Excel Discussion (Misc queries) | |||
Pivot Table: Custom formula in data area | Excel Discussion (Misc queries) | |||
Get Pivot Table Data Formula | Excel Discussion (Misc queries) | |||
Pivot table, dynamic data formula | Excel Discussion (Misc queries) | |||
Need Formula to display pivot table source data | Excel Worksheet Functions |