Limiting an AVERAGE to a maximum number of values?

I've got a table for which I want to find the average of the right-most
(filled) 12 columns.


To be specific, the data in the table is organized by month, and I want to,
for each given row, see what the average is for the prior year (12 months).


For example, I have it set up that:

Column J represents January 2003 data
Column K represents February 2003 data
Column L represents March 2003 data
And so on...


For each row, how do I average just the last 12 months, bearing in mind that
if less than 12 months are used, it will average JUST those fields.


For example, if (starting with Column J), I have the following amounts
(comma delimited):
5, 5, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 13, 13

I want to get a value of 3 (average of 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 13, and
13 - notice: not including the first two values - 5 and 5- as all I wanted
was the right-most 12 values).

If the values are:
1, 1, 1, 1, 1 (and the rest still blank/not yet filled in)

I want a value of 1 (average of 1, 1, 1, 1, and 1)


I don't mind if I have to use VBA code to do this, as I'm comfortable with
VBA. But if there's a standard way to do it, I'd prefer something built-in
instead of something I'd write myself.


Is there an easy way to do this?


Thanks!


0
4/20/2004 8:35:57 PM
excel 39879 articles. 2 followers. Follow

1 Replies
443 Views

Similar Articles

[PageSpeed] 32

Hi Scott
if your data starts in column J try the following formula for row 1
=AVERAGE(OFFSET($J$1,0,COUNTA($J$1:$IV$1)-1,1,-12))

--
Regards
Frank Kabel
Frankfurt, Germany

"Scott Lyon" <scott.NOSPAM.lyon@NOSPAM.rapistan.NOSPAM.com> schrieb im
Newsbeitrag news:408589b0$1_1@127.0.0.1...
> I've got a table for which I want to find the average of the
right-most
> (filled) 12 columns.
>
>
> To be specific, the data in the table is organized by month, and I
want to,
> for each given row, see what the average is for the prior year (12
months).
>
>
> For example, I have it set up that:
>
> Column J represents January 2003 data
> Column K represents February 2003 data
> Column L represents March 2003 data
> And so on...
>
>
> For each row, how do I average just the last 12 months, bearing in
mind that
> if less than 12 months are used, it will average JUST those fields.
>
>
> For example, if (starting with Column J), I have the following
amounts
> (comma delimited):
> 5, 5, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 13, 13
>
> I want to get a value of 3 (average of 1, 1, 1, 1, 1, 1, 1, 1, 1, 1,
13, and
> 13 - notice: not including the first two values - 5 and 5- as all I
wanted
> was the right-most 12 values).
>
> If the values are:
> 1, 1, 1, 1, 1 (and the rest still blank/not yet filled in)
>
> I want a value of 1 (average of 1, 1, 1, 1, and 1)
>
>
> I don't mind if I have to use VBA code to do this, as I'm comfortable
with
> VBA. But if there's a standard way to do it, I'd prefer something
built-in
> instead of something I'd write myself.
>
>
> Is there an easy way to do this?
>
>
> Thanks!
>
>

0
frank.kabel (11126)
4/20/2004 8:55:14 PM
Reply:

Similar Artilces:

prevent Excel from EVER automatically converting numbers to dates
in Excel v. X for Mac how do I permanently prevent Excel from EVER automatically converting numbers to dates. Formatting individual cells as text will not work in my applications. I need to disable this funtion. Not possible - there's no preference option to turn this off. Instead of preformatting as text, entries can be made with an apostrophe as a prefix: '3/4 You might be able to use an event macro to change it back retroactively, but that depends on knowing what format the user entered it. For instance, 3/4, 3-4, 4Mar, mar4, etc will all be interpreted as 4 Marc...

The number 1 is appened to filename after file is opened
I am trying to find out why the number 1 is added to the file name after it is opened in Excel? I am using Excel from Office 97. It only happens when I open the file from Windows Explorer or from a shortcut on my desktop. It sounds like windows is using New as the default action when you doubleclick on the filename in windows explorer. If you right click on a *.xls file, you'll see a bunch of options--one will be Open and one will be New. Open should be the bold (default) one. I bet you'll see New in bold. If that's the case, you can try this: Close Excel and Windows Start...

Macro for detect palindromes and repeats in letters/numbers string
Dear all, I am looking to detect palindromes (sentence or number or other sequence of units that can be read the same way in either direction) and repeats (sequences of letters or numbers which are repeating atleast twice within a string) in some strings containing between 20-5000 letters. Has somebody any idea how could I perform that using an Excel macro? I would like that the string to be evaluated could be on cell "A1" and that the detected palindromes and repeats could be listed bellow A2 and C2, respectively; and that the number of times that they appear in the sentence could b...

Storage Limit Warnings are not being received by Outlook 98 clients in offline m
Exchange 2000 Server, Windows 98, 2000 and XP users. Office 97 with Outlook 98 Upgrade. I have users in remote offices using "offline mode" to connect to mail server over a vpn. The storage limit warnings are not being received by these users. As a result, they often exceed their limits, effectively cutting off their ability to send and receive email. Help is appreciated in advance. Rick ...

How do I set a cell value based on a formula in another cell?
How do I set a cell value based on a formula in another cell? =IF(D4="P.B.A.",G8,0) ,This works to set the value in the cell the formula is in, say cell A1. I would like to have this formula reside in cell A1 but set the value in cell A2 to (G8,0). Also it would be helpful if I could lock cell A2 based on a False condition from this formula in cell A1. Thanks Gary I note that this entry is also asked and answered in Programming. dingy101 Wrote: > How do I set a cell value based on a formula in another cell? > > > =IF(D4="P.B.A.",G8,0) ,This w...

Add Value of Cells by Color
How do I sum the values of cells with a specific color?? Hi see: http://www.cpearson.com/excel/colors.htm -- Regards Frank Kabel Frankfurt, Germany B2 wrote: > How do I sum the values of cells with a specific color?? ...

Re: Selective Averaging
Frank, Sorry I inadvertently deleted the duplicate A. It should read =AVERAGE(OFFSET(A1,COUNTA(A:A)-5,0,5,1)) -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "Bob Phillips" <bob.phillips@notheretiscali.co.uk> wrote in message news:... > Hi Frank, > > This should do it > > =AVERAGE(OFFSET(A1,COUNT(A)-5,0,5,1)) > > -- > > HTH > > Bob Phillips > ... looking out across Poole Harbour to the Purbecks > (remove nothere from the email address if mail...

Exceeds server limit even though it doesn't have limits
This is a multi-part message in MIME format. ------=_NextPart_000_000F_01C47F9B.2BD786E0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable I have a problem on limiting the size of the message passing thru IMS. I = remove MTA Message Size limits to No limit and IMS Message Size to No = Limit and when users send an email, the system prompts for an = undeliverable message like this: -----Original Message----- From: System Administrator=20 Sent: Thursday, July 29, 2004 9:45 AM To: receipient@domain.com Subject: Undeliverable: RE: Subject Your...

Hiding a column of hyperlinks behind a column of numbers
I have two adjacent columns in Excel 2007: one with numbers, one with hyperlinks. I wish to "place" the column of hyperlinks behind the column of numbers, so if one clicks on the "number" the hyperlink will open. I know how to do this to each individual cell one at a time, but the columns have a couple thousand numbers and hyperlinks. Is there a way I can combine or merge the columns all at once to do what I want? Thank you for any response. Are your hyperlinks the result of the =HYPERLINK() function or are they Inserted hyperlinks?? -- Gary''s Stud...

Copying values between worksheets
This seems a simple problem, but I can't find the answer: I am setting up multiple worksheets (one per month); the closing balance on Jan becomes the opening balance on Feb; if, for example, the opening balance is in cell F38 and the closing one in F42, then in cell F38 in Feb I enter =, then click on F42 in the Jan sheet. However copying across to the other sheets results in all sheets referencing Jan!F42. I understand why sheets can't be treated in a relative way, but is there an easy way of copying this without resorting to VBA? Cheers, ABriggs -- ABriggs -----------------...

Find 2 values closest that are higher
Hi Got an equation nightmare on my hands. So i have 3 columns of data A: State Abbreviate B: Benchmark Number C: New Number I am trying to get an equation that in column D will return the 2 closest values in column B for a value in a cell in Column C. So if column C1 is 100 And column D1:D5 is 101, 115, 98, 5,92 It will find values 101 and 115 and put them in column D. The trick being was that i was hoping it would also return the State Abbreviate for this record. So if Fl is 101 it would return Fl: 101 Any ideas on how to do this??? caleb wrote: > Hi > > Got an equation nightm...

include string or text value in cell reference
Hi, does anyone know how/if i can include a cell value within a path in a function/formula so i can update it by simply changing cell value, e.g. SUM($A$1, '[E:\folder\stats June.xls]'!sheet1$A$1) so that the 'June' part can change according to a cell value, say F1. So if I change F1 from "June" to "July" then the SUM formula would change to show ....'\stats July' . many thanks in advance, Nelly Use the INDIRECT() function -- Gary''s Student "nelly" wrote: > Hi, > > does anyone know how/if i can include a ce...

Condtional format of row depending on string value of $A2 +
Ok, I have a table that already has conditional formatting on columns E:H. My table has a subtotal on it. I am trying to keep a private record of my stock stuff, so heres a snippet of my table: A - H 1 Stk Symbol - other headers 2 ADP 'other info 3 ADP 'other info 4 ADP Total 5 AMAT 'other info 6 AMAT 'other info 7 AMAT 'other info 8 AMAT Total Ok, I have a table that already has conditional formatting on columns E:H. I am trying to do a conditional format for whenever $A1 is greater than...

CEdit maximum length
Hello all, We have a problem setting the maximum length of a CEdit field to 20 bytes. It gets set to 20 characters, which in SBCS is the same as 20 bytes, but in DBCS it is 40 bytes. Can anyone help? Thanks >We have a problem setting the maximum length of a CEdit field to 20 bytes. >It gets set to 20 characters, which in SBCS is the same as 20 bytes, but in >DBCS it is 40 bytes. Can anyone help? This seems a strange requirement - why do you want 20 bytes rather than 20 characters? Remember that non-Unicode is strictly multi-byte characters rather than single byte, so in some en...

Transation sequence number issue
Happy new year everybody!! I have a customer with this situation. his invoice must be sequentially he can't skip a transaction number for his invoices, when a transaction is aborted a number is skiped. i konw that one is not hard to fix but this is the main issue when he do a return he need to have a differnt count numbers than the transactions number for example when he sale something the transation number 1545 is generated but is he return or do a store credit the transaction number is 145 without affectin the secuence os the sale transaction number. can be done? the other probl...

Customer credit limit checking in Project Accounting and Field Ser
Hi there We are using both Project Accounting and Field Services at a site that is very strict about using a customers credit limit. But the problem we have is that when billing from these two modules it does not check against the credit limit as it does in SOP. How can I get it to check the credit limit? Regards Robin You will need to create a Dex customization for this. Best regards, -- MG.- Mariano Gomez, MIS, MCP, PMP Maximum Global Business, LLC http://www.maximumglobalbusiness.com The Dynamics GP Blogster at http://dynamicsgpblosgter.blogspot.com "Robinv" wrote: ...

Merging 2 excel sheets with numbers and words
I started with one sheet (using Excel 2003) but took a copy home (on a flash drive)to work on and added data (both numbers and "X" 's) - using Excel 2007. I saved the work done at home so Excel 2003 could "read" it. Then, I did some work on the copy my computer at work (BIG mistake!!) So, the problem is: I have some data on my flash drive copy which is not on the work computer sheet, and vice versa. I want to merge the 2 copies - so I cannot replace either one because data will be lost. Options??? Way to do this? (It seems like it will be easier to "Co...

Formula to see if a number is allocated
I need to find out if a number is allocated or free. Basically on sheet1 is a load of information. in Column G on this sheet is a unique identifier number. In sheet2 I have a list of numbers say from 1-100 which correspond to the identifier numbers in sheet1. Id like a forumla something like: "If A1 in sheet 2 = any number in column G in sheet1, then print Allocated, else print free" At the moment I have: =IF(A1=Sheet1!G:G,"allocated", "free") But it does not work. What it is doing is If A1 = everything in column G then say allocated, else say fre...

Converting Weekly Data into Monthly Averages
Could someone please show me a formula to average weekly data into monthly. I have a spreadhseet: Date Data 2/1/99 1.15 9/1/99 2.42 16/1/99 1.24 through to..... 25/2/05 3.54 How can i calculate the average of every month between 1999 and 2005? In other words, i need something which looks up all the weeks in a particular month (ie Jan 2001) and averages them out into a table: 1999 2000 ......... 2005 Jan 2.45 6.42 Feb 3.64 etc etc. I was thinking vlookup, but don't know how to average in a vlookup. I would ...

Download limited to 2 years?
The page: http://www.microsoft.com/money/ProductDetails.aspx?pid=002 &active_tab=Compare says that for Money 2007 Plus, the ability to download bank statements will be available for 2 years. (Exact text below.) Money Essentials says 1 year. MS Money can't stop the bank's Web site from generating an OFX or QIF file, but of course the product could refuse to import the file 2 years after activation. I have been downloading OFX files from my bank, into MS Money 2003 for about 5 years now. Question: If I upgraded to Money 2007 or 2008, would I be forced to upgrade again...

Combining Worksheet Numbers
And old problem revisited. I brought this question to this board about a year ago and got two spectacular answers from Tom Ogilvy and Ken Wright. See previous thread ---> http://tinyurl.com/2yleu I will repeat my question again just in case Anyway, I have the same problem/question, but this time instead of all of the worksheets having a static set of values (names), the players will be changing positions from "season" to "season." Thus, the Start to finish thing doesn't seem to work. I am doing a career statistical page for an online soccer simulation. I want to...

How to join auto number and letters
Hi, I have a form for issuing a letter number for our letter book. In this form , I have a text box with auto number . what I need is to issue auto number start with letters. For example: APP&P1, which 1 is the auto number and it is start with “APP&P”. once the user complete the letter subject, letter receive and the sender , will receive a control number or letter number. What about having two fields, where the first field contains the letter prefix and the second field contains the number? You could display the two fields in a calculated control combining the data. "...

put numbers back on a calendar template after removing shading?
O n the Event Schedule Planner template, when I remove shading from the calendar the numbers are removed also. It won't let me put them back. When I use "no fill" it removes the numbers too. Exactly what template are you using? What version Publisher? -- Mary Sauer MSFT MVP http://office.microsoft.com/ http://msauer.mvps.org/ news://msnews.microsoft.com "dfarnelli" <dfarnelli@discussions.microsoft.com> wrote in message news:162D36F1-E593-4EB2-B0A2-F769CBE0C1C0@microsoft.com... > O > n the Event Schedule Planner template, when I remove shading from the...

Limit to # of shapes in "My Shapes" folder?
I got a new computer from my corporation with Visio 2003 installed. I copied the VNE folder from my old computer to new computer into the "My Shapes" folder and all of the stencils do NOT appear in the pick list. Is there an upper limit on the # of files that can be in the "My Shapes" folder? (The VNE directory from my old computer had approx 1100 stencils). Also opening the "My Shapes" dialog box is REALLY slow -- anyone know how to fix that as well? Thanks in advance. I'm not sure if there is a hard limit or just a performance limit on the number of...

copy rows with values
Hi First step is no problem: in Sheet1 col AI this formula is inserted with VB to add age groups =OM(OCH(Age>=0;Age<7);"a. 0-6";OM(OCH(Age>6;Age<16);"b. 7-15";OM(OCH(Age>15;Age<26);"c. 16-25";OM(OCH(Age>25);"d. 26-";"")))) Col is autofilled to last used row. In next step I loop thru these rows to find certain rows and copy them to Sheet2. Problem is that formula on each row is then copied. Instead I need it to be values only! Needs to work in Excel 2003 and 2007. Sheet1 can have >20 000 rows Any s...