Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
AVERAGE & SUMPRODUCT
I'm trying to calculate an average of a series of numbers, which
numbers are within a range. Not all members of the range should be included in the average calculation, however. I have the following: =SUMPRODUCT(--(K2:K13="WMT"),(M2:M13))/COUNTIF(K2:K13,"WMT") which obviously works but it would seem to me there is a more direct way to do this using the AVERAGE function. (I'm using SUMPRODUCT here instead of SUMIF because I may need to add conditions in the future.) Thoughts? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
AVERAGE & SUMPRODUCT
try
=average(if(K2:K13="WMT",M2:M13)) entered as array control-shift-enter you could also use =average(if(and(K2:K13="WMT",criteria 2),M2:M13)) entered as array control-shift-enter you could use Or instead of And if needed. "Dave F" wrote: I'm trying to calculate an average of a series of numbers, which numbers are within a range. Not all members of the range should be included in the average calculation, however. I have the following: =SUMPRODUCT(--(K2:K13="WMT"),(M2:M13))/COUNTIF(K2:K13,"WMT") which obviously works but it would seem to me there is a more direct way to do this using the AVERAGE function. (I'm using SUMPRODUCT here instead of SUMIF because I may need to add conditions in the future.) Thoughts? |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
AVERAGE & SUMPRODUCT
try:
=AVERAGE(IF(K2:K13="WMT",M2:M13)) Enter with Ctrl+Shift+Enter. If entered correctly, you will get {} brackets round the formula. HTH "Dave F" wrote: I'm trying to calculate an average of a series of numbers, which numbers are within a range. Not all members of the range should be included in the average calculation, however. I have the following: =SUMPRODUCT(--(K2:K13="WMT"),(M2:M13))/COUNTIF(K2:K13,"WMT") which obviously works but it would seem to me there is a more direct way to do this using the AVERAGE function. (I'm using SUMPRODUCT here instead of SUMIF because I may need to add conditions in the future.) Thoughts? |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
AVERAGE & SUMPRODUCT
You could use =averageif() in xl2007.
Dave F wrote: I'm trying to calculate an average of a series of numbers, which numbers are within a range. Not all members of the range should be included in the average calculation, however. I have the following: =SUMPRODUCT(--(K2:K13="WMT"),(M2:M13))/COUNTIF(K2:K13,"WMT") which obviously works but it would seem to me there is a more direct way to do this using the AVERAGE function. (I'm using SUMPRODUCT here instead of SUMIF because I may need to add conditions in the future.) Thoughts? -- Dave Peterson |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
AVERAGE & SUMPRODUCT
Excellent suggestion. How do I get my employer to install Office 07
on my pc? The consensus here seems to be that an array formula is the way to go on pre-XL07 installations. Thanks to all for the feedback. On Jul 31, 3:15 pm, Dave Peterson wrote: You could use =averageif() in xl2007. Dave F wrote: I'm trying to calculate an average of a series of numbers, which numbers are within a range. Not all members of the range should be included in the average calculation, however. I have the following: =SUMPRODUCT(--(K2:K13="WMT"),(M2:M13))/COUNTIF(K2:K13,"WMT") which obviously works but it would seem to me there is a more direct way to do this using the AVERAGE function. (I'm using SUMPRODUCT here instead of SUMIF because I may need to add conditions in the future.) Thoughts? -- Dave Peterson- Hide quoted text - - Show quoted text - |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
AVERAGE & SUMPRODUCT
Ask them?
I think I'd use =sumif()/countif() until you needed more criteria. Dave F wrote: Excellent suggestion. How do I get my employer to install Office 07 on my pc? The consensus here seems to be that an array formula is the way to go on pre-XL07 installations. Thanks to all for the feedback. On Jul 31, 3:15 pm, Dave Peterson wrote: You could use =averageif() in xl2007. Dave F wrote: I'm trying to calculate an average of a series of numbers, which numbers are within a range. Not all members of the range should be included in the average calculation, however. I have the following: =SUMPRODUCT(--(K2:K13="WMT"),(M2:M13))/COUNTIF(K2:K13,"WMT") which obviously works but it would seem to me there is a more direct way to do this using the AVERAGE function. (I'm using SUMPRODUCT here instead of SUMIF because I may need to add conditions in the future.) Thoughts? -- Dave Peterson- Hide quoted text - - Show quoted text - -- Dave Peterson |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
AVERAGE & SUMPRODUCT
The average(if()) array construction works well for me in the interim.
No luck on getting XL 07 installed till early next year at my co. Not an issue though. Thanks for the help. Dave On Jul 31, 5:17 pm, Dave Peterson wrote: Ask them? I think I'd use =sumif()/countif() until you needed more criteria. Dave F wrote: Excellent suggestion. How do I get my employer to install Office 07 on my pc? The consensus here seems to be that an array formula is the way to go on pre-XL07 installations. Thanks to all for the feedback. On Jul 31, 3:15 pm, Dave Peterson wrote: You could use =averageif() in xl2007. Dave F wrote: I'm trying to calculate an average of a series of numbers, which numbers are within a range. Not all members of the range should be included in the average calculation, however. I have the following: =SUMPRODUCT(--(K2:K13="WMT"),(M2:M13))/COUNTIF(K2:K13,"WMT") which obviously works but it would seem to me there is a more direct way to do this using the AVERAGE function. (I'm using SUMPRODUCT here instead of SUMIF because I may need to add conditions in the future.) Thoughts? -- Dave Peterson- Hide quoted text - - Show quoted text - -- Dave Peterson- Hide quoted text - - Show quoted text - |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Average nesting within Sumproduct? | Excel Worksheet Functions | |||
sumproduct and average functions | Excel Worksheet Functions | |||
Using Sumproduct to Find Average | Excel Discussion (Misc queries) | |||
Sumproduct Average | Excel Discussion (Misc queries) | |||
sumproduct & average | Excel Worksheet Functions |