Sum using next cell
 

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.

Sum using next cell
 

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


"gibsol" wrote in message
...
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.

Sum using next cell
 

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


"gibsol" wrote in message
...
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.

Sum using next cell
 

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

cheers

"Bernie Deitrick" wrote:

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


"gibsol" wrote in message
...
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.

Sum using next cell
 

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.

"Rick Rothstein (MVP - VB)" wrote:

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


"gibsol" wrote in message
...
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.