average formula :: MrExcel Message Board
 Search   Memberlist   Usergroups   Favorites   Statistics   Register

 average formula

calliroi
Board Regular

Joined: 10 Jul 2003
Posts: 10

Flag:

Status: Offline

average formula

I need help with the average formula:

ex: i have a column with 6,000 records.....i want to average the records in the same column that have lets say \$300-\$1000...is that possible

Fri Sep 19, 2003 5:33 pm

earlyd
Board Master

Joined: 11 Dec 2002
Posts: 955

Flag:

Status: Offline

Re: average formula

=(SUMIF(I6:I12,">300") - SUMIF(I6:I12,">1000"))/(COUNTIF(I6:I12,">300") - COUNTIF(I6:I12,">1000"))

Fri Sep 19, 2003 5:38 pm

earlyd
Board Master

Joined: 11 Dec 2002
Posts: 955

Flag:

Status: Offline

Re: average formula

Above solution's range obviously has to be changed to allow for your 6000 entries.

Fri Sep 19, 2003 5:39 pm

Iridium
Board Master

Joined: 16 Jul 2002
Posts: 2581
Location: Walsall, England
Flag:

Status: Offline

Re: average formula

Without knowing more about how your s/s is set up you could try

http://www.mrexcel.com/tip011.shtml
http://www.cpearson.com/excel/lists.htm

HTH
_________________

Fri Sep 19, 2003 5:41 pm

.

Joined: 15 Feb 2002
Posts: 14083
Location: The Hague
Flag:

Status: Offline

Re: average formula

quote:
Originally posted by calliroi:
I need help with the average formula:

ex: i have a column with 6,000 records.....i want to average the records in the same column that have lets say \$300-\$1000...is that possible

=AVERAGE(IF((\$A\$2:\$A\$6001>=300)*(\$A\$2:\$A\$6001<=1000),\$A\$2:\$A\$6001))

which must be confirmed with control+shift+enter, not just with enter.

Fri Sep 19, 2003 5:43 pm
 Display posts from previous: All Posts1 Day7 Days2 Weeks1 Month3 Months6 Months1 Year Oldest FirstNewest First

Forum Jump:
 Jump to: Select a forum MrExcel Forums----------------Excel QuestionsInternational forumMicrosoft AccessTechnical issues and Future development Holy Macro! Books----------------Holy Macro! Products The Lounge----------------Max Cells Lounge Announcements----------------About This BoardHall of Fame WinnersTest Here

Page 1 of 1

Forum Rules:
 You cannot post new topics in this forumYou cannot reply to topics in this forumYou cannot edit your posts in this forumYou cannot delete your posts in this forumYou cannot vote in polls in this forum