|
|
|
|
wrote...
I have a spreadsheet with daily info over a period of time. Each
row
represents a day, with the next row representing then next day;
etc
etc.
Columns might be daily sales, daily gm, etc.
I want to sum and average on a week to date and prior week to
date
basis.
...
If the first column of your range (which I'll denote Rng) contains
dates sorted in ascending order with the topmost row containing
column labels, and weeks begin on Sunday, then for the current week
to date the range for the k_th data column would be
OFFSET(Rng,MATCH(TODAY(),OFFSET(Rng,1,0,ROWS(Rng)-1,1)),k-1,
-WEEKDAY(TODAY()),1)
and for the previous week to date it'd be
OFFSET(Rng,MATCH(TODAY()-7,OFFSET(Rng,1,0,ROWS(Rng)-1,1)),k-1,
-WEEKDAY(TODAY()),1)
These assume there are no gaps in the dates. If there were gaps, the
4th arguments to OFFSET become more complicated. If only M/Tu/W/Th/F
dates were included, then the OFFSET expressions would become
=OFFSET(Rng,MATCH(TODAY(),OFFSET(Rng,1,0,ROWS(Rng)-1,1)),k-1,
-MIN(5,WEEKDAY(TODAY(),2)),1)
and
=OFFSET(Rng,MATCH(TODAY()-7,OFFSET(Rng,1,0,ROWS(Rng)-1,1)),k-1,
-MIN(5,WEEKDAY(TODAY(),2)),1)
respectively. If there were no simple pattern to the gaps in the
dates, then the 4th arguments to OFFSET would be come more
complicated still, but until you provide more details, I'm going to
stop here.
---------
www.coffeecozy.com
Use your Bodum and give up cold coffee for good!
|