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.
BalcoAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Rgonzo1971Commented:
Hi,

pls try

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

Open in new window

Regards
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.