Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUMPRODUCT - Creating a weighted score in column with subtotals
Col A Col B
Count Average Score Manual Calculation Single 0 - Single 1 5.00 Single 2 4.83 Single 2 4.00 Single 0 - Sub Total 5 4.53 22.67 Single 0 - Single 3 4.22 Sub Total 3 4.22 12.67 Single 3 4.78 Single 0 - Single 1 3.00 Single 1 4.67 Sub Total 5 4.40 22.00 Sub Total 2 3.83 7.67 Grand Total 15 4.37 4.33 Using Excel 2003 This is my current calculation and it returns 4.37 for the Grand Total line. (SUMPRODUCT(A3:A17,A3:A17)/SUM(A3:A17))) Trying to create a weighted average score for a group of items. The Subtotals use the SUMPRODUCT command for the Singles above. Using a SUMPRODUCT command for the Grand Total should work, but when I manually calculate it out I'm getting a different number - 4.33 instead of 4.37. Can you tell me what needs to be fixed? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUMPRODUCT - Creating a weighted score in column with subtotals
=SUMPRODUCT((A3:A17="Single")*B3:B17*C3:C17)/SUMIF(A3:A17,"Single",B3:B17)
HTH, Bernie MS Excel MVP "DP" wrote in message ... Col A Col B Count Average Score Manual Calculation Single 0 - Single 1 5.00 Single 2 4.83 Single 2 4.00 Single 0 - Sub Total 5 4.53 22.67 Single 0 - Single 3 4.22 Sub Total 3 4.22 12.67 Single 3 4.78 Single 0 - Single 1 3.00 Single 1 4.67 Sub Total 5 4.40 22.00 Sub Total 2 3.83 7.67 Grand Total 15 4.37 4.33 Using Excel 2003 This is my current calculation and it returns 4.37 for the Grand Total line. (SUMPRODUCT(A3:A17,A3:A17)/SUM(A3:A17))) Trying to create a weighted average score for a group of items. The Subtotals use the SUMPRODUCT command for the Singles above. Using a SUMPRODUCT command for the Grand Total should work, but when I manually calculate it out I'm getting a different number - 4.33 instead of 4.37. Can you tell me what needs to be fixed? |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUMPRODUCT - Creating a weighted score in column with subtotal
Bernie - I copied this calculation and am getting an error message
(#VALUE!). Are there any Add-In's that need to be active in order for it to work? "Bernie Deitrick" wrote: =SUMPRODUCT((A3:A17="Single")*B3:B17*C3:C17)/SUMIF(A3:A17,"Single",B3:B17) HTH, Bernie MS Excel MVP "DP" wrote in message ... Col A Col B Count Average Score Manual Calculation Single 0 - Single 1 5.00 Single 2 4.83 Single 2 4.00 Single 0 - Sub Total 5 4.53 22.67 Single 0 - Single 3 4.22 Sub Total 3 4.22 12.67 Single 3 4.78 Single 0 - Single 1 3.00 Single 1 4.67 Sub Total 5 4.40 22.00 Sub Total 2 3.83 7.67 Grand Total 15 4.37 4.33 Using Excel 2003 This is my current calculation and it returns 4.37 for the Grand Total line. (SUMPRODUCT(A3:A17,A3:A17)/SUM(A3:A17))) Trying to create a weighted average score for a group of items. The Subtotals use the SUMPRODUCT command for the Singles above. Using a SUMPRODUCT command for the Grand Total should work, but when I manually calculate it out I'm getting a different number - 4.33 instead of 4.37. Can you tell me what needs to be fixed? |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUMPRODUCT - Creating a weighted score in column with subtotal
DP,
No, no add-ins, but you cannot have a dash in place of a zero or a blank. HTH, Bernie MS Excel MVP "DP" wrote in message ... Bernie - I copied this calculation and am getting an error message (#VALUE!). Are there any Add-In's that need to be active in order for it to work? "Bernie Deitrick" wrote: =SUMPRODUCT((A3:A17="Single")*B3:B17*C3:C17)/SUMIF(A3:A17,"Single",B3:B17) HTH, Bernie MS Excel MVP "DP" wrote in message ... Col A Col B Count Average Score Manual Calculation Single 0 - Single 1 5.00 Single 2 4.83 Single 2 4.00 Single 0 - Sub Total 5 4.53 22.67 Single 0 - Single 3 4.22 Sub Total 3 4.22 12.67 Single 3 4.78 Single 0 - Single 1 3.00 Single 1 4.67 Sub Total 5 4.40 22.00 Sub Total 2 3.83 7.67 Grand Total 15 4.37 4.33 Using Excel 2003 This is my current calculation and it returns 4.37 for the Grand Total line. (SUMPRODUCT(A3:A17,A3:A17)/SUM(A3:A17))) Trying to create a weighted average score for a group of items. The Subtotals use the SUMPRODUCT command for the Singles above. Using a SUMPRODUCT command for the Grand Total should work, but when I manually calculate it out I'm getting a different number - 4.33 instead of 4.37. Can you tell me what needs to be fixed? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
If a column contains only 1 lowest score... | Excel Discussion (Misc queries) | |||
Make a sheet to enter score and score is copy to the proper cell.. | Excel Worksheet Functions | |||
Weighted average using SUMIF and/or SUMPRODUCT | Excel Discussion (Misc queries) | |||
2 Dimensional Lookup by column & rows to return score grade | Excel Discussion (Misc queries) | |||
Creating a chart with subtotals | Charts and Charting in Excel |