formula to work from data column

masterdineen · #1 08-08-2012, 05:29 AM

I have attached a exmple of data

I would like to average the score column based on the current month and year

Regards

Rob

Colin Legg · #2 08-08-2012, 06:18 AM

Hi Rob,

Since you have more than one condition you have to use AVERAGEIFS instead of AVERAGEIF.

Code:

=AVERAGEIFS(E3:E100,C3:C100,MONTH(TODAY()),D3:D100,YEAR(TODAY()))

If you also want to exclude scores less than or equal to zero then include a third condition:

Code:

=AVERAGEIFS(E3:E100,C3:C100,MONTH(TODAY()),D3:D100,YEAR(TODAY()),E3:E100,">0")

You could simplify (and improve) things further by putting =TODAY() in a spare cell (eg cell H1) and then referecing it from your formula, such as

Code:

=AVERAGEIFS(E3:E100,C3:C100,MONTH(H1),D3:D100,YEAR(H1),E3:E100,">0")

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Change Resource Time vs. Duration when Updating Work column	lylel	Project	3	07-23-2012 06:09 PM
Displaying Work Week instead of date in a column.	whitney07	Project	1	07-05-2012 02:27 PM
definition of work column	ketanco	Project	9	05-31-2012 05:15 PM
Right-Aligning Data to a Column	sunnyside	Excel	3	02-27-2012 01:25 AM
Formula not getting copied to entire column	theacegary	Excel	1	11-29-2010 02:18 PM