Menu

Problem with =sum(offset(cell reference,w,x,y,z). I want cell reference to be variable

Search this Thread

Prev

Previous Post

Next Post

Next

#3

Report Post

hrlngrv - ExcelForums.com

Posts: n/a

Default

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!

Reply With Quote

Reply With Quote

Thread Tools	Search this Thread
Show Printable Version	Search this Thread: Advanced Search
Display Modes
Switch to Linear Mode Switch to Hybrid Mode Threaded Mode

Posting Rules
Smilies are On [IMG] code is On HTML code is Off Trackbacks are On Pingbacks are On Refbacks are On

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Maintaining cell reference after sorting	GRITS	Excel Discussion (Misc queries)	2	April 30th 23 07:42 PM
Cell reference - for the sheet name, can I use a variable?	Matt Lawson	Excel Discussion (Misc queries)	4	December 13th 04 02:31 PM
problem with cell reference in =sum(offset(cell reference,x,y,z,a)). Want cell ref to be variable.	[email protected]	Excel Worksheet Functions	2	December 11th 04 12:05 AM
GET.CELL	Biff	Excel Worksheet Functions	2	November 24th 04 07:16 PM
can you use a variable or cell reference in a getpivotdata formul.	stevec	Excel Worksheet Functions	1	November 8th 04 09:20 PM

All times are GMT +1. The time now is 01:34 PM.

About Us

"It's about Microsoft Excel"

Contact Us ExcelBanter forum home Privacy Statement