Refresh ColumnTotal
 

I have a data table to which fresh rows keep getting added evry now and then.
how to i put a toal at the top of a column which will refresh everytime a
fresh row is added.

Refresh ColumnTotal
 

Just sum from the cell below the sum formula to the last cell of the column.
The following could be placed in cell A1. It is for xl2007. Earlier versions
do not have as many rows. Note: You can't include the cell where the formula
is because you would get a circular reference.

=SUM(A2:A1048576)
--
Regards,

OssieMac


"a m spock" wrote:

I have a data table to which fresh rows keep getting added evry now and then.
how to i put a toal at the top of a column which will refresh everytime a
fresh row is added.

Refresh ColumnTotal
 

also it does not seem to be very elegant!

"OssieMac" wrote:

Just sum from the cell below the sum formula to the last cell of the column.
The following could be placed in cell A1. It is for xl2007. Earlier versions
do not have as many rows. Note: You can't include the cell where the formula
is because you would get a circular reference.

=SUM(A2:A1048576)
--
Regards,

OssieMac


"a m spock" wrote:

I have a data table to which fresh rows keep getting added evry now and then.
how to i put a toal at the top of a column which will refresh everytime a
fresh row is added.

Refresh ColumnTotal
 

i thought of this. but does it have any impact on file sizes etc. or the
time it takes to refresh, save or reopen the files?
"OssieMac" wrote:

Just sum from the cell below the sum formula to the last cell of the column.
The following could be placed in cell A1. It is for xl2007. Earlier versions
do not have as many rows. Note: You can't include the cell where the formula
is because you would get a circular reference.

=SUM(A2:A1048576)
--
Regards,

OssieMac


"a m spock" wrote:

I have a data table to which fresh rows keep getting added evry now and then.
how to i put a toal at the top of a column which will refresh everytime a
fresh row is added.

Refresh ColumnTotal
 

I ran some tests on the file size.

I placed 30 random numbers in column A with the sum formula in cell A1 and
saved the file and checked its size. (In the open file dialog box, by right
clicking the file name and then properties, the file parameters are displayed
including an accurate size in bytes.)

I then changed the formula to include cells A2 to end of worksheet and re
checked the file sizes.

xl 2002 test:

30 numbers: File size was 13,824 bytes and 16,384 bytes on the disk.
Full column: File size was unchanged.

xl2007 test

30 numbers: File size was 8,447 bytes and 12,288 bytes on the disk.
Full column: File size was 8,447 bytes and size on disk unchanged.

I increased the number of columns to 100 and while the files were a little
larger in all tests, the difference due to summing only the data or summing
the entire column was negligible.

As far as time to open the file, I was unable to tell the difference in the
time.

If the formula does what is required, why worry about elegance.
--
Regards,

OssieMac


"a m spock" wrote:

also it does not seem to be very elegant!

"OssieMac" wrote:

Just sum from the cell below the sum formula to the last cell of the column.
The following could be placed in cell A1. It is for xl2007. Earlier versions
do not have as many rows. Note: You can't include the cell where the formula
is because you would get a circular reference.

=SUM(A2:A1048576)
--
Regards,

OssieMac


"a m spock" wrote:

I have a data table to which fresh rows keep getting added evry now and then.
how to i put a toal at the top of a column which will refresh everytime a
fresh row is added.

Refresh ColumnTotal
 

Many thanks for all the trouble you have taken. Much appreciated.

"OssieMac" wrote:

I ran some tests on the file size.

I placed 30 random numbers in column A with the sum formula in cell A1 and
saved the file and checked its size. (In the open file dialog box, by right
clicking the file name and then properties, the file parameters are displayed
including an accurate size in bytes.)

I then changed the formula to include cells A2 to end of worksheet and re
checked the file sizes.

xl 2002 test:

30 numbers: File size was 13,824 bytes and 16,384 bytes on the disk.
Full column: File size was unchanged.

xl2007 test

30 numbers: File size was 8,447 bytes and 12,288 bytes on the disk.
Full column: File size was 8,447 bytes and size on disk unchanged.

I increased the number of columns to 100 and while the files were a little
larger in all tests, the difference due to summing only the data or summing
the entire column was negligible.

As far as time to open the file, I was unable to tell the difference in the
time.

If the formula does what is required, why worry about elegance.
--
Regards,

OssieMac


"a m spock" wrote:

also it does not seem to be very elegant!

"OssieMac" wrote:

Just sum from the cell below the sum formula to the last cell of the column.
The following could be placed in cell A1. It is for xl2007. Earlier versions
do not have as many rows. Note: You can't include the cell where the formula
is because you would get a circular reference.

=SUM(A2:A1048576)
--
Regards,

OssieMac


"a m spock" wrote:

I have a data table to which fresh rows keep getting added evry now and then.
how to i put a toal at the top of a column which will refresh everytime a
fresh row is added.