Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUM to infinity on a column
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUM to infinity on a column
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 "Bobby" wrote: 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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUM to infinity on a column
On 13 Jun, 12:46, Duke Carey
wrote: 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 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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUM to infinity on a column
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. -- Arvi Laanemets ( My real mail address: arvi.laanemets<attarkon.ee ) "Bobby" wrote in message oups.com... 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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUM to infinity on a column
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. -- David Biddulph "Bobby" wrote in message oups.com... 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 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUM to infinity on a column
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} -- Arvi Laanemets ( My real mail address: arvi.laanemets<attarkon.ee ) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
divide column(x) by column(y) to give column(x/y) in excel? | New Users to Excel | |||
Based on a condition in one column, search for a year in another column, and display data from another column in the same row look | Excel Discussion (Misc queries) | |||
infinity and beyond | Excel Discussion (Misc queries) | |||
making a sum of a eq. with a var running form 0-infinity. | Excel Discussion (Misc queries) | |||
How to get excel to recognise "infinity" for asymptotes in charts | Charts and Charting in Excel |