If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
? avoid changing sum function as rows added?
Hello Group:
I'm obviously new. To keep it simple, here's how I'll pose the question: Suppose I have multiple rows and two columns. Column A contains names of people Column B contains each person's age The last cell in column B is to be an average of the ages, so in that cell I insert the function =AVERAGE(B1:B100) This works fine, but as I add rows, I have to change the formula to include the recently added row, B101, then B102, etc) Is there any way I can make the function automatically include the last cell? Sort of like a wild symbol? It's not a big problem, but I'm just wondering if that's possible. Thanks. Jack |
#3
|
|||
|
|||
? avoid changing sum function as rows added?
On Wed, 25 Oct 2006 23:25:10 +0100, "Sandy Mann"
wrote: JClark, Try using the OFFSET() Function: =AVERAGE(B1:OFFSET(B100,-1,0)) To avoid the #DIV/0! error: =IF(COUNT(B1:OFFSET(B100,-1,0))=0,"",AVERAGE(B1:OFFSET(B100,-1,0))) Sandy, Thanks for the suggestion. I think it will require a bit of research on my part before I can understand it. I'll copy your note and work on it this weekend. Seemed to me as though there ought to be a way to do it. Best regards, Jack |
#4
|
|||
|
|||
? avoid changing sum function as rows added?
Jack,
=AVERAGE(B1:OFFSET(B100,-1,0)) the range being averaged starts in B1 and goes to the cell that the formula is in (B100) and then offsets -1 rows ie the row above, and zero columns right ie the same column. The range is therefore B1:B99. When you sit in row 100 and insert a new row, XL will insert an new row 100 and move the formula down to row 101. The offset part formula will be indexed so that it will then say OFFSET(B101,-1,0) ie still the row above -- HTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings with @tiscali.co.uk "JClark" wrote in message ... On Wed, 25 Oct 2006 23:25:10 +0100, "Sandy Mann" wrote: JClark, Try using the OFFSET() Function: =AVERAGE(B1:OFFSET(B100,-1,0)) To avoid the #DIV/0! error: =IF(COUNT(B1:OFFSET(B100,-1,0))=0,"",AVERAGE(B1:OFFSET(B100,-1,0))) Sandy, Thanks for the suggestion. I think it will require a bit of research on my part before I can understand it. I'll copy your note and work on it this weekend. Seemed to me as though there ought to be a way to do it. Best regards, Jack |
#5
|
|||
|
|||
? avoid changing sum function as rows added?
JClark,
Try this: At the bottom of your column A (names) & column B (ages) Insert a blank cell in both columns A & B. When Excel ask you to move cells,Select down. Check your last cell formula, make sure it added the last cell in the formula. Eventhough it is blank. The next time you want to add a name select these new cells (empty ones), insert and down as above. Your Average formula will adjust automatically, even if you decide to insert in the middle of your columns(as long as you insert two cells). hth :-) "JClark" wrote: Hello Group: I'm obviously new. To keep it simple, here's how I'll pose the question: Suppose I have multiple rows and two columns. Column A contains names of people Column B contains each person's age The last cell in column B is to be an average of the ages, so in that cell I insert the function =AVERAGE(B1:B100) This works fine, but as I add rows, I have to change the formula to include the recently added row, B101, then B102, etc) Is there any way I can make the function automatically include the last cell? Sort of like a wild symbol? It's not a big problem, but I'm just wondering if that's possible. Thanks. Jack |
Thread Tools | |
Display Modes | |
|
|