Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Average of numbers within a range meeting certain criteria
I have a column of test scores - N9:N154.
Scores can be grouped into low (<=79), average (80<=x=120), and high (=121). I want to have one formula for each group that will find the average score for that group. The logic is something like: Low group: find the average of all the scores that are less than or equal to 79 within N9:N154. Middle group: find the average of all the scores between 80 and 120 within N9:N154. High group: find the average of all the scores higher than or equal to 121 within N9:N154. |
#2
|
|||
|
|||
Try the following formula that need to be confirmed with
CONTROL+SHIFT+ENTER, not just ENTER... =AVERAGE(IF(N9:N154<=79,N9:N154)) =AVERAGE(IF((N9:N154=80)*(N9:N154<=120),N9:N154)) =AVERAGE(IF(N9:N154=121,N9:N154)) Hope this helps! In article , "opal23k" wrote: I have a column of test scores - N9:N154. Scores can be grouped into low (<=79), average (80<=x=120), and high (=121). I want to have one formula for each group that will find the average score for that group. The logic is something like: Low group: find the average of all the scores that are less than or equal to 79 within N9:N154. Middle group: find the average of all the scores between 80 and 120 within N9:N154. High group: find the average of all the scores higher than or equal to 121 within N9:N154. |
#3
|
|||
|
|||
Opal - these formulas will give you what you're after, confirm with
ctrl+shift+enter Low Average: =AVERAGE(IF(N9:N154<=79,N9:N154,"")) Middle Average: =AVERAGE(IF(AND(N9:N154=80,N9:N154<=120),N9:N154, "")) High Average: =AVERAGE(IF(N9:N154=121,N9:N154,"")) Hope that helps, |
#4
|
|||
|
|||
Thank you David!
"David Billigmeier" wrote: Opal - these formulas will give you what you're after, confirm with ctrl+shift+enter Low Average: =AVERAGE(IF(N9:N154<=79,N9:N154,"")) Middle Average: =AVERAGE(IF(AND(N9:N154=80,N9:N154<=120),N9:N154, "")) High Average: =AVERAGE(IF(N9:N154=121,N9:N154,"")) Hope that helps, |
#5
|
|||
|
|||
Thank you Domenic!
"Domenic" wrote: Try the following formula that need to be confirmed with CONTROL+SHIFT+ENTER, not just ENTER... =AVERAGE(IF(N9:N154<=79,N9:N154)) =AVERAGE(IF((N9:N154=80)*(N9:N154<=120),N9:N154)) =AVERAGE(IF(N9:N154=121,N9:N154)) Hope this helps! In article , "opal23k" wrote: I have a column of test scores - N9:N154. Scores can be grouped into low (<=79), average (80<=x=120), and high (=121). I want to have one formula for each group that will find the average score for that group. The logic is something like: Low group: find the average of all the scores that are less than or equal to 79 within N9:N154. Middle group: find the average of all the scores between 80 and 120 within N9:N154. High group: find the average of all the scores higher than or equal to 121 within N9:N154. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Count and Sum Total occurrances of two specific numbers | Excel Worksheet Functions | |||
Can I sum or average a range with more than 1 condition? | Excel Discussion (Misc queries) | |||
Average Non-Contiguous numbers | Excel Worksheet Functions | |||
Can you average data in 1 column based on a range of values in another? | Excel Worksheet Functions | |||
How do i get an average that ignores blanks in the range of cells. | Excel Worksheet Functions |