Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

It's been a long time since I needed to use Excel and I need a little help with copying a formula.

This is a simplified explanation of what I'm trying to do:

A1:365 has daily sales figures in it.

In column B I want to calculate the weekly sales, therefore the formula in B1 is =SUM(A1:A7)

If I copy the formula down B2 changes to =SUM(A2:A8), but I would like it to say =SUM(A8:A14)

Can this be done without the need to re-type the formula 52 times?

share|improve this question

migrated from stackoverflow.com Aug 5 '11 at 13:44

This question came from our site for professional and enthusiast programmers.

2 Answers 2

up vote 4 down vote accepted

As already mentioned INDIRECT() is an option. Another is OFFSET()...

So B1 would become

=SUM(OFFSET($A$1,(ROW()-1)*7,0,7,1))

and copy down as before.

Deconstructing a little, OFFSET(reference, rows, cols, height, width) defines a range that starts rows down and cols across from reference whose dimensions are height rows and width columns. (The last two are optional if you only want a 1x1 range). ROW() with no argument returns the row number of the cell containing the function, so in the case above it returns 1 in B1, 2 in B2 etc, so we just subtract 1 and multiply by our desired size (7 in this case) to figure out where in the list our target for summing should start.

share|improve this answer
    
Wow, and so much simpler than my solution!!! +1 –  stema Aug 4 '11 at 8:51
    
@stema - keep it a secret: OFFSET is the source of much of my Excel-fu... ;-) –  Mike Woodhouse Aug 4 '11 at 8:54
    
Bingo! Perfect, works first time. Thanks for the help. –  Darren Cook Aug 4 '11 at 8:57

Yes it can be done.

Try this here:

=SUM(INDIRECT(ADDRESS((CELL("row",B1)-1)*7+1,1)):INDIRECT(ADDRESS((CELL("row",B1))*7,1)))

Edit:

thanks to Mike Woodhouse (see his answer) I can simplify my solution a bit.

Row() (in the cell "B1") will do the same than CELL("row",B1) so

=SUM(INDIRECT(ADDRESS((ROW()-1)*7+1,1)):INDIRECT(ADDRESS(ROW()*7,1)))

will do the same a bit simpler.

Explanation:

You need to calculate the addresses then.

(CELL("row",B1)-1)*7+1 will give you the start cell. CELL("row",B1) gives me the number of the current row. Subtract 1 multiply by 7 and add 1 will give for each row the starting daily row as a number.

ADDRESS((CELL("row",B1)-1)*7+1,1) is making an address string from numbers, I give the calculated starting row number and the column (the 1 at the end)

INDIRECT(ADDRESS((CELL("row",B1)-1)*7+1,1)) indirect is making a range from a string.

The same than for the last wanted cell. INDIRECT(ADDRESS((CELL("row",B1))*7,1)) the difference is only the calculation of the ending row.

Then I give the whole thing to Sum and I am done. Just copy it down 52 times and you have the sum for every week.

Solution

share|improve this answer
    
Wow, would never have been able to work that out! Even with your great explanation it starts to make my brain hurt! –  Darren Cook Aug 4 '11 at 8:28
    
You are welcome, just vote me up and accept if it is working ;). If you have any questions to understand it or to get it working just ask. –  stema Aug 4 '11 at 8:32
    
Just pasted the formula into my workbook and it gives an error (highlights "row" in black) I'm using Excel 2010. Can see from your capture that it's working for you though?? –  Darren Cook Aug 4 '11 at 8:37
    
I am still using Excel 2003, but I checked on microsoft.com, 2010 does still have this function. Did you paste into cell "B1"? What happens if you just copy =CELL("row";B1) into another cell? (should give you 1 as result) –  stema Aug 4 '11 at 8:46
    
Yes, was in B1. Tried the =CELL("row";B1) but same error. Also tried saving as 97-2003 workbook but no joy. –  Darren Cook Aug 4 '11 at 8:54

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.