Formula help

I

Ian

I need a formula which can be copied to any row in a spreadsheet to produce
a sum of the rows above startign at row 2.
eg =SUM(G2:Gthisrow-1)
I also need one to calculate the difference MIN & MAX in the same manner

It is going to be stored in another sheet and manually copy/pasted so I
suspect I may need to start the range reference with G$2, but I'm not sure.
Anyway, that's the easy bit. The hard bit (to me) is the relative reference.
I know how to do it in VBA code, but I need an in-cell formula.

Any ideas?
 
D

Don Guillett

Where 9999 is any number larger than possible in your range
=SUM(A1:OFFSET(A1,MATCH(99999,A:A),0))
 
G

Gary''s Student

This is even better because it does not depend on the column ID:

=SUM(INDIRECT(ADDRESS(2,COLUMN())):INDIRECT(ADDRESS(ROW()-1,COLUMN())))
 
H

Harlan Grove

Ian wrote...
I need a formula which can be copied to any row in a spreadsheet to produce
a sum of the rows above startign at row 2.
eg =SUM(G2:Gthisrow-1)
I also need one to calculate the difference MIN & MAX in the same manner
Click to expand...

The other respondents were warm, but the best answer involving volatile
functions is

=SUM(INDIRECT("R2C7:R[-1]C7",0))

if you always want to refer to column G (the 7th column in the
worksheet). If the formula above would have been entered in column G in
the row immediately below the values to be summed, and if you'd be
doing this for other columns as well, try

=SUM(INDIRECT("R2C:R[-1]C",0))

But you don't need to use volatile functions.

=SUM(G$2:INDEX(G:G,ROW()-1))

If the MIN and MAX formulas would be underneath the SUM formula, they'd
need to refer, respectively, to ranges ending 2 or 3 rows above, so

=MIN(G$2:INDEX(G:G,ROW()-2))

and

=MAX(G$2:INDEX(G:G,ROW()-3))
 
D

Dave Peterson

Another one if the formula is going in G100:

=sum(g2:eek:ffset(g99,-1,0))

Adjust the 99 to be one less row than the cell's row that's getting the formula.

But I'm confused about what you mean about storing it in another worksheet.

And an alternative suggestion...

Insert a new Row 1 and put the formula in that.

=sum(g3:g65536)
(headers in row 2)

And if you use window|Freeze panes, your totals will always be visible.

And if you used data|filter|autofilter and =subtotal() as your formula, then
those formulas would just count/sum... the visible cells.
 
O

OM

Great solution Gary's Student! I thought I was "reasonably good" at Excel,
but seems I have a lot left to learn....

OM
 
P

Peo Sjoblom

Here's a non volatile version (not tested except for on a small dataset)
the bonus of using non volatile functions (INDIRECT is volatile) is that
you won't be asked to save the workbook every time you open and close
the workbook

=SUM(INDEX($1:$65536,2,COLUMN()):INDEX($1:$65536,ROW()-1,COLUMN()))

Regards,

Peo Sjoblom,
 
I

Ian

Thanks, Harlan. This seems to be what I'm looking for.

Thanks, also to everyone else who's chipped in. I'm sure I can learn
something from each of your responses.

Happy new year.

--
Ian
--
Harlan Grove said:
Ian wrote...
I need a formula which can be copied to any row in a spreadsheet to
produce
a sum of the rows above startign at row 2.
eg =SUM(G2:Gthisrow-1)
I also need one to calculate the difference MIN & MAX in the same manner
Click to expand...

The other respondents were warm, but the best answer involving volatile
functions is

=SUM(INDIRECT("R2C7:R[-1]C7",0))

if you always want to refer to column G (the 7th column in the
worksheet). If the formula above would have been entered in column G in
the row immediately below the values to be summed, and if you'd be
doing this for other columns as well, try

=SUM(INDIRECT("R2C:R[-1]C",0))

But you don't need to use volatile functions.

=SUM(G$2:INDEX(G:G,ROW()-1))

If the MIN and MAX formulas would be underneath the SUM formula, they'd
need to refer, respectively, to ranges ending 2 or 3 rows above, so

=MIN(G$2:INDEX(G:G,ROW()-2))

and

=MAX(G$2:INDEX(G:G,ROW()-3))
Click to expand...
 
I

Ian

The problem is that I don't know what row it will be going in. It will vary
each time and I was looking for a solution where I can copy/paste a range of
cells containing formulae from another location.

The spreadsheet will be produced automatically by Access as an email
attachment. Before the email goes, I currently open the attachment and
manually add formulae. It would be much earier if I could copy/paste
ready-made formulae from another spreadsheet.

I probably didn't explain myself too well in my OP.

Thanks for the input, but I think Harlan's message addresses my needs.

Happy new year.
 
D

Dave Peterson

You'll know where you're going to store the formula, right.

If you store it in A3, then try this:
=SUM(A$2:OFFSET(A3,-1,0))

Then copy and paste it into other cells to see if it does what you want. Excel
is pretty smart and will adjust the formula nicely.

Stay out of A2 and A1, though.
The problem is that I don't know what row it will be going in. It will vary
each time and I was looking for a solution where I can copy/paste a range of
cells containing formulae from another location.

The spreadsheet will be produced automatically by Access as an email
attachment. Before the email goes, I currently open the attachment and
manually add formulae. It would be much earier if I could copy/paste
ready-made formulae from another spreadsheet.

I probably didn't explain myself too well in my OP.

Thanks for the input, but I think Harlan's message addresses my needs.

Happy new year.
Click to expand...
 
Top