Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Weighted Average with multiple conditions
I am trying to create a weighted average based on multiple conditions
in a spreadsheet and can't quite seem to get the formula right. For Example, my data is as follows Client Region Year Sales Hourly Rate Client 1 North 2008 1000000 85 Client 2 South 2008 1500000 90 Client 3 East 2007 300000 110 Client 4 North 2007 500000 105 Client 5 East 2006 3500000 98 Client 6 West 2006 150000 100 Client 7 North 2008 5000000 107 Client 8 South 2007 200000 100 From this data, I would like to calculate the weighted average hourly rate for sales in each region for each year. So in a particular cell I would want the weighted average of hours for customers in the north region for 2008 which would end up being clients 1 and 7. I have previously used sumproduct() for weighted averages, but never with criteria, especially 2 different ones and I can't quite seem to get it to work. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Weighted Average with multiple conditions
This
=SUMPRODUCT(--($B$2:$B$9="North"),--($C$2:$C$9=2008),$D$2:$D$9,$E$2:$E$9)/SUMPRODUCT(--($B$2:$B$9="North"),--($C$2:$C$9=2008),$D$2:$D$9) selects the North region in 2008 and for them it computes (Sales*Hourly rate)/Sales to give 103.33. Is this what is needed? The "North" and 2008 could be replaced by cell references =SUMPRODUCT(--($B$2:$B$9=F1),--($C$2:$C$9=G1),$D$2:$D$9,$E$2:$E$9) with "North" (without quotes) in F1 and 2008 in G1 For more see: Bob Phillips http://www.xldynamic.com/source/xld.SUMPRODUCT.html J.E McGimpsey http://mcgimpsey.com/excel/formulae/doubleneg.html best wishes -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email wrote in message ... I am trying to create a weighted average based on multiple conditions in a spreadsheet and can't quite seem to get the formula right. For Example, my data is as follows Client Region Year Sales Hourly Rate Client 1 North 2008 1000000 85 Client 2 South 2008 1500000 90 Client 3 East 2007 300000 110 Client 4 North 2007 500000 105 Client 5 East 2006 3500000 98 Client 6 West 2006 150000 100 Client 7 North 2008 5000000 107 Client 8 South 2007 200000 100 From this data, I would like to calculate the weighted average hourly rate for sales in each region for each year. So in a particular cell I would want the weighted average of hours for customers in the north region for 2008 which would end up being clients 1 and 7. I have previously used sumproduct() for weighted averages, but never with criteria, especially 2 different ones and I can't quite seem to get it to work. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Weighted Average with multiple conditions
I tried this with blank rows and all was well.
The error N/A suggest something that should be a number is not Are they truly blank rows? By the way do not use full column references such as SUMPRODUCT(--(B:B="North") unless you have Excel 2007 If you want to send me a file I would be happy to check it for you. (remove TRUENORTH. from my email address) -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email wrote in message ... Thanks for your help! Thanks works, but I have one followup question. With my data I don't know how many rows of data I am going to have, but I would like to include it up to at least 5000 rows. The problem is that when I include the blank rows I just get N/A returned. How can I include the extra rows in my formula, but exclude them from the calculation if they are blank? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Average with multiple conditions | Excel Discussion (Misc queries) | |||
Array Average - Multiple Conditions? | Excel Worksheet Functions | |||
Average If with multiple conditions | Excel Discussion (Misc queries) | |||
AVERAGE IF with multiple conditions | Excel Discussion (Misc queries) | |||
Average with multiple conditions | Excel Worksheet Functions |