Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
AverageIf
Is there a way to average a range of data?
For example: I have a column with values 1,2,3,4,5...10 and I want to average those values greater than 2 but less than 8. I *think* the AverageIfs will work but it is my understanding that this function is only valid in the most current versions of excel. If so, I would like to find a function that is more compatible. Thank you. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
AverageIf
Maybe
=AVERAGE(IF($A$1:$A$152,IF(A1:A15<8,A1:A15))) Array entered with Ctrl+Shift+Enter Mike "TG" wrote: Is there a way to average a range of data? For example: I have a column with values 1,2,3,4,5...10 and I want to average those values greater than 2 but less than 8. I *think* the AverageIfs will work but it is my understanding that this function is only valid in the most current versions of excel. If so, I would like to find a function that is more compatible. Thank you. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
AverageIf
This is an array formula that must be entered using ctrl+shift+enter
=AVERAGE(IF((H2:H222)*(H2:H22<6),H2:H22)) -- Don Guillett Microsoft MVP Excel SalesAid Software "TG" wrote in message ... Is there a way to average a range of data? For example: I have a column with values 1,2,3,4,5...10 and I want to average those values greater than 2 but less than 8. I *think* the AverageIfs will work but it is my understanding that this function is only valid in the most current versions of excel. If so, I would like to find a function that is more compatible. Thank you. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
AverageIf
I'll confess my bias against array formulas, and therefore suggest an
alternative, which goes back to the definition of average: total / count. So I'd calculate the conditional average as sumif/countif: (sumif(range,"2",sum_range)-sumif(range,"=8",sum_range)) / (countif(range,"2")-countif(range("=8")) "TG" wrote: Is there a way to average a range of data? For example: I have a column with values 1,2,3,4,5...10 and I want to average those values greater than 2 but less than 8. I *think* the AverageIfs will work but it is my understanding that this function is only valid in the most current versions of excel. If so, I would like to find a function that is more compatible. Thank you. |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
AverageIf
Here is yet a different way
=SUMPRODUCT(--(F6:F152),--(F6:F15<8),F6:F15)/SUMPRODUCT(--(F6:F152),--(F6:F15<8)) If you want to include either 2 or 8 change the inequality to = or <= respectively. -- Wag more, bark less "TG" wrote: Is there a way to average a range of data? For example: I have a column with values 1,2,3,4,5...10 and I want to average those values greater than 2 but less than 8. I *think* the AverageIfs will work but it is my understanding that this function is only valid in the most current versions of excel. If so, I would like to find a function that is more compatible. Thank you. |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
AverageIf
You guys all rock. All these methods work and are exactly what I was after.
Thank you! "Brad" wrote: Here is yet a different way =SUMPRODUCT(--(F6:F152),--(F6:F15<8),F6:F15)/SUMPRODUCT(--(F6:F152),--(F6:F15<8)) If you want to include either 2 or 8 change the inequality to = or <= respectively. -- Wag more, bark less "TG" wrote: Is there a way to average a range of data? For example: I have a column with values 1,2,3,4,5...10 and I want to average those values greater than 2 but less than 8. I *think* the AverageIfs will work but it is my understanding that this function is only valid in the most current versions of excel. If so, I would like to find a function that is more compatible. Thank you. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
AVERAGEIF and group?? | Excel Worksheet Functions | |||
Averageif help | Excel Discussion (Misc queries) | |||
AverageIF | Excel Discussion (Misc queries) | |||
averageif | Excel Worksheet Functions | |||
averageif | Excel Discussion (Misc queries) |