formula to work from data column

masterdineen · #1 08-08-2012, 04:21 AM

Hello

I have the following equation =AVERAGEIF(SUPPORT!K9:K99, ">0")

support!K9:K99 being number of daydiff.

I want to select the daydiff values based on another date column, that only has the current month and year. ie Aug(8) & 2012

Colin Legg · #2 08-08-2012, 04:26 AM

Welcome to the forum. Please would you attach an example worksheet (with no confidential data) and tell us the result you want to help us provide the best solution to your question?

Pecoflyer · #3 08-08-2012, 04:41 AM

Perhaps

Code:

=AVERAGE(IF((SUPPORT!K9:K99, ">0")*(month(j9:j99)=8)*(year(j9:j99)=2012)))

where col J contains your dates

Enter this array formula with Ctrl+Shift+Enter

masterdineen · #4 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 · #5 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")

masterdineen · #6 08-08-2012, 06:24 AM

thank you very much.

excellent stuff

masterdineen · #7 08-08-2012, 06:46 AM

would this work when the Month and year figures are made up from formulars as well.

eg the month column is made up like. =month(c3:c400) c3:c400 is full of dates.

masterdineen · #8 08-08-2012, 06:50 AM

i am using the following equation

=AVERAGEIFS(SUPPORT!K9:K258,SUPPORT!J9:J258,MONTH( TODAY()),SUPPORT!J9:J258,YEAR(TODAY()))

and i get #DIV/0!

Colin Legg · #9 08-08-2012, 07:05 AM

You've referenced column J for both the Month and the Year criteria. What does column J contain?

Code:

=AVERAGEIFS(SUPPORT!K9:K258,SUPPORT!J9:J258,MONTH( TODAY()),SUPPORT!J9:J258,YEAR(TODAY()))

masterdineen · #10 08-08-2012, 07:10 AM

they are dates

masterdineen · #11 08-08-2012, 07:19 AM

is there a way i can get round the #DIV/0!

Colin Legg · #12 08-08-2012, 07:20 AM

Okay, your original example had month and year numbers in separate cells. If you want to reference a single column which contains dates then a different formula is required. Here's one variation:

Code:

=AVERAGEIFS(SUPPORT!K9:K258,
    SUPPORT!J9:J258,">="&EOMONTH(TODAY(),-1)+1,
    SUPPORT!J9:J258,"<"&EOMONTH(TODAY(),0)+1)

masterdineen · #13 08-08-2012, 07:27 AM

still getting #DIV/0!

masterdineen · #14 08-08-2012, 08:13 AM

its ok, ive worked it out.

thank you again for you help.

Regards

Rob

masterdineen · #15 08-08-2012, 02:35 PM

now i am trying the following formula

=COUNTIFS(SUPPORT!H8:H25,"Martin",SUPPORT!M9:M25,Y EAR(TODAY()))

and i get #Value!

but when i create the same formula on the support sheet.

ie =COUNTIFS(H9:H250,"Martin",M9:M250,YEAR(TODAY())) and it works.

Is there limits when referencing other sheets in workbook?

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