| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
weighted avg. of groups in table
i have a table with group #s in column A and values in column B.
i am using the formula below to calculate the average value (ignoring nulls) of the relevant group on each row. if i add a weighting in C1:C10 how do i calculate the weighted average of each group? =SUM(SUMIF(A1:A10,A1,B1:B10))/SUMPRODUCT((A1:A10=A1)*(B1:B10<"")) thanks in advance for your time. DK |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
weighted avg. of groups in table
=SUMPRODUCT(--(A1:A10=A1),--(B1:B10<""),C1:C10,B1:B10)/SUMPRODUCT(--(A1:A10=A1),--(B1:B10<""),C1:C10)
-- Regards, Tom Ogilvy "dkingston" wrote: i have a table with group #s in column A and values in column B. i am using the formula below to calculate the average value (ignoring nulls) of the relevant group on each row. if i add a weighting in C1:C10 how do i calculate the weighted average of each group? =SUM(SUMIF(A1:A10,A1,B1:B10))/SUMPRODUCT((A1:A10=A1)*(B1:B10<"")) thanks in advance for your time. DK |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
weighted avg. of groups in table
this worked perfectly.
thanks for this and for the earlier formula. you are an excel rock-star! "Tom Ogilvy" wrote: =SUMPRODUCT(--(A1:A10=A1),--(B1:B10<""),C1:C10,B1:B10)/SUMPRODUCT(--(A1:A10=A1),--(B1:B10<""),C1:C10) -- Regards, Tom Ogilvy "dkingston" wrote: i have a table with group #s in column A and values in column B. i am using the formula below to calculate the average value (ignoring nulls) of the relevant group on each row. if i add a weighting in C1:C10 how do i calculate the weighted average of each group? =SUM(SUMIF(A1:A10,A1,B1:B10))/SUMPRODUCT((A1:A10=A1)*(B1:B10<"")) thanks in advance for your time. DK |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| weighted average from table | Excel Worksheet Functions | |||
| weighted averages in a pivot table - how? | Excel Worksheet Functions | |||
| Weighted Average in Pivot table | Excel Worksheet Functions | |||
| weighted average on a pivot table | Excel Discussion (Misc queries) | |||
| weighted average in pivot table | Excel Discussion (Misc queries) | |||
Linear Mode
