Moving Average Formula Problem

I have a workbook with a date on worksheet 1 that indicates data has
been entered for that month.

On worksheet 2 is are columns of numbers with an average for each
column at the bottom.  I would like the average to use the date on
worksheet 1 to calculate the average.

The start of the range is known, but the end of the range is found only
by looking up the date on worksheet 1.

I have tried to use something like:  =AVERAGE(B10:ADDRESS(VLOOKUP(date,
range, 1, TRUE).  This doesn't work and I can't find the functions
needed to make it successful.

Thanks in advance for any help!

0
Bob
3/21/2006 10:20:57 PM
excel 39879 articles. 2 followers. Follow

3 Replies
536 Views

Similar Articles

[PageSpeed] 55

Needs more detail
best wishes
-- 
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

<Bob@1800-mail.com> wrote in message 
news:1142979657.771734.154110@i40g2000cwc.googlegroups.com...
>I have a workbook with a date on worksheet 1 that indicates data has
> been entered for that month.
>
> On worksheet 2 is are columns of numbers with an average for each
> column at the bottom.  I would like the average to use the date on
> worksheet 1 to calculate the average.
>
> The start of the range is known, but the end of the range is found only
> by looking up the date on worksheet 1.
>
> I have tried to use something like:  =AVERAGE(B10:ADDRESS(VLOOKUP(date,
> range, 1, TRUE).  This doesn't work and I can't find the functions
> needed to make it successful.
>
> Thanks in advance for any help!
> 


0
bliengme5824 (3040)
3/21/2006 10:39:48 PM
Hello, Bob,
I suggest you use the DAVERAGE function, which lets you retrieve the
average of certain numbers, based on given criteria.

For example, I set up a spreadsheet like this:
a1 contains "date"
b1 contains "num"
a2 contains "<1/10/2006"
a3 contains "date"
b3 contains "num"
a4 through a17 have dates 1/1/2006 through 1/14/2006.
b4 through b17 have numbers that I want to average.

A 19 contains the formula =DAVERAGE(A3:B17,"num",A1:B2)
which means "Give me the average of the num column where the condition
A1:B2 holds. (The condition is date <1/10/2006). 

Hope that helps.

0
3/21/2006 11:07:03 PM
Another option is to use a dynamic range.  A dynamic range is a named
range that uses the offset formula.  So you create a named range that
uses this formula to determine the range:

=Offset($B$10,0,0,VLOOKUP(date, range, 1, TRUE), 1)

Let's say we named the range Bob.  Then your average formula would be:

=Average(Bob)

This assumes that you are average contiguous cells.  If they are not
all right next to each other then you will need to use Rajah's advice
on DAVERAGE or look into array formulas.

0
kletcho (34)
3/21/2006 11:52:39 PM
Reply:

Similar Artilces:

Formulas #14
I have dollar amounts in cells A1, B1, C1 and D1. I need a formula in cell E1 to give me the sum of A1+B1 or the sum of C1-D1 if the sum of A1+B1 = 0. I have over 5000 rows. Thanks. "Rumy" <Rumy@discussions.microsoft.com> wrote: > I have dollar amounts in cells A1, B1, C1 and D1. I need a formula in cell > E1 to give me the sum of A1+B1 or the sum of C1-D1 if the sum of A1+B1 = > 0. > I have over 5000 rows. =IF(A1+B1=0, C1-D1, A1+B1) Caveat: If A1 and/or B1 does not contain constants, or if they have opposite signs, their sum might appear to be ze...

Calendar appointments dissapearing (Auto Accept Agent problem?)
I'm having a wierd issue where a meeting organizer will not me able to see a meeting they booked on their calendar but the meeting will still show up on the attendee's calendars. If I look at the meeting from the attendee calendar the organizer still shows up. Sometimes the meeting will stay for the organizer but they can't modify the meeting (no tracking tab, acts like they are an attendee instead of the organizer). This doesn't happen every time, in fact it happens a small amout of the time. We have the auto accept agent running and the organizers list the conference room...

Moving Servers Across Administrative Groups...
Here's the quick scenario... Exchange 5.5 original site. Exchange 2000 (non-cluster) brought up as first Exchange 2000 server in 5.5 site. (not doing anything) Exchange 2000 cluster brought up. Exchange site name is XXXNTDOMAIN which, in ESM shows up as the name of the First Administrative Group. This doesn't match the logical environment going forward so I was curious if it was possible to do: 1. Rename the XXXNTDOMAIN administrative group to something else? 2. Create a second administrative group and bring up a 2003 cluster at some point in the near future and migrate ...

Formula result shows as zero Excel 2003
Formula as shown =IF(C12<>"",+C11-C12,"") appears in D12. This is the same formula as rows above but rows above show correct result whereas D12 and subsequent rows show only zero. If I do an F2 and F9, the correct result shows in the Formula Editing bar so formula is working correctly. I have tried copying both formula and cell formatting from previous rows which do display their result correctly but still doesn't fix the problem. Any assistance would be much appreciated Hi maybe automatic calculation is disabled. Check 'Tools - Options - Calculate...

New Problem
In 2007: In a form I created a button, used the wizard to set it to go to next record when clicked. When I click it, I get this: The expression ON CLICK you entered as the event property setting produced the following error: Ambiguous name detected: PRINT_CLICK. Now, if I wrote the VBA/Macro, I could understand it not working if I made a mistake, but this button was generated by ACCESS and doesn't work. Same problem with Previous record button and Save Record button. Thanks in advance, Vito On 13 May 2010 16:00:59 GMT, Vito wrote: > In 2007: > > In ...

Conditional Formula based on previous date + 30
I have a spread sheet that caluclates when proposals expire. Date Sent Follow Up date January 13, 2010 February 12, 2010 What I am looking for is for the Follow up date to turn RED when the date is expired (over the date listed). Just use CF with a formula of =B2>TODAY() -- HTH Bob "Chris" <Chris@discussions.microsoft.com> wrote in message news:66DE65F1-F041-434C-86A7-B13635C6914F@microsoft.com... >I have a spread sheet that calu...

Authentication mode
Hi: Currently I have installed the MS CRM 1.2 Server on a server that host another application(sharepoint portal server & project server), this application requires SQL Authentication for work,but CRM requieres Windows Authentication Mode; at least the CRM implementacion guide document recommends this type of authentication, is there any way for work with both applications on same server ?. Microsoft CRM can work with SQL in mix mode authentication. Frank Lee Workopia, Inc. http://www.workopia.com/Links.htm San Francisco, CA "Rafael Diaz" wrote: > Hi: > >...

how can I move the e-mail accounts from outlook 2002 to an other computer
how can I move the e-mail accunts from outlook 2002 to an other computer same program?? ...

Problems with Access 2000 reports in Access 2003
Hi, we have a sent out a 2000 format access database to a client running Access 2003. When trying to open any of the reports they get an error - "the openreport action was cancelled" - Not sure if they get this by running the report from the database window or if it's only when done through a form (buttons that run docmd.openreport when pressed). Is anyone aware of any issues that may be causing this? I couldn't find anything elsewhere on the MS site. Thanks - Angie P.S. the reports work fine in 2000 and XP. ...

URGENT!!! Problem with row data being truncated in a copy worksheet sub #2
Dave, Thanks for responding. I tried this but I could not get it t work in conjunction with the entire module. It dies right afte copying and PasteSpecial Values It does not kill the temp file or loa the newly created sheet into an e-mail. Any Ideas -- Doctor ----------------------------------------------------------------------- DoctorV's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=514 View this thread: http://www.excelforum.com/showthread.php?threadid=26863 You may want to post your current procedure. DoctorV wrote: > > Dave, Thanks for respon...

Tricky Formula.. Please Help
I have created a time roster.. easy in each day I have start,end and break deduction.. still no probs I need to create an output formula for hours worked after a 16:00 from the start and end range on a day. So in the roster it will still display normal hours, however I will create a field for hours after 16:00. this is for calculating a different wage rate. Thanks Aaron See response in .programming -- HTH RP (remove nothere from the email address if mailing direct) "Aaron H" <aaron@istarnetworks.com.au> wrote in message news:uU96kXI$EHA.2076@TK2MSFTNGP15.phx.gbl... ...

Match formula to match values in multiple columns
Hi all, does any friend know that how can I make below formula work MATCH(A2,$K$2:$M$30,0) I am not sure I have understood correectly. Please go through the below example With data as below if you need to retrive the name of the 1st Rank holder from London. D2 = 1 D3 = London In D4 apply the below formula =INDEX($B$2:$B$9,MATCH(1,($A$2:$A$9=D2)*($C$2:$C$9=D3),0)) Please note that this is an array formula. You create array formulas in the same way that you create other formulas, except you press CTRL+SHIFT+ENTER to enter the formula. If successful in 'Formula Bar'...

OFFSET problems in dynamic range
Hi, I'm having the strangest problem with the OFFSET function. I have 5 dynamic ranges in my worksheet. Aimline =OFFSET('Weekly ORF'!$F$4,1,0,COUNTA('Weekly ORF'$F:$F)-1,1) Sessions =OFFSET ('Weekly ORF'!$D$4,1,0,COUNTA('Weekly ORF'$E:$E)-1,1) WCPM =OFFSET ('Weekly ORF'!$G$4,1,0,COUNTA('Weekly ORF'$G:$G)-1,1) Date =OFFSET ('Weekly ORF'!Aimline,0,-1) Date2 =OFFSET ('Weekly ORF'!WCPM,0,-1) The problem is that the two names for Date and Date2 keep reverting to =OFFSET ('Excel Template.xls'!Aimline,0,-1) and =OFFSET (&#...

OnGetMinMaxInfo problem
Hi, I added an OnMinMaxInfo() function to my dialog so i can set the minimum tracking width and height for my dialog, I added this prototype to my dialog class: afx_msg void OnGetMinMaxInfo(MINMAXINFO* lpMMI); Then I added this function to set the min and max values: void CMessDlg::OnGetMinMaxInfo(MINMAXINFO* lpMMI) { POINT min; min.x = 200; min.y = 200; lpMMI->ptMinTrackSize = min; CDialog::OnGetMinMaxInfo(lpMMI); } It compiles but i can still resize the dialog to a smaller width/height, Any help would be apreciated :) Thx in advance, Thomas "Thomas" <Da_QuiK@fake...

Urgent Question Please Help: Credit Card Setup Problem
We have an urgent question regarding credit card setup/cash receipts/bank rec We understand that in Great Plains when setting up a Credit Card to be used by a Customer, it must be setup as a "Bank Card", not a "Charge Card", in order for the transaction to hit Bank Rec. However, we mistakenly setup all the Credit Cards as "Charge Cards", therefore after posting Credit Card Cash Receipt transactions in Receivables, the transaction does not appear in Bank Rec for us to perform Bank Deposits. Please Please Help!!! Is there any way to "fix" those pos...

Problem running MS CRM 3.0 over SSL (https)
I have setup an install of Microsoft CRM 3.0 on a high spec Windows 2003 Small Business Server and it all worked happily. I then generated an SSL certificate and applied it to the site in IIS. Initially when you load CRM (using the web client) it loads the left hand menu and navigation panes and shows the top menus and the user which is logged in however to main content area remains blank. After a time out period the error "An error has occurred. For more information, contact your system administrator" appears. The strange thing is you can navigate the menus etc and even edit...

Average speed #2
Hi all Having trouble working out average speed. miles in a1 =14.3 time in B1 = 0:50:23 this cell has been formatted to HH:MM:SS The formula A1/B1 brings back the wrong answer if I alter to A1/(B1*24) still does not work correctly. Advise please. Thanks Jon "jon" <jon@nospam.com> schrieb im Newsbeitrag news:4659eb9a$1_1@glkas0286.greenlnk.net... > Hi all > Having trouble working out average speed. > > miles in a1 =14.3 > time in B1 = 0:50:23 this cell has been formatted to HH:MM:SS > > The formula A1/B1 brings back the wrong answer if I al...

Question about formula or marco
Hi all, Here is what I need to do: Column B will have a list of first names (John), Column C will have last names (Smith). What kind of formula or macro do I need so that I can have Column A read "jsmith" (all lower case)? Any ideas? Thanks very much. Try this formula in Cell A1:- =LOWER(CONCATENATE(LEFT(B1),C1)) "Gus Jae" <gusjae@verizon.net> wrote in message news:dn8Ue.186$sk2.153@trndny03... > Hi all, > Here is what I need to do: > Column B will have a list of first names (John), Column C will have last > names (Smith). > What kind of formu...

How do you copy a cell's content verses it's formula?
I have 2 cells and combined them into a third cell with the following formula... =a1&" "&b1. I was combining a person's first name (cell 1) with a person's second name (cell 2) so cell 3 included the first and second name. Now I want to copy and paste cell 3, but it copies the formula... I need to paste in the content (first and second name) not the formula. Hi Tammy, You could use a macro see JOIN macro on it's page http://www.mvps.org/dmcritchie/excel/join.htm not what you actually asked because if would change column A with the concatenated const...

moving publisher #2
I bought a computer with publisher bundled (no cd). Now I want to put it on my new laptop. How do I move it from the desktop computer to the laptop???? Cliff without the source CD...you don't. -- ~~~~~~~~~~~~~~~~~~ Rob Giordano Microsoft MVP Expression "Cliff" <Cliff@discussions.microsoft.com> wrote in message news:336C1CBE-2A51-4B62-B83D-46C35C557B74@microsoft.com... > I bought a computer with publisher bundled (no cd). Now I want to put it > on > my new laptop. How do I move it from the desktop computer to the > laptop???? > > Cliff I...

Moving a row from one sheet to another wrongly leaves blank-row artifact.
Moving an entire row within a sheet in Excel XP is implemented correctly: Select the entire row, cut it, go to the target location, and Insert Copied Cells. The target row is inserted (pushing all rows below it down), and the source row is deleted (bringing all rows below it up, as it were). NOT so when the target is in another sheet in the same workbook. Excel inserts the target row correctly, but fails to delete the source row. The row still exists (only it's blanked out). You have to remeber to, tediously, return to the source and delete the blank row. Which means that if you intende...

Import plain text with formulas into Excel
I'm having some trouble in Excel. If I create a plain text file like the following: 1,2,3 4,5,6 =sum(a1:a2), =sum(b1:b2) Nothing can be done as if in spanish is (SI), iserror is (ESERROR), and so on. This really sucks as I can't just install other software on the server nor make other modifications. I don't know what Microsoft was thinking when they translated the versions; any Excel version should accept it's language commands and English commands, but well, that's too much to ask for... Any good sugestions? Regards -- schmiedel ---------------------------------...

WindowsCE problems
I have some problems building application for Pocket PC (MDA1), maybe someone could help me. I have these two problems: -I need to create modal dialog box, but after creation sometimes I need to access it (in response to system calls) and show some messages... what is the best way to access modal dialog box while it's running? -is there a way to change colors of single items in list-view control ? Currently, when I'm using SetTextColor before each InsertItem, color changes but for all items in list-view (CListCtrl) :\ Is there way make this work? I'm using Microsoft eMbedded Visu...

Draft Folder problem #2
I'm running outlook 2000. My draft folder is called draft1. Can't seem to rename it! There isn't a folder named draft so what's happening? Isn't a big problem, but I should be able to solve it and I can't. Help. Thanks in advance Peter You could try to start Outlook from the run line with the /resetfoldernames switch. Start-->Run outlook.exe /resetfoldernames Reference: http://office.microsoft.com/en-gb/assistance/HP010031101033.aspx "Peter Brown" <peter.jennifer@gmail.com> wrote in message news:TErag.449$TF.2645@news-1.opaltelecom.net... &...

Formula auditing on protected sheets
Simple question: is there a easy (or difficult?) way to track precedent and dependent cells on sheets which are protected? Thanks, Andrew ...