AVERAGEIF Formula

May 16, 2006 at 11:56 am 1 comment

Excel has a formula to count sum with any condition, SUMIF. But there isn’t built-in formula to count average with any condition, e.g. said AVERAGEIF. Then u yourself must write function with macro. But any other solution without macro?The anwser is absolutly…. yes! :Horrayy:
U can use the advantage of array function, which is by pressing Ctrl+Shift+Enter after u write the formula. Then u can see that the formula shows in the bracket {..}.

The formula is
=AVERAGE(IF(array of condition = condition, array of average))
and followed by pressing Ctrl+Shift+Enter

For example. This table below shows the rate of subject by student. Our mission is to find the average of each subject.

U just write the formula in D16, D17, and D18, respectively:
=AVERAGE(IF($C$3:$C$13=C16,$D$3:$D$13))
=AVERAGE(IF($C$3:$C$13=C17,$D$3:$D$13))
=AVERAGE(IF($C$3:$C$13=C18,$D$3:$D$13))
and don’t forget to press Ctrl+Shift+Enter.

Entry filed under: Formula, Sedeng. Tags: .

2D lookup Generating Normal Distribution Data

1 Comment Add your own

  • 1. DEDE IRPANDI  |  December 28, 2010 at 5:19 pm

    if u use MSOffice2010, the formula like this

    =AVERAGEIF($C$3:$C$13,C16,$D$3:$D$13)

    Reply

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Trackback this post  |  Subscribe to the comments via RSS Feed


We Moved!

Kami telah pindah ke www.excelquotient.com

Tweet XQ

Kapsul XQ Terkini

Archives

Annex


Langganan
RSS Kapsul XQ
RSS Komentar XQ

# Blog Walkers
web tracker

Ranks
My Popularity (by popuri.us)

Lisensi
Creative Commons License

%d bloggers like this: