Calculate Average Using Date Range

**LisaG** · 07-02-2008, 10:28 AM

Can anyone possibly help with the following please:

I have a spreadsheet that contains the month in column A. Column B contains a list of numbers, but there are blanks in some of the fields in Column B. Basically I want to calculate the average of the numbers in Column B for each month shown in Column A.

See example data below. Can anyone help with a formula that can calculate average but exclude/ignore any blank fields in the number Field.

Month Number
May 2
June 3
May
June 4
June 7
June 9
June
June 12

Can anyone help?

Any help would be greatly appreciated.

Many Thanks

**NBVC** · 07-02-2008, 10:34 AM

For example...

=Average(IF(A1:A100="June",IF(B1:B100<>"",B1:B100))

adjust ranges to suit

This formula must be confirmed with CTRL+SHIFT+ENTER not just ENTER. You will see { } brackets appear.

**LisaG** · 07-02-2008, 10:52 AM

Thank you so much for this NBVC this works a treat!

**Vbort44** · 07-02-2008, 11:28 AM

Originally Posted by NBVC

For example...

=Average(IF(A1:A100="June",IF(B1:B100<>"",B1:B100))

adjust ranges to suit

This formula must be confirmed with CTRL+SHIFT+ENTER not just ENTER. You will see { } brackets appear.

I am trying to better understand excel formulas in general and I was wondering what the "(B1:B100<>"",B1:B100)" part of this formula means?

**NBVC** · 07-02-2008, 11:39 AM

Originally Posted by Vbort44

I am trying to better understand excel formulas in general and I was wondering what the "(B1:B100<>"",B1:B100)" part of this formula means?

This formula is simply an array version of a regular IF statement...

So if this was a regular "check one cell statement", it might look something like:

=IF(A1="June",IF(B1<>"",B1))

which would say if A1 = "June" and if B1 is not blank, then return what's in B1.

The array version looks at all elements in the array(s) and tests each one and returns an array of results (stored internally)...

so =IF(A1:A100="June",IF(B1:B100<>"",B1:B100)) would create an internal array of all elements from B1:B100 where you have "June" in range A1:A100 and corresponding elements in B1:B100 are not blank.

The AVERAGE() function then averages only those elements returned as TRUE

Here's more on Array formulas: http://www.cpearson.com/excel/ArrayFormulas.aspx

Calculate Average Using Date Range

LinkBack

Thread Tools

Rate This Thread

Display

Calculate Average Using Date Range

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions