Averageifs + weeknum

[ShaanB]

Hi all,

I'm trying to build a worksheet for our office manager to report on the average number of hours worked on each completed task, each week. It has two sheets - one has the numbers she's looking for, and one has the raw data from the timesheet program.

The data I get from my timesheet software is just raw
Project . Date completed . Hours worked

I'm trying to get an AVERAGE, by project, of the hours worked LAST WEEK

If I add a column to the raw data that has the Weeknum, then I can easily use AverageIF to get the number. I'm trying to AVOID adding that column however and just putting the formula for the weeknumber directly into the Average if function, so she just has to look at a table of data and not deal with the raw data sheet aside from pasting it in there.



This works, when I put the week number into the Export sheet in column D:
=AVERAGEIFS(Export!C:C,Export!A:A,Numbers!A4,Export!D:D,WEEKNUM(TODAY())-1)

But I'm trying to get to something like this:

=AVERAGEIFS(Export!C:C,Export!A:A,Numbers!A4,Export!C:C,WEEKNUM(TODAY())-1)
i.e., Average IF (RawData!C:C, If Project = e.g., "Project 1", If Date completed was last week (e.g., week number = 41).


Anyone know how I can get this? Thanks!

 

[Aladin Akyurek]

In column D Of Export enter and copy down:

=WEEKNUM(C2)


In Numbers:

=AVERAGEIFS(Export!C:C,Export!A:A,A4,Export!D:D,MAX(D:D))

 

[ShaanB]

Hi Aladin,

Thanks for this, but this is what I've already done and I'm trying to avoid. I don't want to have to do anything in the Export tab.

I want the person using it to just have to copy and paste the raw data into the Export tab, and then switch to the Numbers tab and see her numbers.

 

[Aladin Akyurek]

Hi Aladin,

Thanks for this, but this is what I've already done and I'm trying to avoid. I don't want to have to do anything in the Export tab.

I want the person using it to just have to copy and paste the raw data into the Export tab, and then switch to the Numbers tab and see her numbers.

Just to check:

Column A of Export houses what exactly? And what condition must this column meet?
Column B of Export houses what exactly? And what condition must this column meet?


Column C of Export houses number which must be averaged conditionally, right?

 

[ShaanB]

The data I get from my timesheet software is just raw
Project . Date completed . Hours worked

Hey Aladin

Export:
Column A is "Project", it doesn't need to meet any condition, it's raw data
Column B is "Date Completed", it doesn't need to meet any condition, it's raw data

Column C needs to be Averaged conditioned on columns A & Column B

So for example

EXPORT (raw data):

PROJECT .....DATE COMPLETED......HRS WORKED
A ...............10/1/18......................5
B ...............10/1/18......................7.5
C ...............10/7/18......................3.2
A ...............10/10/18.....................4.2
A ...............10/11/18.....................3.3
C ...............10/11/18.....................6.8

My final table (NUMBERS) should look like this

.........................AVG TOTAL ..............AVG LAST WEEK
A ......................4.17........................3.75
B ......................7.5..........................0
C ......................5.............................6.8

But I want all the calculations happening on the NUMBERS tab (if I can). I don't want to do anything to the raw data on Export
I'm good with the first column on the table, and I understand the logic of an AVERAGEIFS but it doesn't seem to work when I insert the WEEKNUM function in the criteria.

 

[Aladin Akyurek]

In B2 of NUMBERS just enter and copy down:

=AVERAGEIFS(EXPORT!$C$2:$C$7,EXPORT!$A$2:$A$7,A2)

In C2 of NUMBERS control+shift+enter, not just enter, and copy down:

=IFERROR(AVERAGE(IF(EXPORT!$A$2:$A$7=$A2,IF(WEEKNUM(EXPORT!$B$2:$B$7+0)=WEEKNUM(TODAY())-1,EXPORT!$C$2:$C$7))),0)