How to turn months into weeks?

[procterjames]

Right so ive got a spreadsheet that has the following columns

Average Days Date Recieved Date Complete Number Of Jobs



Basically i start a fresh sheet each month and what im trying to do is get the sheet to detect what has been done in the week I.E For each week i want it to average out the days for that week only .. and also add up the total amount of jobs (once again for that week only)

At the moment i do have a MONTHLY one that is working fine .. but what im trying to do here is sort it out so it does it automatically (I.E.) at the moment im having to average the weekly figures out by myself .. and im wondering if there is a way i can get excell to do this automatically for me??

I dont know if that makes alot of sense but if you have any ideas its greatly appreciated !


Cheers

 

[barry houdini]

How do you define your weeks, i.e. is it Sunday to Saturday?

You could add an additional column with Excel's Weeknum function (which requires Analysis ToolPak to be enabled) then calculate your averages etc. based on the week number.

 

[procterjames]

Its just weekdays Monday - Friday

 

[barry houdini]

Well I think you could use WEEKNUM then but I'm not quite sure what you want to average, it's difficult to tell from your post where one column starts and another ends.

If you want to average data in one column, say D2:D100, for a specific week then add a column, say Z with this formula in Z2

=WEEKNUM(B2)

where B2 contains the date

Copy the formula down all rows and then use this formula for an average of week 44

=AVERAGE(IF(Z2:Z100=44,D2:D100))

confirmed with CTRL+SHIFT+ENTER

 

[procterjames]

just for reference they split like this:

Average Days |Total Number of days | Date Recieved | Date Complete | Number Of Jobs | Cost


I dont know if i made myself very clear but i already have formulas in to work out Monthly averages (and it all works itself automatically) so all i need to do is enter in the date recieved and the date complete and it works out total number of days between them .. then it also averages out for the whole month.

What im wanting to do is similar but i just cant figure out how to get excell to differenciate between the weeks.

I dont know how easy it is to explain exactly what im trying to do but il try:


Basically i want to know how to get Excell to automatically (If possible) .. work out on a weekly basis The average number of days ... And the total number of Jobs (also on a weekly basis).
But like i said my sheet is already working out THe montly figures .. i just need it to be broken down into weekly figures (and obviously having to keep entering formulas at the end of the month will just get annoyign after a while .. but if theres no way around it .. thats what il have to do..
I hope this makes sense.


Thanks

 

[barry houdini]

Assuming the following:

Your existing data is in columns A to F with headers in row 1
Rows are sorted in order of date received
Week number is determined by the date received date

add this formula in G2 copied down

=WEEKNUM(C2)

then in H2

=IF(G2=G1,"",AVERAGE(IF(G$2:G$100=G2,B$2:B$100)))

confirmed with CTRL+SHIFT+ENTER and copied down column

this will give you an average in the first row for each week.

 

[procterjames]

thats great thanks!

 

[procterjames]

Ok, so what you told me there .. is spot on Technically .. so now ive got the cells like this:



WEEK NUMBER | AVERAGE DAYS | AVERAGE WEEK |



.. the only problem seems to be that the formula for the week number is copying itself all the way down 1024 cells .. when really i want it to be copying itself in one cell at the end of the week (E.G.)

like i said before my cells are like this:


Average Days |Total Number of days | Date Recieved | Date Complete | Number Of Jobs | Cost


In the date recieved box and date complete box are 2 serperate dates .. for the week number im using the 'DATE COMPLETE BOX' .. and i can have up to 50 date entries in one day (E.G.) if i do 50 jobs .. i enter each one under the same complete date(If that makes sense) .. and the same for everyday .. so atm ive got about 50 jobs a day.. and im trying to get the weekly number to appear once per week .. if that is possible? .. and also .. im trying to get this into some sort of report.... for example

ive got a layout like

Average Days (WC/02/10/2006)
Average Days (WC/09/10/2006)
Average Days (WC/16/10/2006)
Average Days (WC/23/10/2006)

.. how can i get the weekly data to insert itself into a new sheet (on the same workbook) .. without me having to type anything into it (E.g.) works it out intself??

I hope this makes sense!

Thanks

 

[barry houdini]

OK, I think you can dispense with the Weeknum column

assuming the following

You have a "complete date" for every job in column D and a "total days" figure for every job in column B. Lets call this worksheet "Data"

In another worksheet in A2 you have

"Average Days Week Commencing" and in B2 just a date, e.g.

02/10/2006

then in C2 use this formula

=AVERAGE(IF(ABS(B2-data!D$2:D$2000+3)<=3,data!B$2:B$2000))

confirmed with CTRL+SHIFT+ENTER and copied down