? 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
0
10/25/2006 9:31:59 PM
excel.newusers 15348 articles. 2 followers. Follow

4 Replies
407 Views

Similar Articles

[PageSpeed] 21

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)))

-- 
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

sandymann2@mailinator.com
Replace@mailinator.com with @tiscali.co.uk


"JClark" <jclark@nomail.invalid> wrote in message 
news:p2lvj2tpk6dcsgpfjsnett1iftdot2dask@4ax.com...
> 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 


0
sandymann2 (1054)
10/25/2006 10:25:10 PM
On Wed, 25 Oct 2006 23:25:10 +0100, "Sandy Mann"
<sandymann2@mailinator.com> 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
0
10/26/2006 9:58:48 PM
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

sandymann2@mailinator.com
Replace@mailinator.com with @tiscali.co.uk


"JClark" <jclark@nomail.invalid> wrote in message
news:kqb2k21oa5mlg8ku4541cfbbbqjj04jcbs@4ax.com...
> On Wed, 25 Oct 2006 23:25:10 +0100, "Sandy Mann"
> <sandymann2@mailinator.com> 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




0
sandymann2 (1054)
10/26/2006 10:47:27 PM
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
> 
0
FloMM2 (102)
10/28/2006 7:37:02 PM
Reply:

Similar Artilces:

Changing Radians to Degrees
Hi, I am using Excell 2007, and I prefer to use degrees rather than radians. When I calculate Sin (angle), excel identifies the angle as in Radians. How can I make "degrees" to be my default unit of angles? Leo On Sun, 15 Nov 2009 12:34:13 +0300, "Leonard" <akwilapo@chem.udsm.ac.tz> wrote: >Hi, >I am using Excell 2007, and I prefer to use degrees rather than radians. >When I calculate Sin (angle), excel identifies the angle as in Radians. How >can I make "degrees" to be my default unit of angles? >Leo As far as I know, ...

how do you make a line of a chart change color halfway through?
How could you change the color of a line in a line chart so that at some point on the graph the line changes color? Say like the line chart shows data over the months of a year but at month July, the line on the chart changes from blue to green and is green the rest of the months. I am asking about 1 line being two colors. Hi, You could format each segment of the line. Select the series and then use the Right cursor to move through the segments. Note if you only have 1 series changing a section of the series will cause the legend to report each segment. The other way is to use multip...

Adding note field in Smartlist builder
I would like to include the note index in my inventory transactions report from Smartlist builder. When I run the report from my smart list builder I can click on column and add the note index field to my report but all I get is 0.0000. I would like to be able to print the comments I write in the note index box onto my report. I would like if someone coudl provide soem instructions on how to do this. Thanks -- MV ...

Can I make a row "float"?
I would like to make a row float. So if I scroll down to row 211 or whatever, I will have one row tha will always be on top no matter how far down I scroll. Thanks in advance. dvcrogers@sbcglobal.ne -- dvcroger ----------------------------------------------------------------------- dvcrogers's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=3262 View this thread: http://www.excelforum.com/showthread.php?threadid=52413 Say you want row 4 to freeze Select row 4, then go to window and freez -- davesexce --------------------------------------------------------...

Change Folder List Text Color (2002)
Is there anyway to change the color of the text listed in the folder view? ie - "Inbox" text color from black to red, "Sent" from black to blue" No. --� Milly Staples [MVP - Outlook] Post all replies to the group to keep the discussion intact. Due to the (insert latest virus name here) virus, all mail sent to my personal account will be deleted without reading. After searching google.groups.com and finding no answer, jsonne@verizon.net asked: | Is there anyway to change the color of the text listed in | the folder view? ie - "Inbox" text color from b...

How to change Menu font in Outlook 2007?
Hi I hate new ClearType font in outlook2007, i can turn off Cleartype and change mail font, but cant change Menu font, now it use Segoe or Calibri without ClearType and it look awful. Thx No, it always uses Segoe UI. Patrick Schmid [OneNote MVP] -------------- http://pschmid.net *** Office 2007 RTM Issues: http://pschmid.net/blog/2006/11/13/80 Office 2007 Beta 2 Technical Refresh (B2TR): http://pschmid.net/blog/2006/09/18/43 *** Customize Office 2007: http://pschmid.net/office2007/customize RibbonCustomizer Add-In: http://pschmid.net/office2007/ribboncustomizer OneNote 2007: http://pschm...

create chart /table excel-save, close & reopen colors change? Why
When I create a chart/table in Excel - save, close & reopen to use again, the colors have changed for my formating. How do I set the formating so that the colors stay the same - I have tried styles with no luck. The colors need to be the company approved. Thank you, Anne, I am unable to reproduce yr problem. Excel should NOT override yr manual settings. However,you can set yr company colours as default under Tools/Options/[Color tab]. Here are the colours Excel uses by default for the workbook. (Under that thin line you see the defaults for fill and line colours.) Hope this...

Dialog member function protection
Hello When you subclass CDialog into CMyDialog and know that you won't subclass CMyDialog anymore. Where do you put subsequent utility or misc member functions? In private, protected or public? Is it a matter of style or there is a reason for taking a specific choice? (Knowing that your dialog is not intended to be overwritten). -- Elias They should only be public if you want to allow other parts of your application to access them. A good OOP design should leave as little public members as possible. In general, it's a good idea to leave most hidden members protected. There is ...

Adding on to a formula across rows and columns
I need to add a second section of a formula onto an existing formul across rows and columns. The problems are: -Not all cells currently contain formulas (some are blank) -Not all the formulas are the same (there are at least 2 different formulas among all the existing formulas that I want to add that secon formula or section to)...and the different formulas are somewha interspersed. -Some of the existing formulas contain relative references (I want t change them to absolute references) -If I use relative references to somehow copy a portion of a formul across all remaining rows and columns,...

How to change page number font/color
I have inserted page numbers into a 30-page document using the Insert command. How may I change the font size and color of the numbers. Ideally I'd like to create a number with a line above it. cuig wrote: > I have inserted page numbers into a 30-page document using the Insert > command. How may I change the font size and color of the numbers. Ideally > I'd like to create a number with a line above it. You can change the format of your page numbers just like the format of any other text. Ctrl+M to get to your master page, select the number, then change the font...

Change Status Workflow
Is the Change Status workflow rule treated like a Manual workflow in that you have to manually apply the rule - or will it fire autmatically? I am creating a very simple WF but it seems to only work if I apply it manually. Here is my WF: When an Account is created and the AccountRatingCode is set to a certain value [in this case "Premier"] or when the AccountRatingCode is set to "Premier" for an exisitng Account then create a task for the account owner to call that Premier account. Also, will workflows fire on custom schema fields? Change Status only applies to ch...

Changing Comments on Sales Invoices and Returns
I have posted Invoices and Returns that have incorrect comments on them. I know that the Comments are held in the SOP30200 in a field called Comment ID. In this case they did not use an established Comment ID, so it's reflected as untitled on the document and blank in the table. I'm trying to find out if someone has attempted to alter comments after the documents have posted. If I pull the historical document up in Inquiry>Sales>Sales Document>Sales Order Processing Document Inquiry>Sales Transaction Inquiry Zoom, I can see the old comment. I just don't know...

Only Display records before and after a change in teh value of a f
I have a table that looks like the following RUNID | DATETIME | PROCESS 0001 1/1/09 1201 Neck 0001 1/1/09 1202 Neck 0001 1/1/09 1203 Neck 0001 1/1/09 1204 Neck 0001 1/1/09 1205 Neck 0001 1/1/09 1206 Body 0001 1/1/09 1207 Body 0001 1/1/09 1208 Body 0001 1/1/09 1209 Body 0001 1/1/09 1210 Neck 0001 1/1/09 1211 Neck 0001 1/1/09 1212 Neck 0001 1/1/09 1213 Neck 0001 1/1/09 1214 Body 0001 1/1/09 1215 Body 0001 ...

my switchboard prevents me making changes to the database
the database opens with a switchboard screen and doesn't provide access to all the database tools so I can make changes, open tables, etc. In Access 2000 and 2002 you would hold down the control or shift key when you opened the database and it turned off the switchboard control features to open normally. I'm using Office 2003. Is there a similar key to press and hold when you open a database to turn the switchboard feature off? Thanks! Holding down the shift key works exactly the same in Access 2003 as in previous versions. If it's not working for you, perhaps someone set ...

How to change cell data from all upper to title case
I need to change several columns of data from all upper case to Title Case. What formula should I use & How can I apply the formula to the data in each cell? Check out the Proper() function. It changes any case to title case, and can be copied from cell to cell to apply the formula to your data. Check out the Proper() function. It changes any case to title case and you can copy it to a range of cells to apply it to your data. You have other answers showing the use of the PROPER Function to act upon one cell at a time. If you want to change many cells at once you will need a mac...

maximum number of rows that can be coppied to clipboard
is there a maximum number of rows that can be copied to clipboard from a table? using access 2002 on XP, Roland wrote: > is there a maximum number of rows that can be copied to clipboard from a > table? using access 2002 on XP, I have Access 2003 when I tried to copy a table with 70,000 records it said the limit was about 65,000. gls858 65536 -- Regards - Andy Wiggins FCCA www.BygSoftware.com Excel, Access and VBA Consultancy "Roland" <Roland@discussions.microsoft.com> wrote in message news:CF98F875-56C6-4C22-8695-D8CFBE47D12E@microsoft.com... > is there a...

want to turn PT row data into column data
original data: name win loss score paul 1 325 Jane 0 1 225 walt 1 400 nancy 0 200 jane 1 300 nancy 1 200 paul 1 365 walt 1 350 when i create p.t. I get (or a vague approximation) paul win 2 loss 0 avg 345 jane win 1 loss 1 avg 260 walt win 1 loss 1 avg 375 nancy win 0 loss 2 avg 200 but this is what I would like: name win loss avg Paul 2 0 345 Jane 1 1 260 walt 1 1 375 nancy 0 2 200 ---------------------------- HOw? tx phynkel, Grab the g...

What Excel macro puts data in the NEXT row of a sheet?
I have a list of data in col B of sheet 2 which I need to copy and transpose into sheet 1, then delete the data in sheet 2 to use the form again to copy and transpose onto the next line in sheet 1. I have tried with a macro shortcut I've called ctrl + e. (I am really using the data form process but cannot because I have too many inputs). Is there a simple macro I can copy and paste? ...

Public folders changes problem
Hi On Exchange 2007 I created public folder named "IT" and inside a calendar called "IT calnedar" that is used for all events in our IT department. It's strange when our secretary adds events into this calendar and mark them with different colors (categories) these changes (the right color for each event) are only shown in Outlook 2003 or OWA, but in Outlook 2007 all colors are mixed up. We all have Outlook 2007 in IT department, so we all see wrong colors, but when I try to log-on through OWA or with a test machine that has Outlook 2003 installed everything is O...

Adding an Active X control to a cell
I would like to add a drop down lists to a bunch of cells. The control adds a box over the top of all the cells, trying to size it so that it fits over the top of the cell seems to be a rather inaccurate method of doing it. Hold down alt while you place it in the cell and it will fit the cell -- Regards, Peo Sjoblom "Rick" <rstenson@qualnetics.com> wrote in message news:317AA5B4-7970-45B2-8497-556670E48018@microsoft.com... > I would like to add a drop down lists to a bunch of cells. The control adds a box over the top of all the cells, trying to size it so that it f...

changing date range for budget
I need for an annual budget to be from September to September not for the calendar year. Is there any way to do this? I have money 2002. Thanks! Unfortunately, the Annual Budget report appears to only display information for the year selected. However, you can customize the Monthly Budget report to cover 9/1 to 8/30 and report summary information. It won't be broken down by month, however. Derrick "Shari" <anonymous@discussions.microsoft.com> wrote in message news:49a801c3e43d$901d1970$a401280a@phx.gbl... > I need for an annual budget to be from September to &g...

Expected Function or Variable
I recorded a macro in Word 2003 that searches for a word and then applies a character style to that word and to the following word. Then I found the answer on these boards how to loop through the rest of the document. But when I run the macro I get the "expected Function or Variable" error. Could somebody please assist? Thanks in advance. --Sandi Sub SectionStyle() ' ' SectionStyle Macro ' Searches for the word Section and applies the Section character Style to that word and the following word ' With Selection.Find.ClearFormatting Sel...

adding the bin field to the SOP Blank Packing Slip
Hello: The bin number field in Report Writer is available to be added to the SOP Blank Packing Slip in GP 10.0. But, upon adding this field, the default bin for the item does not print. Why is that? This is a non-Multibin environment. But, the items again do have a single default bin attached to them. Thanks! childofthe1980s ...

combine hide row macro with conditional formatting
I'm sure one of you gurus can solve this in a flash. I've recorded a simple macro to hide a row (Alt O, Alt R, Alt H) but I want it to run automatically based on a cell value. For example: If cell value "A1" is "x" then I want the macro to run, and repeat itself down the column "A2, A3" etcetera until it reaches a blank cell. Is there a way to plug my existing macro "hiderow" into Conditional Formatting for a column; or is there a simpler solution? ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.c...

Null Return on Count Function
I have the following Query: SELECT IIf(Count(*) Is Null,0,Count(*)) AS [No longer in Facility] FROM Residents GROUP BY Residents.[Last Status Date] HAVING (((Residents.[Last Status Date]) Between [Forms]![Open_PIP Report]![DateStart] And [Forms]![Open_PIP Report]![DateEnd])); The query is still returning a null value. Many of the other queries in my project require this to be at least a zero value to calculate properly. I have tried using the NZ function also. Perhaps I am using it incorrectly....any ideas? Hi - I don't quite understand your question. First, Count(*) never retu...