Sum using next cell

  • Thread starter Thread starter gibsol
  • Start date Start date
G

gibsol

I have a table that shows running averages, I want the sum to be calculated
using cell a1 - b1 when I enter data into col B, then b1 - c1, when I enter
data into col c, and so on.
 
If there is nothing in row 1 except the numbers....

=SUM(OFFSET(A1,0,COUNTA(1:1)-2,1,2))

HTH,
Bernie
MS Excel MVP
 
This should work whether the row is completely filled in or not...

=INDEX(1:1,SUMPRODUCT(MAX(COLUMN(1:1)*(1:1<>"")))-1)+INDEX(1:1,SUMPRODUCT(MAX(COLUMN(1:1)*(1:1<>""))))

If you don't mind array-entered** formulas, you can use this instead...

=INDEX(1:1,MAX(COLUMN(1:1)*(1:1<>""))-1)+INDEX(1:1,MAX(COLUMN(1:1)*(1:1<>"")))

** array-entered means commit the formula by pressing Ctrl+Shift+Enter.

Rick
 
Thanks very much for the assistance, unfortunately entering this fomula has
raised a different error, which I am trawling through at present.

cheers
 
Thanks for the information it now given me a different problem with my
formula that I need to work on.

thanks for all the help.
 
Back
Top