1

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?

2 Answers 2

4

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.

3
  • Wow, and so much simpler than my solution!!! +1
    – stema
    Aug 4, 2011 at 8:51
  • @stema - keep it a secret: OFFSET is the source of much of my Excel-fu... ;-) Aug 4, 2011 at 8:54
  • Bingo! Perfect, works first time. Thanks for the help.
    – Darren Cook
    Aug 4, 2011 at 8:57
0

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

6
  • 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, 2011 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, 2011 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, 2011 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, 2011 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, 2011 at 8:54

You must log in to answer this question.