Excel Help

[M17TT C]

Afternoon all,

I've noticed quite a few threads on Excel in the past here and not been able to find a definitive answer through Google so wondered if I could scratch some of your brains for what it most likely, quite a basic formula in Excel.

From Google-ing I believe it is the OFFSET function that I need to use but I can't get the formula correct.

What I am trying to do is simply count how many days employees have worked in the past week and also for the past 13 weeks, or put more simply, count the values in the last 7 cells and also the last 91 cells for each row.

Currently I'm just doing this manually using the SUM function and then copying and pasting the formula to each sheet (13 sheets in total) but what I would like to acheive is to have a sheet set up so that when I add the previous weeks attendance to the sheet, it automatically counts the last 7 and 91 days worked without me having to change any formula or with as little changing as possible.

I hope I've explained this clear enough and I've added a screenshot for reference if needed.



Thanks

 

[Blue Maniac]

Is there any reason COUNTIF won't do what you want?

 

[M17TT C]

I'm not sure tbh as this is all fairly new to me.

I just searched for how to count last 7 cells in a row and most results seemed to indicate the OFFSET function was the one to use so if there is an easier or more efficient way then I'm happy with that.

Basically, I'm an Excel n00b so any help/advice would be great!

 

[Blue Maniac]

Never used OFFSET before. I'll have a fiddle with it in a little while, and if you haven't got an answer by the time I come back I'll let you know what I've discovered.

 

[M17TT C]

Thanks BM. No need to rush though as it's not essentail as I can do it manually for the time being but thought I'd ask here as there seems to be a few excel wizzes around.

For reference, this was the help page I was following but change the rows and collums around as my data goes across the page rather than down it as in their example.

http://excelsemipro.com/2010/10/the-offset-function-last-7-data-points/

 

[Blue Maniac]

I assume you just want to know how many cells have '1' in them over a certain period? If that's the case, use COUNTIF. OFFSET won't work properly because your sheet has non-numerical values in it.

For example, if you want to know how many days worked in the week 26th January to 1st February for row 3, use =COUNTIF(CN3:CT3,1)

Helpfully, if you do this on any row in column CT (doesn't matter which), then copy-paste to the same row in column DA, it'll automatically switch the referred cells seven to the right (ie the next week).

 

[Matt_B]

I assume you just want to know how many cells have '1' in them over a certain period? If that's the case, use COUNTIF. OFFSET won't work properly because your sheet has non-numerical values in it.

For example, if you want to know how many days worked in the week 26th January to 1st February for row 3, use =COUNTIF(CN3:CT3,1)

Helpfully, if you do this on any row in column CT (doesn't matter which), then copy-paste to the same row in column DA, it'll automatically switch the referred cells seven to the right (ie the next week).

I think he wants to be able to do it without copying and pasting, just get the last 7 days at any time. I may be wrong though.

I'll have a Play later.

 

[Matt_B]

Do you have a new sheet for each month or does the east midlands one just carry on indefinitely? Could you upload an anonymous version for us to play with?

 

[Blue Maniac]

I think he wants to be able to do it without copying and pasting, just get the last 7 days at any time. I may be wrong though.

I'll have a Play later.

The appropriate variation on that formula will always work though, he'll just need to extend the cell range for the 91 day count. I'm pretty sure it's the simplest and more effortless way of doing what he needs.

 

[Joe_Fox]

Here you go M17TTC, if that's really your name. Take a look at this.
The formula just needs to have the corresponding end column value, so J would be 10, K 11, L 12, etc.
Hope it helps.
Joe_Fox

 

[M17TT C]

I assume you just want to know how many cells have '1' in them over a certain period? If that's the case, use COUNTIF. OFFSET won't work properly because your sheet has non-numerical values in it.

For example, if you want to know how many days worked in the week 26th January to 1st February for row 3, use =COUNTIF(CN3:CT3,1)

Helpfully, if you do this on any row in column CT (doesn't matter which), then copy-paste to the same row in column DA, it'll automatically switch the referred cells seven to the right (ie the next week).

Yes that is right, I basically need to count how many 1s each employee has in the last 1 week (7 days) and 13 weeks (91 days). I will have a stab at the COUNTIF function later today and if the non-numerical values cause an issue, it is easy to change them to zeros using find and replace.

Thanks very much for the advice though, I'll give it a go later and let you know how I get on.

 

[M17TT C]

I think he wants to be able to do it without copying and pasting, just get the last 7 days at any time. I may be wrong though.

I'll have a Play later.

Pretty much. Each week I have to download the attendance reports from a central server and the reports are split up by sales region which is why I have them on seperate tabs. The end result I would like is to be able to download these new reports, add them to my spread sheet and have a small table below on each tab that tells me the last 7 days and also 91 days total with as little playing about as possible.

Do you have a new sheet for each month or does the east midlands one just carry on indefinitely? Could you upload an anonymous version for us to play with?

It's just something I am making myself from another source and I was just going to keep it going indefinitely or just keep a max of say the past 6 months or something as I only need totals going back as far as 13 weeks.

I'll upload something later if anyone wants to have a play but the advice so far has been a good help and this is something I'm just doing in my spare time at work so no rush

 

[M17TT C]

Here you go M17TTC, if that's really your name. Take a look at this.
The formula just needs to have the corresponding end column value, so J would be 10, K 11, L 12, etc.
Hope it helps.
Joe_Fox

Nice one Joe, that's pretty much what I'm looking for though I wanting it to count the last 7 and 91 cells for each row but hopefully I'll be able to play about with the formula to get it do what I want it later.

Thanks bud though I must admitt I was quite scared about opening the file in the office in case it wasn't just a spread sheet!

 

[M17TT C]

Here is a sheet if anyone wants to play about with but I think Joe's method should work with a bit of changing and I've not tried the COUNTIF function yet either.






EDIT - Just tried the COUNTIF and it works but isn't that just essentially the same as SUM(CN3:CT3) following on from BMs examples given?? And would still mean I need to change the formula each week.

It's good but what I really want it to do is count the last 7 & 91 values in each row as it is going to be constantly updated on a weekly basis and this is why I think I need to use the offset function if it's possible at all??

 

[Joe_Fox]

Glad to have helped.

 

[Blue Maniac]

I don't see why Joe_'s solution is any different to the one I gave, other than being longer to type out. Have I missed something?

Let me do a thing, hold on.

 

[Blue Maniac]

Also, you're right. In this instance it appears COUNTIF and SUM do the same thing. I've just now realised that you actually want one cell to carry the constantly updated figure.

I'll do a thing and come back.

 

[Blue Maniac]

Yeah... I've been poking around for about an hour and genuinely can't find anything that does this. The solutions given above by me and Joe_ are effectively the same thing, and I think that's as good as it'll get.

 

[M17TT C]

No worries BM, thanks very much for your time and help though and it's not a massive issue doing it manually, I just thought it sounded possible from that link I provided by changing AVERAGE to SUM and then altering it to account for rows instead of collums.

Thanks again though as I never really knew about the COUNT functions so still learnt something new

 

[M17TT C]

Persistance is the key!

If anyone is still interested then the correct answer is:

=SUM(OFFSET(A2,0,COUNTA(2:2)-7,1,7))

And if anyone wants to play about with the sheet to see it working, feel free to have a look.

Thanks again though for everyones time, help and input