Solved average only cells in column b based on whats in column a

July 2, 2013 at 22:38:53
Specs: Windows XP
Let's say there are 500 rows and 30 columns.
Column G is the month in which the row pertains to and column J is the total minutes of duration.

I need to search all 500 cells in column G containing a month, say June for instance, and then average the total minutes for all rows containung June.

So if there are 5 rows contianing June, the formula must then calculate the total mins of all June rows, let's say they total 500, then the average would be calculated to 100.


See More: average only cells in column b based on whats in column a

Report •

#1
July 3, 2013 at 00:35:01
What version of Excel are you using?

2007 and later have an AVERAGEIF function which will do exactly what you are asking for. Look it up in the Excel Help files or DAGS.

Earlier than version 2007 and it gets a bit more complicated. You could use SUMIF divided by COUNTIF

You could also use an array formula such as:

=AVERAGE(IF((A2:A10=E2),C2:C10))

The array formula must be entered with Ctrl-Shift-Enter in order to work correctly.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#2
July 3, 2013 at 06:02:58
no that doesn't work as I have tried the averageif but how do you account for the dynamic nature of where the total minutes are located. Check out my post on ozgrid for a better explanation and an excel file.
I am using excel 2010.

Report •

#3
July 3, 2013 at 06:47:07
✔ Best Answer
Format F14 as a Number. Excel is showing a Custom Format of mmmm

It appears that only F14 is formatted as mmmm. F15:F19 are all formatted as General, which should return the actual average.

Based on the values used in the spreadsheet that you posted at the Ozgrid site, the average for January is 369.4.

369.4 is Excel's serial number for January 3, 1901 9:36AM. Excel is picking it up as a Date because of the Custom Format of mmmm and returning January.

BTW...Since you are using Excel 2010, you should use AVERAGEIF (after reformatting F14) since array formulas can be cumbersome to edit and use more resources.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •
Related Solutions


Ask Question