Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need average of numbers that occur within range
I need help writing a formula that calculates the average number that
occurs within a range of numbers. For example, I have a column that contains 500 rows. Each row contains a random number between 1 and 100. I want to only extract numbers that fall between the range of 1-10 and than compute the avg number that occurs between 1-10. Thanks, Brian |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need average of numbers that occur within range
Give this a try...
=SUMIF(A1:A500,"<=10")/COUNTIF(A1:A500,"<=10") Rick "Techhead" wrote in message ... I need help writing a formula that calculates the average number that occurs within a range of numbers. For example, I have a column that contains 500 rows. Each row contains a random number between 1 and 100. I want to only extract numbers that fall between the range of 1-10 and than compute the avg number that occurs between 1-10. Thanks, Brian |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need average of numbers that occur within range
Hi,
try this and enter as an array with Ctrl+shift+enter =AVERAGE(IF((A1:A500=1)*(A1:A500<=10),A1:A500,FAL SE)) Mike "Techhead" wrote: I need help writing a formula that calculates the average number that occurs within a range of numbers. For example, I have a column that contains 500 rows. Each row contains a random number between 1 and 100. I want to only extract numbers that fall between the range of 1-10 and than compute the avg number that occurs between 1-10. Thanks, Brian |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need average of numbers that occur within range
On Wed, 9 Apr 2008 09:49:51 -0700 (PDT), Techhead
wrote: I need help writing a formula that calculates the average number that occurs within a range of numbers. For example, I have a column that contains 500 rows. Each row contains a random number between 1 and 100. I want to only extract numbers that fall between the range of 1-10 and than compute the avg number that occurs between 1-10. Thanks, Brian When you write "between the range 1-10" I'm not sure if you want to include 1 and 10 or only want to include numbers that fall "between" those two values. If the latter, then: =(SUMIF(A1:A500,"1")-SUMIF(A1:A500,"=10"))/(COUNTIF(A1:A500,"1")-COUNTIF(A1:A500,"=10")) If the former, then you just need to change the equalities: =(SUMIF(A1:A500,"=1")-SUMIF(A1:A500,"10"))/(COUNTIF(A1:A500,"=1")-COUNTIF(A1:A500,"10")) If you have Excel 2007, then: =AVERAGEIFS(A1:A500,A1:A500,"1",A1:A500,"<10") or, depending on what you mean by between: =AVERAGEIFS(A1:A500,A1:A500,"=1",A1:A500,"<=10") --ron |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need average of numbers that occur within range
On Wed, 09 Apr 2008 13:18:40 -0400, Ron Rosenfeld
wrote: On Wed, 9 Apr 2008 09:49:51 -0700 (PDT), Techhead wrote: I need help writing a formula that calculates the average number that occurs within a range of numbers. For example, I have a column that contains 500 rows. Each row contains a random number between 1 and 100. I want to only extract numbers that fall between the range of 1-10 and than compute the avg number that occurs between 1-10. Thanks, Brian When you write "between the range 1-10" I'm not sure if you want to include 1 and 10 or only want to include numbers that fall "between" those two values. If the latter, then: =(SUMIF(A1:A500,"1")-SUMIF(A1:A500,"=10"))/(COUNTIF(A1:A500,"1")-COUNTIF(A1:A500,"=10")) If the former, then you just need to change the equalities: =(SUMIF(A1:A500,"=1")-SUMIF(A1:A500,"10"))/(COUNTIF(A1:A500,"=1")-COUNTIF(A1:A500,"10")) If you have Excel 2007, then: =AVERAGEIFS(A1:A500,A1:A500,"1",A1:A500,"<10") or, depending on what you mean by between: =AVERAGEIFS(A1:A500,A1:A500,"=1",A1:A500,"<=10 ") --ron Obviously, if you want to include "1", and if the smallest value in your range is "1", then the above formulas can be simplified; but I left them the way they are so as to provide examples you could use for other ranges. --ron |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need average of numbers that occur within range
On Apr 9, 12:18*pm, Ron Rosenfeld wrote:
On Wed, 9 Apr 2008 09:49:51 -0700 (PDT), Techhead wrote: I need help writing a formula that calculates the average number that occurs within a range of numbers. For example, I have a column that contains 500 rows. Each row contains a random number between 1 and 100. I want to only extract numbers that fall between the range of 1-10 and than compute the avg number that occurs between 1-10. Thanks, Brian When you write "between the range 1-10" I'm not sure if you want to include 1 and 10 or only want to include numbers that fall "between" those two values. If the latter, then: =(SUMIF(A1:A500,"1")-SUMIF(A1:A500,"=10"))/(COUNTIF(A1:A500,"1")-COUNTIF*(A1:A500,"=10")) If the former, then you just need to change the equalities: =(SUMIF(A1:A500,"=1")-SUMIF(A1:A500,"10"))/(COUNTIF(A1:A500,"=1")-COUNTI*F(A1:A500,"10")) If you have Excel 2007, then: =AVERAGEIFS(A1:A500,A1:A500,"1",A1:A500,"<10") or, depending on what you mean by between: =AVERAGEIFS(A1:A500,A1:A500,"=1",A1:A500,"<=10") --ron Thank you, this worked out well. I used this syntax. I replaced the range with the entire row series. Thanks for your help! =AVERAGEIFS(G:G,G:G,"=1",G:G,"<=10") |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need average of numbers that occur within range
On Wed, 9 Apr 2008 11:33:08 -0700 (PDT), Techhead
wrote: Thank you, this worked out well. I used this syntax. I replaced the range with the entire row series. Thanks for your help! =AVERAGEIFS(G:G,G:G,"=1",G:G,"<=10") Glad to help. Thanks for the feedback. --ron |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Average a group of numbers within an date range. | Excel Worksheet Functions | |||
Identify numbers that only occur once in a column. | Excel Worksheet Functions | |||
Average of select range of numbers | Excel Worksheet Functions | |||
Average the first nine numbers in a range. | Excel Discussion (Misc queries) | |||
Average first n numbers in a range (there may be less than n numbe | Excel Discussion (Misc queries) |