SOLVED

Question: Formula to divide total from past 7 days by total from last year's past 7 days?

Copper Contributor

Greetings,

 

I work at a museum and we I have an excel spreadsheet with data entered daily going back a LONG time (1995).  I'm looking to find a formula that does what the subject says, but can't figure out how to set it up.

 

The end goal is to find out if our attendance is ahead / behind for the "Last 7 days".  Eventually, I'll probably also add "Last 30 days", "Last 60 days", etc.

 

Thank you in advance!

 

Chris

8 Replies
best response confirmed by allyreckerman (Microsoft)
Solution

@Flopbot 

 

It is easy to do what you're requesting for quarters or months using Excel's Pivot Table. I did what you see here in just a few minutes taking your data, highlighting it, and then letting Excel's Pivot Table dialog box guide me through a few formatting things.

 

Doing by "last seven days" is a different can of worms, because although seven days is a week, weeks begin and end with different days of the week, so you're not necessarily comparing (to use the cliche) apples with apples. There are of course ways to do it, to define the first seven days of the year as Jan 1 through Jan 7, and so on, no matter what week days are involved. But is that really what you want?

 

Anyway, take a look at this revised spreadsheet and see if it's going in the right direction.

mathetes_0-1626894973190.png

 

@Flopbot 

I agree with @mathetes , the main question here is to define what are correspondent days in previous year.

As variant

image.png

- let assume attendance week starts every Wednesday. Let enter in first cell first Wed in history, into next cell below =prev+7

- sum for the previous week (from previous Wed till day before this Wed) will be

=IFERROR(SUM(INDEX(C:C,MATCH(I26-7,B:B,0)):INDEX(C:C,MATCH(I26,B:B,0)-1)), "no data")

- Prev year week starts from this Wed-364, we may skip this column

- sum for the previous week in previous year is similar

=IFERROR(SUM(INDEX(C:C,MATCH(K26-7,B:B,0)):INDEX(C:C,MATCH(K26,B:B,0)-1)), "no data")

Practically same formulas for previous 4 weeks, in general n weeks.

Few Wednesdays are missed in the history, for such weeks we can't calculate the sum. It's possible to complicate the formulas for such case, much easier to add these few Weds into the history with zero #.

@mathetes 

 

Thank you so much for your help!  That's an interesting idea, using a pivot table and doing quarterly.  I'll play around with it to see what I can find.  I do know that I'm needing an actual formula for this usage... it will get reported out with an EOD (End of Day) report that we send so it has to be something that calculates automatically; not a separate report that I reference.  Google Sheets has a =Filter() function, but I'm not sure if something like that exists in Excel...if it does that might be a part of it.

 

I'll try making the pivot table a separate worksheet that I can reference as needed.

One bit of additional info. I don't want to make this more complex that it already is so I'm just looking at something like this.

Prior week = Today() through Today()-7.
Last year, same week = Today()-365 through Today()-372.

@Flopbot 

When you may use formulas like

=SUMIFS(values, date, "<=" & TODAY()-365, date, ">=" & TODAY() - 372)

and similar for the current year.

@Flopbot 

 

You wrote: Google Sheets has a =Filter() function, but I'm not sure if something like that exists in Excel...if it does that might be a part of it.

 

Excel -- the newest version of it -- DOES have a FILTER function. It's one of several Dynamic Array functions, and could be used. Here's a YouTube video in which a Microsoft software expert explains these: https://www.youtube.com/watch?v=9I9DtFOVPIg

 

Since you know about that function in Google Sheets, I'm going to assume you are also sophisticated enough to make it work. And if you're like me in this regard, you'd just as soon figure it out yourself. Feel free, though, to come back with questions if you need to.

Learn everything about our brand-new Dynamic Arrays and how you can use them to build advanced spreadsheets. Arrays (CSE) have long been present in Excel, but were limited to power users. With Dynamic Arrays we have rebuilt the calc engine, effectively turning all formulas into array formulas ...

@mathetes 

FILTER() is great but I'd avoid to use it on large data ranges and in complex formulas if there are other alternatives. Performance.

@Sergei Baklan 

 

Good to know that it can introduce performance issues. I've not encountered any so far; in fact the benefits have far outweighed any costs, in the applications I've seen. But I'll keep that in mind in the future.

1 best response

Accepted Solutions
best response confirmed by allyreckerman (Microsoft)
Solution

@Flopbot 

 

It is easy to do what you're requesting for quarters or months using Excel's Pivot Table. I did what you see here in just a few minutes taking your data, highlighting it, and then letting Excel's Pivot Table dialog box guide me through a few formatting things.

 

Doing by "last seven days" is a different can of worms, because although seven days is a week, weeks begin and end with different days of the week, so you're not necessarily comparing (to use the cliche) apples with apples. There are of course ways to do it, to define the first seven days of the year as Jan 1 through Jan 7, and so on, no matter what week days are involved. But is that really what you want?

 

Anyway, take a look at this revised spreadsheet and see if it's going in the right direction.

mathetes_0-1626894973190.png

 

View solution in original post