| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sumproduct with filters
I am trying to get a weighted average based on item cost, potential cost
increase, and peices sold. I entered a formula using sumproduct which works, except I can't seem to figure out how to make it work for a filtered set of data. Column AJ = design improvement cost increase Column AP = sold qty Column U = item cost =SUMPRODUCT(AJ5:AJ845,AP5:AP845)/SUMPRODUCT(AP5:AP845,U5:U845) I am hoping to be able to filter based on a product category and see the weighted average cost increase for a potential design improvement by category. any help would be greatly appreciated. |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Sumproduct with filters
Andy,
Sumproduct gets a bit more complex on a filtered range, try this =SUMPRODUCT(SUBTOTAL(9,OFFSET(AJ4,ROW(AJ5:AJ845)-ROW(AJ4),,1)),SUBTOTAL(9,OFFSET(AP4,ROW(AP5:AP845)-ROW(AP4),,1)))/SUMPRODUCT(SUBTOTAL(9,OFFSET(AP4,ROW(AP5:AP845)-ROW(AP4),,1)),SUBTOTAL(9,OFFSET(U4,ROW(U5:U845)-ROW(U4),,1))) Mike "Andy" wrote: I am trying to get a weighted average based on item cost, potential cost increase, and peices sold. I entered a formula using sumproduct which works, except I can't seem to figure out how to make it work for a filtered set of data. Column AJ = design improvement cost increase Column AP = sold qty Column U = item cost =SUMPRODUCT(AJ5:AJ845,AP5:AP845)/SUMPRODUCT(AP5:AP845,U5:U845) I am hoping to be able to filter based on a product category and see the weighted average cost increase for a potential design improvement by category. any help would be greatly appreciated. |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Sumproduct with filters
Hi,
With the analysis toolpak loaded =MROUND(A1,50) or without =ROUND(A1/50,0)*50 Mike "Andy" wrote: I am trying to get a weighted average based on item cost, potential cost increase, and peices sold. I entered a formula using sumproduct which works, except I can't seem to figure out how to make it work for a filtered set of data. Column AJ = design improvement cost increase Column AP = sold qty Column U = item cost =SUMPRODUCT(AJ5:AJ845,AP5:AP845)/SUMPRODUCT(AP5:AP845,U5:U845) I am hoping to be able to filter based on a product category and see the weighted average cost increase for a potential design improvement by category. any help would be greatly appreciated. |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Sumproduct with filters
Hmm,
Replied to wrong thread "Mike H" wrote: Hi, With the analysis toolpak loaded =MROUND(A1,50) or without =ROUND(A1/50,0)*50 Mike "Andy" wrote: I am trying to get a weighted average based on item cost, potential cost increase, and peices sold. I entered a formula using sumproduct which works, except I can't seem to figure out how to make it work for a filtered set of data. Column AJ = design improvement cost increase Column AP = sold qty Column U = item cost =SUMPRODUCT(AJ5:AJ845,AP5:AP845)/SUMPRODUCT(AP5:AP845,U5:U845) I am hoping to be able to filter based on a product category and see the weighted average cost increase for a potential design improvement by category. any help would be greatly appreciated. |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Sumproduct with filters
SUMPRODUCT can also filter, try this:
If data by which you want to filter are, say in column Z and the value to be filtered out is in cell AA1 then =(SUMPRODUCT(AJ5:AJ845,AP5:AP845)/SUMPRODUCT(AP5:AP845,U5:U845))*SUMPRODUCT(--(Z5:Z845=$AA$1)) Regards, Stefi €žAndy€ť ezt Ă*rta: I am trying to get a weighted average based on item cost, potential cost increase, and peices sold. I entered a formula using sumproduct which works, except I can't seem to figure out how to make it work for a filtered set of data. Column AJ = design improvement cost increase Column AP = sold qty Column U = item cost =SUMPRODUCT(AJ5:AJ845,AP5:AP845)/SUMPRODUCT(AP5:AP845,U5:U845) I am hoping to be able to filter based on a product category and see the weighted average cost increase for a potential design improvement by category. any help would be greatly appreciated. |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Sumproduct with filters
Try...
=SUMPRODUCT(SUBTOTAL(9,OFFSET(AJ5:AJ845,ROW(AJ5:AJ 845)-ROW(AJ5),,1)),AP5: AP845)/SUMPRODUCT(SUBTOTAL(9,OFFSET(AP5:AP845,ROW(AP5:AP8 45)-ROW(AP5),,1) ),U5:U845) Hope this helps! In article , Andy wrote: I am trying to get a weighted average based on item cost, potential cost increase, and peices sold. I entered a formula using sumproduct which works, except I can't seem to figure out how to make it work for a filtered set of data. Column AJ = design improvement cost increase Column AP = sold qty Column U = item cost =SUMPRODUCT(AJ5:AJ845,AP5:AP845)/SUMPRODUCT(AP5:AP845,U5:U845) I am hoping to be able to filter based on a product category and see the weighted average cost increase for a potential design improvement by category. any help would be greatly appreciated. |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Sumproduct with filters
Thanks for the quick reply Mike. Looks like i may get the report out today
after all. Andy "Mike H" wrote: Andy, Sumproduct gets a bit more complex on a filtered range, try this =SUMPRODUCT(SUBTOTAL(9,OFFSET(AJ4,ROW(AJ5:AJ845)-ROW(AJ4),,1)),SUBTOTAL(9,OFFSET(AP4,ROW(AP5:AP845)-ROW(AP4),,1)))/SUMPRODUCT(SUBTOTAL(9,OFFSET(AP4,ROW(AP5:AP845)-ROW(AP4),,1)),SUBTOTAL(9,OFFSET(U4,ROW(U5:U845)-ROW(U4),,1))) Mike "Andy" wrote: I am trying to get a weighted average based on item cost, potential cost increase, and peices sold. I entered a formula using sumproduct which works, except I can't seem to figure out how to make it work for a filtered set of data. Column AJ = design improvement cost increase Column AP = sold qty Column U = item cost =SUMPRODUCT(AJ5:AJ845,AP5:AP845)/SUMPRODUCT(AP5:AP845,U5:U845) I am hoping to be able to filter based on a product category and see the weighted average cost increase for a potential design improvement by category. any help would be greatly appreciated. |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Sumproduct with filters
Glad I could help
"Andy" wrote: Thanks for the quick reply Mike. Looks like i may get the report out today after all. Andy "Mike H" wrote: Andy, Sumproduct gets a bit more complex on a filtered range, try this =SUMPRODUCT(SUBTOTAL(9,OFFSET(AJ4,ROW(AJ5:AJ845)-ROW(AJ4),,1)),SUBTOTAL(9,OFFSET(AP4,ROW(AP5:AP845)-ROW(AP4),,1)))/SUMPRODUCT(SUBTOTAL(9,OFFSET(AP4,ROW(AP5:AP845)-ROW(AP4),,1)),SUBTOTAL(9,OFFSET(U4,ROW(U5:U845)-ROW(U4),,1))) Mike "Andy" wrote: I am trying to get a weighted average based on item cost, potential cost increase, and peices sold. I entered a formula using sumproduct which works, except I can't seem to figure out how to make it work for a filtered set of data. Column AJ = design improvement cost increase Column AP = sold qty Column U = item cost =SUMPRODUCT(AJ5:AJ845,AP5:AP845)/SUMPRODUCT(AP5:AP845,U5:U845) I am hoping to be able to filter based on a product category and see the weighted average cost increase for a potential design improvement by category. any help would be greatly appreciated. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Sumproduct & Filters | Excel Discussion (Misc queries) | |||
| Pivot Table filters, especially DATE filters | Excel Worksheet Functions | |||
| Conditional SUMPRODUCT or SUMPRODUCT with Filters | Excel Worksheet Functions | |||
| How to copy with filters but not copy the filters in the middle? | Excel Discussion (Misc queries) | |||
| Filters, Subtotal & Intacted Results after the filters' Removal | Excel Discussion (Misc queries) | |||
Linear Mode
