Appending or Up-dating a formula

I am using an =Ave function in a formula to average quite a few cell values
together.  I add new information all the time; sometimes 5 or 6 new cells at
a time.

How can I append or up-date my formula to include these new cells without
having to type the new cells into the formula?

When I make the cell that has the formula in it active/edit, all of the
cells that are referenced in the formula have a highlight around them.

Is there a key or key combination I can press while clicking on the cells I
want to add to the formula?



Thanks,

john


0
5/27/2004 3:48:52 PM
excel.misc 78881 articles. 5 followers. Follow

4 Replies
314 Views

Similar Articles

[PageSpeed] 30

Hi
why not make the range large enough. AVERAGE will ignore blank cells.
So try
=AVERAGE(A1:A1000)

--
Regards
Frank Kabel
Frankfurt, Germany

"john" <donotsendspam@mchsi.com> schrieb im Newsbeitrag
news:uvXZeIAREHA.3452@TK2MSFTNGP10.phx.gbl...
> I am using an =Ave function in a formula to average quite a few cell
values
> together.  I add new information all the time; sometimes 5 or 6 new
cells at
> a time.
>
> How can I append or up-date my formula to include these new cells
without
> having to type the new cells into the formula?
>
> When I make the cell that has the formula in it active/edit, all of
the
> cells that are referenced in the formula have a highlight around
them.
>
> Is there a key or key combination I can press while clicking on the
cells I
> want to add to the formula?
>
>
>
> Thanks,
>
> john
>
>

0
frank.kabel (11126)
5/27/2004 3:53:38 PM
....or, if your initial range was A1:A9, you can place this formula in A10:
=AVERAGE($A$1:OFFSET(A10,-1,)). Formula will automatically take in any new
cells you insert above A10.

If your initial range was A1:E1, you can place this formula in F1:
=AVERAGE($A$1:OFFSET(F1,,-1)). Will automatically take in columns inserted
to the left of Column F.

"When I make the cell that has the formula in it active/edit, all of the
cells that are referenced in the formula have a highlight around them. Is
there a key or key combination I can press while clicking on the cells I
want to add to the formula?"

No such key, but what you can do is grab the "fill handle" in the lower
right corner and drag the frame to include new cells.
-- 
DDM
"DDM's Microsoft Office Tips and Tricks"
Visit us at www.ddmcomputing.com


"Frank Kabel" <frank.kabel@freenet.de> wrote in message
news:%23TTQrKAREHA.3420@TK2MSFTNGP11.phx.gbl...
> Hi
> why not make the range large enough. AVERAGE will ignore blank cells.
> So try
> =AVERAGE(A1:A1000)
>
> --
> Regards
> Frank Kabel
> Frankfurt, Germany
>
> "john" <donotsendspam@mchsi.com> schrieb im Newsbeitrag
> news:uvXZeIAREHA.3452@TK2MSFTNGP10.phx.gbl...
> > I am using an =Ave function in a formula to average quite a few cell
> values
> > together.  I add new information all the time; sometimes 5 or 6 new
> cells at
> > a time.
> >
> > How can I append or up-date my formula to include these new cells
> without
> > having to type the new cells into the formula?
> >
> > When I make the cell that has the formula in it active/edit, all of
> the
> > cells that are referenced in the formula have a highlight around
> them.
> >
> > Is there a key or key combination I can press while clicking on the
> cells I
> > want to add to the formula?
> >
> >
> >
> > Thanks,
> >
> > john
> >
> >
>


0
DDMara (266)
5/27/2004 4:18:07 PM
Thanks Frank, but with out going into detail, that will not work for my
situation.

My original question stands:
After I make the cell that has the formula in it active/edit, all of the
cells that are referenced in the formula have a highlight around them.

Is there a key or key combination I can hold while clicking on the cells I
want to add to the formula?


"Frank Kabel" <frank.kabel@freenet.de> wrote in message
news:%23TTQrKAREHA.3420@TK2MSFTNGP11.phx.gbl...
> Hi
> why not make the range large enough. AVERAGE will ignore blank cells.
> So try
> =AVERAGE(A1:A1000)
>
> --
> Regards
> Frank Kabel
> Frankfurt, Germany
>
> "john" <donotsendspam@mchsi.com> schrieb im Newsbeitrag
> news:uvXZeIAREHA.3452@TK2MSFTNGP10.phx.gbl...
> > I am using an =Ave function in a formula to average quite a few cell
> values
> > together.  I add new information all the time; sometimes 5 or 6 new
> cells at
> > a time.
> >
> > How can I append or up-date my formula to include these new cells
> without
> > having to type the new cells into the formula?
> >
> > When I make the cell that has the formula in it active/edit, all of
> the
> > cells that are referenced in the formula have a highlight around
> them.
> >
> > Is there a key or key combination I can press while clicking on the
> cells I
> > want to add to the formula?
> >
> >
> >
> > Thanks,
> >
> > john
> >
> >
>


0
5/27/2004 4:36:35 PM
Hi
you can move the mouse to one of the corners of this selection and drag
this selection 8make it larger). You can also move this selection with
your mouse

--
Regards
Frank Kabel
Frankfurt, Germany

"john" <donotsendspam@mchsi.com> schrieb im Newsbeitrag
news:uXsuIjAREHA.3732@TK2MSFTNGP11.phx.gbl...
> Thanks Frank, but with out going into detail, that will not work for
my
> situation.
>
> My original question stands:
> After I make the cell that has the formula in it active/edit, all of
the
> cells that are referenced in the formula have a highlight around
them.
>
> Is there a key or key combination I can hold while clicking on the
cells I
> want to add to the formula?
>
>
> "Frank Kabel" <frank.kabel@freenet.de> wrote in message
> news:%23TTQrKAREHA.3420@TK2MSFTNGP11.phx.gbl...
> > Hi
> > why not make the range large enough. AVERAGE will ignore blank
cells.
> > So try
> > =AVERAGE(A1:A1000)
> >
> > --
> > Regards
> > Frank Kabel
> > Frankfurt, Germany
> >
> > "john" <donotsendspam@mchsi.com> schrieb im Newsbeitrag
> > news:uvXZeIAREHA.3452@TK2MSFTNGP10.phx.gbl...
> > > I am using an =Ave function in a formula to average quite a few
cell
> > values
> > > together.  I add new information all the time; sometimes 5 or 6
new
> > cells at
> > > a time.
> > >
> > > How can I append or up-date my formula to include these new cells
> > without
> > > having to type the new cells into the formula?
> > >
> > > When I make the cell that has the formula in it active/edit, all
of
> > the
> > > cells that are referenced in the formula have a highlight around
> > them.
> > >
> > > Is there a key or key combination I can press while clicking on
the
> > cells I
> > > want to add to the formula?
> > >
> > >
> > >
> > > Thanks,
> > >
> > > john
> > >
> > >
> >
>
>

0
frank.kabel (11126)
5/27/2004 4:41:16 PM
Reply:

Similar Artilces:

Formula #2
I am trying ot put in a formula. I have a two columns with Revenue listed. One is the Estimate, one is the actual. If there is not an amount in the Actual amount column, I added the cell that was in the Estimate cell. I color coded the cell because I am breakingit out by Sales Person. I have 3 Sales People. I went in and did the SUM and went into each cell that is colored coded to each person. I am getting an error. Please help. Thanks, Vanessa Not enough information to more than guess at what you might be doing wrong. How are you going into SUM? Are you clicking on the sum icon? ...

DTS Export fails on bad date
While trying to import an excel spreadsheet to a SQL Server table, DTS fails, pointing to a column with date values. I have looked at all the date values, and they appear correct. I need to get this excel spreadsheet loaded to a database table. Is there any way to pinpoint the exact cell causing the problem? Or, can you think of another way to export the data in the spreadsheet besides DTS that might not be so sensitive to data content? Thanks, Dean Slindee ...

How do I calculate escalation rates using dates and a set value
If I have a projected that needs to be escalated using start and completion dates by a certain percentage per year, how do I write the formula. For example if my project starts May 26, 2010 and lasts until may 26 2014 I want to add escalation to midpoint at 4% per year. ...

creating a formula.
Hi folks, I am trying to create a formula that will: if a1:a4= to either values 1,2,4 and 12 where value #1 is "Annua Leave" value 2 is "Sick Leave", value 4 is " Comp. Time", value 12 i "Leave Without Pay" respectively then the answer should be inputted i columns b1:b4 respectively. Can anyone help me create this formula ----------------------------------------------- ~~ Message posted from http://www.ExcelTip.com ~~View and post usenet messages directly from http://www.ExcelForum.com I'm not sure I understand your question, but here's a ...

formula help #8
The following formula queries and totals information on a specified sheet. In this case the specified sheet name is 2003 I have several sheets (2001, 2002, 2003, 2004.....) I would like to have the value for the sheet name in the formula reference a cell in which I could type in the name of the sheet I want to query. Can someone tell me how to accomplish this. Just changing the '2003' in the formula to the reference cell doesn't work. (FYI - the reference cell will be L1) =SUMIF(('2003'!H2:I400),+(H3),('2003'!D2:I400)) TIA Mike =SUMIF((INDIRECT("'...

disc date for next month
We want to be able to choose a discount date for the next month the discount is allowed. Our terms are 2% 10th net 15th. We can choose the due date for the 15th of the next month, but we can't choose the discount date as the 10th of the next month. All the invoices entered into the system from the 1st thru the 10th calculate the discount for the current month instead of the next month. ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the me...

combine text & date formula to show mmmm dd, yyyy?
How do I combine text and date formula to show the written date and not the Excel number date? I have text in a couple of cells and a date formula in another cell(input from a different worksheet). I have the formula =A3 & F3 & A4 in A1 and would like it to read: We went to the station on December 21, 2009 and took the train. However, it is reading We went to the station on 40168 and took the train. Thanks. Try something like this... A1 = Today is B1 = 12/21/2009 =A1&" "&TEXT(B1,"mmmm dd, yyyy") Returns: Today is December 21, 2009 ...

Increment value in a Formula
Hi, I'm using Excel version 2003. I have the following formula =IF(SUM('Section 3'!$H$1675:$J$1686)=0,"",SUM('Section 3'!$H$1675:$J$1686)) and would like to increment the cell references by 12. I was wondering if there is a way to do this using a bit of VBA code? So the result i'm looking for after code run is =IF(SUM('Section 3'!$H$1687:$J$1698)=0,"",SUM('Section 3'!$H$1687:$J$1698)). I will be running the formula along the columns so will need to increment the formula by 12 for each new column. Thanks Dave...

Is there a way to copy and paste the date into worksheets
Is there a way to copy and paste dates into the worksheet withou individually typing up each one -- mark_vi ----------------------------------------------------------------------- mark_vi_'s Profile: http://www.excelforum.com/member.php?action=getinfo&userid=2450 View this thread: http://www.excelforum.com/showthread.php?threadid=38137 Is there a way to insert it the date into the the worksheet tab (not th cells in the actual worksheet but the name of the worksheet itself -- mark_vi ----------------------------------------------------------------------- mark_vi_'s Profile: ht...

Formula question 03-03-10
I am trying to complete the following. In s141 i have a drop list with 'yes' and 'no' as possiblities. In x141 i have a drop list with 3 possible choices, .02 , .04 , or .08 g141 is the originating cell p159 is the answer cell. if s141 remains empty or has 'no' chosen from the drop list i need p159 to remain blank, however if 'yes' is in s141 then i need the answer from g141 multiplied by .02 or .04 or .08 (which ever one is chosen from the drop list in x141) to be displayed in p159. Can anybody help? Thanks in advance. Scoob...

Formaula for less than date and name begins with...!!! HELP!!!
IM trying to create a formula that gives me a total count for cells that are less than 02/05/10 and the name begins with FHLM, heres what i came up with... =COUNTIF(E:E,">="&DATE(1900,1,1))-COUNTIF(E:E,">="&DATE(2010,2,5))+COUNTIF(E:E,DATE(2010,2,5))-COUNTIF(U:U,"FHLM*") I keep gettin a bunch of ###### or 0, depending on how I move the data around.. Any ideas???? =SUMPRODUCT(--(E1:E100<=DATE(2010,2,5)),--(LEFT(U1:U100,4)="FHLM")) "Senor Martinez" wrote: > IM trying to create a formula that gives me...

Formula Help
Hi Folks: I have a row of 6 columns to add (E thru J) The values of each cell goes from 0 to 5 in 0.5 increments. If the value of E is less than 1.5, I'd like to just do s straight addition (max value 30.0) If the value of E is greater than 2.0, then I would like to double the added value of get 60.0 What I'm using is =IF(E12<2,,(E12+F12+G12+H12+I12+J12)*2) which works. . . However, if E12 is under 2.0, then it evaluates to 0 instead of 30 Any help wd be appreciated - Thanks Maybe... =MAX(30,(SUM(e12:j12)*2))*(1+(e12>2)) or =MAX(30,(SUM(e12:j12)*2))*if(e12>2,2,1...

Grouping dates in pivot table
I am pulling data from a SQL Server database to create a pivot table. Excel (07) is not recognizing the field as a date. This is a field I would like to group by in the pivottable. I am aware of all the techniques to convert this to a date field, but I am searching for an answer as to why XL pivot tables cannot consume the dates directly from a sql query. I have played with bringing the dates back in a number of differenent formats with no success. Any insights into this would be appreciated. Probably coming in as text and yuo may only need to copy an unused cell and paste sp...

how do i change dates on my calendar and keep my pics
Publisher doesn't support this. You would need to do it manually. -- Brian Kvalheim Microsoft Publisher MVP http://www.publishermvps.com ~pay it forward~ This posting is provided "AS IS" with no warranties, and confers no rights. "Bob T" <Bob T@discussions.microsoft.com> wrote in message news:68671977-F71B-444F-AE72-801D2B359FEE@microsoft.com... > ...

How can I turn off all formulas in Excel?
I am using Excel to make forms and I do not want any formulas. Even when I choose text in the format of a cell, Excel still puts in the formula. Example: When I type "+1% chance for 4 bolts to corrode" in a cell already formated as text, excel still changes it to a formula. There has got to be a setting I could use to turn off formulas when just using Excel as a form builder. Just format the range (or entire sheet) as Text beforehand (eg: select range, click Format > Cells > Number tab. Select "Text" > OK) -- Max Singapore http://savefile.com/projects/236895...

Sorting a column by using formula #3
I am trying to use sort function just to delete blank cells in between Sort order doesn't matter actually. Data is coming by the use of simple cell reference of "another sheet -- Prais ----------------------------------------------------------------------- Praise's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1558 View this thread: http://www.excelforum.com/showthread.php?threadid=27144 Hi you may use the following addin to filter out blank cells: http://www.rondebruin.nl/easyfilter.htm -- Regards Frank Kabel Frankfurt, Germany Praise wrote: &g...

how to create formula to divide two rows autoaatically
Is there any way to setup a sheet or create a formula so that it will divide the data in column A by Column B anytime the data is entered and put it into columnC ? What I am trying to do is create a spreadsheet for calculating fuel MPG. So I have columns as miles, gallons and the calculation as MPG. What I want to do is anytime a value is entered into miles and gallons, to calculate mpg and put it into that respective cell. Is there any way I can do this ? Thanks -- Tony Tony, in Column C, type the formula =sum(a1/b1). This should give you the result you are looking for. Hop...

Looking up a date
Trying to get this to work, but no success: =LOOKUP(A6,{DATEVALUE("3/26/2010"),DATEVALUE("6/24/2010"),DATEVALUE("9/22/2010")},{"3","2","1"}) The value in A6 is a function; not hard-coded. It seems to work with one date and one result vector, but not two, and ultimately I will need several, maybe 7 or 8. Any ideas? Thanks! Ryan-- You can't use functions in array constants. >ultimately I will need several, maybe 7 or 8. Why don't you just create a table in ascending order by date: .............A.......

VLOOKUP returning formula
I have a VLOOKUP on one worksheet =VLOOKUP(A1,'Tasks'!A1:C17,2,FALSE) which refers to looking up the 2nd column of the reference A1 on the sheet that the formula resides, but it only returns the formula not the requested information. My other columns work fine. The cell that A1 refers to is a drop down list but in the template it is as well. Any help? Sounds like the cell is formatted as TEXT. Change the cell format to General. Then, with the cell still selected hit function key F2 then hit Enter. -- Biff Microsoft Excel MVP "Jan Ogg" <JanOgg@d...

Display cell formula by function
Hi. Is there a way to perform a function which display the formula of the target cell (NOT the value of the cell)? {Fictitious function} ShowFormula(TargetCell) Eg: A1 = sum(3, 4) A2 = ShowFormula(A1) Answer of A2: sum(3, 4) -- Additional information: - I'm using Office XP - I'm using Windows XP You could copy the equation to the cell and then put " in front of it (short term) "0-0 Wai Wai ^-^" <x@x.com> wrote in message news:%23OeHX84sFHA.2880@TK2MSFTNGP12.phx.gbl... > Hi. > Is there a way to perform a function which display the formula of the >...

formula for longitude/latitude
any one know where one can get a formula that will parse x,y coordinates from degrees/minutes/seconds into decimal ? Maybe this one? http://www.cpearson.com/excel/latlong.htm -- Kind Regards, Niek Otten Microsoft MVP - Excel "Leo" <Leo@discussions.microsoft.com> wrote in message news:F63AA1EB-40AA-483D-A996-4FB6B8C2514B@microsoft.com... > any one know where one can get a formula that will parse x,y coordinates > from > degrees/minutes/seconds into decimal ? See: http://www.cpearson.com/excel/latlong.htm Regards, Peo Sjoblom "Leo" wrote: &...

Formula = ?
I am trying to create a column in a report that will give me the sum of the original retail price for all items on sale. I have tried Formula = ''where Item.SalePrice > 0, Item.Price * Item.Quantity" I get an error around the "where"...is there another way to say this? Formula = ''Item.Quantity*Item.SalePrice/Item.SalePrice*Item.Price" This would work except for the divide by 0 issue. Any ideas? Thanks! Hi Kat, You would need to use a Case Statement Formula = "case when Item.SalePrice > 0 then Item.Price * Item.Quantity...

Format Date 05-28-10
Hi, all, How do I format the date to show May, 2010? The closest I can get is May-10 from the drop down list. Thanks, Malcolm Malcolm wrote: > Hi, all, > How do I format the date to show May, 2010? The closest I can get is May-10 > from the drop down list. > > Thanks, > Malcolm Custom date format: mmmm, yyyy Custom Format mmmm, yyyy Gord Dibben MS Excel MVP On Fri, 28 May 2010 09:05:01 -0700, Malcolm <Malcolm@discussions.microsoft.com> wrote: >Hi, all, >How do I format the date to show May, 2010? The closest I can get is M...

Is away to insert a "formula" into a text?
I'm working on and have been forever a quote sheet. Many of the values change based on certain criteria. I have a disclaimer sentence at the bottom of the page and I would to have it set up that when I change the criteria in the work sheets it would also change it in the sentence. Does this make sense? Marc Use the & (concatenate) operator along with the TEXT funtion. For instance: ="Today is " & text(today()),"Mmmm dd, yyyy"). Inside the text function, you can calculate and format any formula (such as today()) you like. --Bruce "Marc" wrot...

Formula to Get the Max of a column and its definition
Let's say I have two columns... From: Sheet1 Column Z = Product Name Column B = Reject Quantity Problem: Using Sheet2, How can I have the Max value of the Column (from Sheet1) and have its Product Name from Column Z? Remember, th Sheet1 table is modified every now and then so the max value of B migh be different in every minute. Desired End Result: In Sheet2: Reject Quantity Product Name 10 Dent Please help me on this.... Otix -- Shadow_Otix ----------------------------------------------------------------------- Shadow_Otixz's Pr...