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
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.
Microsoft MVP - Excel
Where there is a will there are many ways. Pick One!
Please read the Forum Rules
If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below
Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.
Preferred Charities: Lupus Canada and Sick Kids Foundation.
Feel Free to Donate if you want to, for the assistance you received today.
Thank you so much for this NBVC this works a treat!
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?Originally Posted by NBVC
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
Microsoft MVP - Excel
Where there is a will there are many ways. Pick One!
Please read the Forum Rules
If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below
Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.
Preferred Charities: Lupus Canada and Sick Kids Foundation.
Feel Free to Donate if you want to, for the assistance you received today.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks