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

[twister212atop]

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.

Using the above formula, I haven't figured out how to nail down the
cell reference. It needs to be something like a vlookup or match where
a cell is identified based on today's date, but I need the cell
reference number.

I tried creating a new column which was =cell("address",data) where
data=cells with the data. I will call this Formula B.

I then created a Formula C in which INDIRECT and VLOOKUP accessed the
data in Formula B, and pointed Formula A (the formula in the Subject
header above) to Formula B. Didn't work.

Any ideas?

Thanks!

 

[Frank Kabel]

Hi
not really sure what you're trying to do:
- post some example data
-. post your expected result
- post your tried formulas

 

[hrlngrv - ExcelForums.com]

(e-mail address removed) wrote..

I have a spreadsheet with daily info over a period of time. Eac ro
represents a day, with the next row representing then next day et

Columns might be daily sales, daily gm, etc

I want to sum and average on a week to date and prior week t dat
basis

..

If the first column of your range (which I'll denote Rng) contain
dates sorted in ascending order with the topmost row containin
column labels, and weeks begin on Sunday, then for the current wee
to date the range for the k_th data column would b

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 b

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, th
4th arguments to OFFSET become more complicated. If only M/Tu/W/Th/
dates were included, then the OFFSET expressions would becom

=OFFSET(Rng,MATCH(TODAY(),OFFSET(Rng,1,0,ROWS(Rng)-1,1)),k-1
-MIN(5,WEEKDAY(TODAY(),2)),1

an

=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 th
dates, then the 4th arguments to OFFSET would be come mor
complicated still, but until you provide more details, I'm going t
stop here