If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Average if using multiple criteria
Hello all. I could use a little help. I've tried working
with average and an array formula and can work with one criteria but I am having trouble adding a second. My spreadsheet is set up as follows column a: column b: column c: customer a 1/15/04 25 customer b 1/26/04 41 customer a 1/30/04 32 customer c 2/01/04 51 customer a 2/10/04 14 How do I find the average of column c where column a equals "customer a" and column b is between 1/01/04 and 1/31/04? According to the above example it would average 25 and 32 but not the 14 since it is past 1/31/04. Thank you in advance. |
#2
|
|||
|
|||
Hi Jeff
there's probably other ways but here's two: =SUMPRODUCT((A2:A6=C8)*(B2:B6=C9)*(B2:B6=C10)*C2 :C6)/SUMPRODUCT((A2:A6=C8) *(B2:B6=C9)*(B2:B6=C10)) and =SUMPRODUCT((A2:A6=C8)*(TEXT(B2:B6,"mmm")="Jan")*C 2:C6)/SUMPRODUCT((A2:A6=C8 )*(TEXT(B2:B6,"mmm")="Jan")) where C8 contains "customer a" and C9 contains the start date of your range and C10 contains the end date of yor range Cheers JulieD "Jeff" wrote in message ... Hello all. I could use a little help. I've tried working with average and an array formula and can work with one criteria but I am having trouble adding a second. My spreadsheet is set up as follows column a: column b: column c: customer a 1/15/04 25 customer b 1/26/04 41 customer a 1/30/04 32 customer c 2/01/04 51 customer a 2/10/04 14 How do I find the average of column c where column a equals "customer a" and column b is between 1/01/04 and 1/31/04? According to the above example it would average 25 and 32 but not the 14 since it is past 1/31/04. Thank you in advance. |
#3
|
|||
|
|||
Hi Frank
still can't get my head around array formulas , whereas the sumproduct makes perfect sense to me vbg cheers JulieD "Frank Kabel" wrote in message ... Hi Julie as alternative using the array formula: =AVERAGE(IF(A2:A6=C8)*(B2:B6=C9)*(B2:B6=C10),C2: C6)) -- Regards Frank Kabel Frankfurt, Germany JulieD wrote: Hi Jeff there's probably other ways but here's two: =SUMPRODUCT((A2:A6=C8)*(B2:B6=C9)*(B2:B6=C10)*C2 :C6)/SUMPRODUCT((A2:A 6=C8) *(B2:B6=C9)*(B2:B6=C10)) and =SUMPRODUCT((A2:A6=C8)*(TEXT(B2:B6,"mmm")="Jan")*C 2:C6)/SUMPRODUCT((A2: A6=C8 )*(TEXT(B2:B6,"mmm")="Jan")) where C8 contains "customer a" and C9 contains the start date of your range and C10 contains the end date of yor range Cheers JulieD "Jeff" wrote in message ... Hello all. I could use a little help. I've tried working with average and an array formula and can work with one criteria but I am having trouble adding a second. My spreadsheet is set up as follows column a: column b: column c: customer a 1/15/04 25 customer b 1/26/04 41 customer a 1/30/04 32 customer c 2/01/04 51 customer a 2/10/04 14 How do I find the average of column c where column a equals "customer a" and column b is between 1/01/04 and 1/31/04? According to the above example it would average 25 and 32 but not the 14 since it is past 1/31/04. Thank you in advance. |
#4
|
|||
|
|||
Hi Julie
as alternative using the array formula: =AVERAGE(IF(A2:A6=C8)*(B2:B6=C9)*(B2:B6=C10),C2: C6)) -- Regards Frank Kabel Frankfurt, Germany JulieD wrote: Hi Jeff there's probably other ways but here's two: =SUMPRODUCT((A2:A6=C8)*(B2:B6=C9)*(B2:B6=C10)*C2 :C6)/SUMPRODUCT((A2:A 6=C8) *(B2:B6=C9)*(B2:B6=C10)) and =SUMPRODUCT((A2:A6=C8)*(TEXT(B2:B6,"mmm")="Jan")*C 2:C6)/SUMPRODUCT((A2: A6=C8 )*(TEXT(B2:B6,"mmm")="Jan")) where C8 contains "customer a" and C9 contains the start date of your range and C10 contains the end date of yor range Cheers JulieD "Jeff" wrote in message ... Hello all. I could use a little help. I've tried working with average and an array formula and can work with one criteria but I am having trouble adding a second. My spreadsheet is set up as follows column a: column b: column c: customer a 1/15/04 25 customer b 1/26/04 41 customer a 1/30/04 32 customer c 2/01/04 51 customer a 2/10/04 14 How do I find the average of column c where column a equals "customer a" and column b is between 1/01/04 and 1/31/04? According to the above example it would average 25 and 32 but not the 14 since it is past 1/31/04. Thank you in advance. |
#5
|
|||
|
|||
Hi Julie
as long as it works who cares :-) Maybe the SUMPRODUCT variant is even faster... -- Regards Frank Kabel Frankfurt, Germany JulieD wrote: Hi Frank still can't get my head around array formulas , whereas the sumproduct makes perfect sense to me vbg cheers JulieD "Frank Kabel" wrote in message ... Hi Julie as alternative using the array formula: =AVERAGE(IF(A2:A6=C8)*(B2:B6=C9)*(B2:B6=C10),C2: C6)) -- Regards Frank Kabel Frankfurt, Germany JulieD wrote: Hi Jeff there's probably other ways but here's two: =SUMPRODUCT((A2:A6=C8)*(B2:B6=C9)*(B2:B6=C10)*C2 :C6)/SUMPRODUCT((A2:A 6=C8) *(B2:B6=C9)*(B2:B6=C10)) and =SUMPRODUCT((A2:A6=C8)*(TEXT(B2:B6,"mmm")="Jan")*C 2:C6)/SUMPRODUCT((A2: A6=C8 )*(TEXT(B2:B6,"mmm")="Jan")) where C8 contains "customer a" and C9 contains the start date of your range and C10 contains the end date of yor range Cheers JulieD "Jeff" wrote in message ... Hello all. I could use a little help. I've tried working with average and an array formula and can work with one criteria but I am having trouble adding a second. My spreadsheet is set up as follows column a: column b: column c: customer a 1/15/04 25 customer b 1/26/04 41 customer a 1/30/04 32 customer c 2/01/04 51 customer a 2/10/04 14 How do I find the average of column c where column a equals "customer a" and column b is between 1/01/04 and 1/31/04? According to the above example it would average 25 and 32 but not the 14 since it is past 1/31/04. Thank you in advance. |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Multiple criteria for countif() | theillknight | General Discussion | 2 | August 10th, 2004 11:38 PM |
Countif functions with multiple criteria | Jason Morin | Worksheet Functions | 3 | April 5th, 2004 08:46 PM |
Countif functions with multiple criteria | t | Worksheet Functions | 0 | April 5th, 2004 07:11 PM |
Sumif over multiple pages with two criteria | Worksheet Functions | 0 | March 17th, 2004 07:27 PM | |
DCOUNT & DSUM w/ multiple criteria | Dianne | Worksheet Functions | 1 | September 30th, 2003 04:35 PM |