Menu

problem with cell reference in =sum(offset(cell reference,x,y,z,a)). Want cell ref to be variable.

Search this Thread

Prev

Previous Post

Next Post

Next

#3

Report Post

[email protected]

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.

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
How can I insert a cell reference in a footer (eg for variable foo	GW	Excel Discussion (Misc queries)	1	December 15th 04 10:58 AM
Cell reference - for the sheet name, can I use a variable?	Matt Lawson	Excel Discussion (Misc queries)	4	December 13th 04 02:31 PM
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:27 PM.

About Us

"It's about Microsoft Excel"

Contact Us ExcelBanter forum home Privacy Statement