Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Problem with =sum(offset(cell reference,w,x,y,z). I want cell reference to be variable
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! |
#3
|
|||
|
|||
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 |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Maintaining cell reference after sorting | Excel Discussion (Misc queries) | |||
Cell reference - for the sheet name, can I use a variable? | Excel Discussion (Misc queries) | |||
problem with cell reference in =sum(offset(cell reference,x,y,z,a)). Want cell ref to be variable. | Excel Worksheet Functions | |||
GET.CELL | Excel Worksheet Functions | |||
can you use a variable or cell reference in a getpivotdata formul. | Excel Worksheet Functions |