There's got to be a way...
April 23, 2013 7:42 AM Subscribe
Excel functions: OFFSET? LOOKUP? ADDRESS? Unconfuse me, please.
I've got a sticky Excel problem that I can't seem to get unstuck.
I've generated a table with 2 columns. Column 1 contains sequential dates. Column 2 contains numbers that are correlated with those dates. I'm trying to write a formula that finds a particular date in the first column, and returns the sum of an adjacent range of values from the second column. I've been trying to use the OFFSET, like this:
=SUM(OFFSET((Reference,[calculated number of rows],[fixed number of columns],[calculated height],[fixed width]))
That works if I manually enter the 'Reference' cell, but I can't figure out how to look up a reference cell. I've tried using ADDRESS, which does fine by itself for returning a cell reference as text, e.g. "A96", but Excel doesn't want to use that result as a cell reference in another formula.
Ideas?
I've got a sticky Excel problem that I can't seem to get unstuck.
I've generated a table with 2 columns. Column 1 contains sequential dates. Column 2 contains numbers that are correlated with those dates. I'm trying to write a formula that finds a particular date in the first column, and returns the sum of an adjacent range of values from the second column. I've been trying to use the OFFSET, like this:
=SUM(OFFSET((Reference,[calculated number of rows],[fixed number of columns],[calculated height],[fixed width]))
That works if I manually enter the 'Reference' cell, but I can't figure out how to look up a reference cell. I've tried using ADDRESS, which does fine by itself for returning a cell reference as text, e.g. "A96", but Excel doesn't want to use that result as a cell reference in another formula.
Ideas?
Response by poster: As for the adjacent range of cells, is this descete? Meaning will the results for 04/01/2013 always be the same? If so, just calculate the adjacent cells and make the second column the sum of that calculation.
The column 2 value adjacent to 4/1/2013 will always be the same, but the size of the range of column 2 cells I want to sum is not constant.
posted by jon1270 at 7:57 AM on April 23, 2013
The column 2 value adjacent to 4/1/2013 will always be the same, but the size of the range of column 2 cells I want to sum is not constant.
posted by jon1270 at 7:57 AM on April 23, 2013
Best answer: I think you want to use INDIRECT() around the ADDRESS() to change the text returned from ADDRESS() into a cell reference that another formula can use. INDIRECT "Returns the reference specified by a text string. References are immediately evaluated to display their contents. Use INDIRECT when you want to change the reference to a cell within a formula without changing the formula itself."
posted by Mr.Know-it-some at 7:57 AM on April 23, 2013
posted by Mr.Know-it-some at 7:57 AM on April 23, 2013
Response by poster: I think you want to use INDIRECT() around the ADDRESS()...
I thought I had already tried that unsuccessfully, but I've been trying it again since I posted the question and I think it may work. My formula is kinda big and ugly, so it's going to take me a little while to be sure.
posted by jon1270 at 8:00 AM on April 23, 2013
I thought I had already tried that unsuccessfully, but I've been trying it again since I posted the question and I think it may work. My formula is kinda big and ugly, so it's going to take me a little while to be sure.
posted by jon1270 at 8:00 AM on April 23, 2013
What you're looking for here is SUMIFS. You want to type SUMIFS(column2, column1, specificdate).
I say SUMIFS, because that way you put the column you're summing first, and you can add extra criteria.
posted by ambrosen at 8:59 AM on April 23, 2013
I say SUMIFS, because that way you put the column you're summing first, and you can add extra criteria.
posted by ambrosen at 8:59 AM on April 23, 2013
Yes, OFFSET might work here but it is rarely the best option, because it is very opaque (hard to actually tell what it is doing) and therefore difficult to troubleshoot and is a "volatile" function which means that excel has to recalculate its value after every user action.
If you care about the above, I can't tell exactly what you are trying to do but I think it could be achieved by summing over a range defined by an INDEX and MATCH.
posted by milestogo at 9:44 AM on April 23, 2013
If you care about the above, I can't tell exactly what you are trying to do but I think it could be achieved by summing over a range defined by an INDEX and MATCH.
posted by milestogo at 9:44 AM on April 23, 2013
This thread is closed to new comments.
Highlight the range of all the cells. Then in the box, name the range. List is a perfectly cromulent name.
The formula looks like this
=VLOOKUP(A1,List,2,false)
So A1 is the cell with the data you want to match.
List is the name of the range of cells you want to search
2 is the second column
False-will only return exact matches.
As for the adjacent range of cells, is this descete? Meaning will the results for 04/01/2013 always be the same? If so, just calculate the adjacent cells and make the second column the sum of that calculation.
posted by Ruthless Bunny at 7:48 AM on April 23, 2013