• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 401
  • Last Modified:

How to use Offset function in excel where the starting cell is a variable

Hi,

I am using an offset function to sum a range of cells as follows

=SUM(OFFSET(Days!A13,0,8,21,1)) and this works fine

My issue is the starting cell can vary as this references the first Monday of the month. I can find the first Monday of the month in a range cells from A10 to A31 use an if statement and dropping the cell reference into another cell say A1 using the following CELL("address",Days!A13) so A1 now has Days!$A$13 in it.

When I try to do the following and replace the Days!A13 with CELL("contents",$A$1) in the Offset function so looks like this

=SUM(OFFSET(CELL("contents",$A$1),0,8,21,1))

Excel comes up with an error and wont even accept the formula.
0
Balco
Asked:
Balco
  • 5
1 Solution
 
Rgonzo1971Commented:
Hi,

pls try

=SUM(OFFSET(INDIRECT($A$1,1),0,8,21,1))

Open in new window

Regards
0
 
Rob HensonFinance AnalystCommented:
The syntax of the OFFSET function is, as you are no doubt aware:

=OFFSET(Reference,RowsOffset,ColumnsOffset,RowsHeight,ColumnsWidth)

With that you can use the columns or rows offset value to move to the correct row/column, leaving the Reference cell the same.

So your current formula starts at A13, moves 0 rows down and 8 columns across and returns a range that is 21 rows high and 1 column wide. I am suggesting that you stay with A13 as your reference but adjust the 0 or 8 to allow for the starting point to change.

You can include a MATCH function to find the correct date.

Thanks
Rob H
0
 
Rob HensonFinance AnalystCommented:
So I think it would adjust to:

=SUM(OFFSET(A9,MATCH(Date,A10:A31,0),8,21,1))

In other words start at A9 and move down A10:A31 until the date is found, go across 8 columns and sum the range that is then 21 rows by 1 column from that point.

I have deliberately started at A9 because if the required date is in cell A10, the result of the MATCH will be 1 so the OFFSET will move down 1 from A9.

Thanks
Rob H
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
BalcoAuthor Commented:
Hi Rob,

I have adjusted your formula to work with my spreadsheet, but am having trouble with the match function.

The cells A10:A31 are filled by another formula with the date and formatted as the Day eg "Mon", "Tue"

So I have set this up but with no luck

=SUM(OFFSET(Days!A9,MATCH("Mon",TEXT(CELL("contents",Days!A10:A31),"ddd"),0),8,21,1)) as the TEXT function returns only the first day eg "Sun" for this month.

Can you please have a look at the match function and let me know if there is way to get this to work.

Thanks Mark
0
 
Rob HensonFinance AnalystCommented:
As A10:A31 are only formatted as days, the true value in the cells is still the date so you don't need to convert to text.

Anyway, the MATCH function won't work in the way you tried. The lookup range has to be a range, by trying to convert that range to text within the formula you will create an error, difficult to explain at 11:30 at night but will try tomorrow. It might work if array entered, confirm entry will Ctrl & Shift & Enter.
0
 
Rob HensonFinance AnalystCommented:
As you have spotted,  the TEXT function is only creating one entry and is using the first cell in the range A10:A31 so the MATCH is finding the relevant entry.

See attached file, I have tried to make a a mockup of your file with the info that I had. The list of dates in A10:A31 and values in column I (8 columns over) are just manual entries.

Enter first day of month in cell B2, from this G2 will calculate the first Monday using the following formula:

=IF(WEEKDAY(B2,1)=1,B2+1,IF(WEEKDAY(B2,1)=2,B2,CEILING(B2,7)+2))

In text, this says IF WEEKDAY of B2 = Sunday THEN use B2 + 1, ELSE IF WEEKDAY of B2 is Monday then use B2, ELSE round up B2 to following Saturday and add 2 to make the following Monday.

In H2 I have just put a link to G2 and formatted differently just to show that the formatting as "ddd" doesn't change the true value of the cell.

Your SUM(OFFSET(...)) formula is then in L5.

I have originally worked on using dates in Feb and as it happens the first Monday was only the second row in the list. So the OFFSET is then summing all but 1 of the 22 values in column I.

However, there is also a Jan sheet in the file and as you can see the 21 rows in the SUM goes beyond the bottom of the values in the I10:I31 range by three rows. Do you want this to happen? If the cells below the I10:I31 range will always be empty then it won't be a problem; if there could be values below that range we can adjust the 21 within the OFFSET function to allow for it.

Alternatively, if you are just trying to add up the values from column I where date in column A is greater than/equal to the First Monday then you can use the SUMIF function which is much simpler:

=SUMIF($A$10:$A$31,">="&G2,$I$10:$I$31)

Shown in cell O5 of Feb sheet.

Hope this all helps. If not then can you upload a sample sheet to show what you need.

Thanks
Rob H
Days.xlsx
0
 
Rob HensonFinance AnalystCommented:
So if you were going to accept the first solution, it would have been nice to know before I continued with my efforts.

Thanks
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now