SUM to infinity on a column

  • Thread starter Thread starter Bobby
  • Start date Start date
B

Bobby

Hi,
I have a spreadsheet which is generated by an export from Access.
Column C does not come from Access and is a totals column, basically
Column A - Column B. I have put some conditional formatting on Column
C so that it is red if the value is negative and green if positive. I
have put a calculaion in Column C which is, "=sum(A1-B1)". I realise
that if I drag Column C down, then each cell will become "=sum(A2-B2)"
and "=sum(A3-B3)" etc.

However, my problem is I have no way of knowing how many rows will be
in the spreadsheet. It could be 10 or it could be 10,000. Is there
anyway that I can make Column C always reaslise that it will be Column
A - Column B on the same row, bearing in mind that row 1 is the
header and cannot be included in the calculation?

Thanks for your help

Bobby
 
Option 1
You can copy your formula down the entire column in anticipation of 65
thousand rows (or over 1 million, if in Excel 2007)

Option 2
Select the cell with the formula. Double-click the small square at the
bottom right of that cell and Excel will copy the formula down as far as you
have contiguous entries in column B

Option 3
Do you calculation of A-B in Access and import all three columns to Excel
 
Option 1
You can copy your formula down the entire column in anticipation of 65
thousand rows (or over 1 million, if in Excel 2007)

Option 2
Select the cell with the formula. Double-click the small square at the
bottom right of that cell and Excel will copy the formula down as far as you
have contiguous entries in column B

Option 3
Do you calculation of A-B in Access and import all three columns to Excel
Click to expand...

Thanks
I'm trying to avoid option 1, but if necessary thats what I'll do.
First I'll experiment with option 2. I thought about Option 3 myself,
but unfortunately Column B will be manually changed in excel, so I
need Column C to change with it.

Bobby
 
Hi

When you use an ODBC Query to get access table data into Excel, then in Data
Range Properties you can check 'Fill down formulas...'.
Into cell C1 enter the formula
=IF(ROW()=1,"Sum",SUM(A1,B1))
, and copy down for length of your table (the formuls has to start from 1st
row, otherwise it will be lost whenever query returns no data).

Now, when you requery, and the number of returned rows changes, the range
with formula is automatically synchronized with query data range.
 
As a matter of interest, why are you using SUM()? Why not just =A1-B1? SUM
is a function that adds a number of values. If you wanted you could use
=SUM(A1,-B1), but with only one argument as you've got it the function does
nothing.

You could equally have used =PRODUCT(A1-B1) or =AVERAGE(A1-B1) or
=MEDIAN(A1-B1) or =MAX(A1-B1) or =MIN(A1-B1) or probably a few others, but
in each case the function is redundant with only one argument.
 
A correction


=IF(ROW()=1,"Sum",SUM(A1,-B1))


{I recommend SUM(A,-B) here instead simply A-B, to avoid errors whenever one
or both cells are empty or contain text values}
 
A column can be referenced with e.g. B:B, so the average value of an entire column can be optained with

=AVERAGE(B:B)
 
Back
Top