Average of absolute values of moving ranges

I'm trying to get the average of the absolute values of a set of data over 8 
weeks. Each week is on a seaparate sheet so to capture the moving ranges I've 
been using the formula below to get my result. Is there an easier way?

=AVERAGE(ABS('Week 1'!G2-'Week 2'!G2),ABS('Week 2'!G2-'Week 3'!G2),ABS('Week 
3'!G2-'Week 4'!G2),ABS('Week 4'!G2-'Week 5'!G2),ABS('Week 5'!G2-'Week 
6'!G2),ABS('Week 6'!G2-'Week 7'!G2),ABS('Week 7'!G2-'Week 8'!G2))

Thanks!
Amy
0
Utf
2/22/2010 4:21:01 PM
excel.misc 78881 articles. 5 followers. Follow

1 Replies
1214 Views

Similar Articles

[PageSpeed] 12

The use of the ABS() throws a kink into it.  I'd consider adding a 'helper 
cell' on each of the sheets with the formula =ABS(G2) in it.  For the example 
below, we'll say that's in cell G3

If the weekly sheets are contiguous, then you can reference the first and 
last sheets in the group to get the average, as:
=AVERAGE('Week 1:Week 8'!G3)
then a simple change would roll it forward to the next period:
=AVERAGE('Week 2:Week 9'!G3)

"Amy" wrote:

> I'm trying to get the average of the absolute values of a set of data over 8 
> weeks. Each week is on a seaparate sheet so to capture the moving ranges I've 
> been using the formula below to get my result. Is there an easier way?
> 
> =AVERAGE(ABS('Week 1'!G2-'Week 2'!G2),ABS('Week 2'!G2-'Week 3'!G2),ABS('Week 
> 3'!G2-'Week 4'!G2),ABS('Week 4'!G2-'Week 5'!G2),ABS('Week 5'!G2-'Week 
> 6'!G2),ABS('Week 6'!G2-'Week 7'!G2),ABS('Week 7'!G2-'Week 8'!G2))
> 
> Thanks!
> Amy
0
Utf
2/22/2010 4:31:01 PM
Reply:

Similar Artilces:

Can I abbreviate one value in a data series?
I've got a chart where one value (8,300) greatly exceeds all the others. Is there a way to abbreviate this value so the other data points show better in the graph? Hi, One way is to break the Y axis, have a look at these examples of how to http://peltiertech.com/Excel/Charts/BrokenYAxis.html http://tushar-mehta.com/excel/newsgroups/broken_y_axis/tutorial/index.html http://www.andypope.info/charts/brokencolumn.htm Cheers Andy CMEknit wrote: > I've got a chart where one value (8,300) greatly exceeds all the others. Is > there a way to abbreviate this value so the other da...

Moving from Home & Student to Full version of Office
Version: 2008 Operating System: Mac OS X 10.5 (Leopard) Processor: Intel I need Exchange support and therefore I am planning to buy the Full version of MS Office 2008 for Mac. I already purchased and installed (and have been using) MS Office 2008 Home & Student edition. My questions are: 1. Should I uninstall the Home & Student edition before installing the Full version? (I assume so). 2. How do I make sure that the data I have already created in Office 2008 Home & Student edition will be available to me after installing the Full version? In particular, how do I make sure that ...

Return values that sum to a known value
I have a list of data and would like to know if there is a formula that would return any items from that list that sum to a known value. Have a look at this thread for something similar: http://www.microsoft.com/office/community/en-us/default.mspx?pg=7&cat=&lang=en&cr=US&guid=&sloc=en-us&dg=microsoft.public.excel.worksheet.functions&fltr= Regards, Tom "lmattern" wrote: > I have a list of data and would like to know if there is a formula that would > return any items from that list that sum to a known value. ...

Copy/paste range of rows between 2 dates...
Hi! I have a sheet called data which act as a database. The column A has the dates. In order to create customized chart in a userform, for different range of data(i.e from column D, G and M...), I'd like to select a range of rows that are between 2 dates and create the charts accordingly. Or copy to range to another sheet and then create the charts. I am not so advanced in VBA and any help would be greatly appreciated. Thanks! Greg ** Posted via: http://www.ozgrid.com Excel Templates, Training, Add-ins & Business Software Galore! Free Excel Forum http://www.ozgrid.com/forum *** Hi ...

Selecting cell value for a sum, based on a condition
Trying to come up with a formula or method that will enable me to sum values based on a condition. For example, I have three columns which contain a condition and two amounts. If the condition is of the 'each' variety, one value will be used in the sum. If the condition is of the "square foot" variety, another value will be used. Here is a small diagram that may help visualize this: A B C D 1 Measure Unit Cost S.F. Cost Summed Total 2 Each 3.00 .30 3 S.F....

absolutely cannot resolve timeout error
OL 2002 (10.6515.6626) SP3 Win XP HE Follow-up to: *microsoft.public.outlook* (this message is cross-posted to the newsgroups that appear in the headers. To reply, just click "Reply" in news client and it will go to the follow-up newsgroup *microsoft.public.outlook*, where u can follow the thread. Thanks.) Hi, I am constantly receiving error message: Task 'smtp server name - Sending and Receiving' reported error (0x8004210A): 'The operation timed out waiting for a response from the receiving (POP) server. If you continue to receive this message, contact your s...

How do I make X-values of a chart dependent on values in cells?
Greetings. I have a chart which can go from x-value 0 to x-value 200. However I'd like to be able to input min X-value into a cell, and a max X-valu into a cell, and the x-value in the chart changes to reflect that. Is it possible to do that? Thanks for any replies. K -- Message posted from http://www.ExcelForum.com Hi, There is no automatic way to do this but take a look a Tushar's AutoChart Manager for a possible solution. (http://www.tushar-mehta.com/) Cheers Andy Kashgarinn < wrote: > Greetings. > > I have a chart which can go from x-value 0 to x-value 200...

Users cannot login to OWA after Exchange 2K3 was moved into different OU
I created a new OU named member servers, and moved the exchange 2k3 server into it. After I moved it, only the administrator was able to login to OWA successfully. No users could login to OWA. I created a test account with a mailbox, and was able to login to OWA with the newly created test account. I then moved the exchange 2k3 server from the member servers OU into the computers OU, and the newly created account was unable to login to OWA. Any help with this would be appreciated. ...

set value of a group of activex control points
Have a spreadsheet that has some 20+ activex control points (option buttons). Is there a way to group all these controls together & set their initial values the same? Trying to setup a "reset" type of operation that would clear all control points. I can do them individually via properties, but it's too time consuming. Any suggestions? ...

Moving certain data to different sheet
I need to move data that meets a certain criteria, to another sheet within a workbook. For instance, if a column of data is for a certain ZIP code area, I need it to automatically copy to a sheet for that city. Say, 40202 would go to the Louisville, KY sheet. Because Louisville has multiple ZIPs, I would need only the data that begins with 402 to go to that sheet. Lexington KY's data, which begins with ZIP code 405, would go to its own sheet. Macro? Formula? Thanks! This can definitely not be created with a formula. I suggest that you make use of the macros. Rgrds, Kris...

Pie Chart Plot Area Moves
After printing a pie chart, the plot area relocates itself. I wrote a procedure to bring it back to the designated "top" and "left" coordinates the next time the sheet is activated, but the plot area doesn't relocate until the sheet is activated a SECOND time. Does anyone know how to fix? Thank you. ...

Show picture outline only when moving in PowerPoint
It's probably simple when you know it, but I'm sure there's a setting that I can toggle, so when I move a large picture in PowerPoint, only the outline shows, not the whole picture. This is because the pictures I'm inserting need to be of good quality, and my laptop struggles with them when I move them. ...

You cannot copy or move messages to public folders
I have a few users who get the following error when trying to move messages from their inbox into a public folder - "Can't move the items. You do not have sufficient permission to perform this operation on this object. See the folder contact or your system administrator" I have found the following on microsofts website and have made those changes but it has not solved the issue http://support.microsoft.com/?kbid=330752 has anyone seen this before? I have also downloaded outspy and run this on messages that have failed and on messages that are ok. However im not really su...

Move server between Admin Groups #2
We are going to be going through a reorganization soon and will need to move some exchange servers from one admin group to another. I know there is not a native way in Exchange 2003 to do this. Does anyone have ideas on how to accomplish this besides a swing server to the new Admin Group? Third party Software? Our environment is 2003 Forest functional and domain level. Exchange 2003 mixed mode. On Tue, 3 Jan 2006 14:16:23 -0500, "Tim Kalligonis" <tkalligonis@comcast.net> wrote: >We are going to be going through a reorganization soon and will need to move >so...

Altering the range that is plotted by a chart via VBA
Good morning all. I haven't really done a huge amount on charting, so I'm sure you'll think that these questions are a bit simplistic, but here goes! I plot a chart from a worksheet that contains three columns of data - the X axis contains week numbers, and is in B29:B54. Actual hours are entered in F29:F24 and cumualtive hours in G29:G24. The cumulative formula is of the form: =SUM($F$29:F29) So, each week, in order to plot another week's data, I have to copy this cumulative formula down by one cell, which, considering I have a lot of these reports, is a bit time cons...

Storing distinct values in an array
Hello .. in the speadsheet, i have a column containing a series of numbers maybe of them repeated multiple times... i would like to store all distinct values in an array any ideas on how i could do that ... eg , if these were the numbers going down column then 1 3 5 7 5 3 5 7 5 4 3 4 5 7 8 5 3 .. store 1,3,5,7,9,4,8 in an array, in no specifi order thank you shimee -- shimee ----------------------------------------------------------------------- shimeel's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1583 View this thread: http://www.excelforum.com/showt...

how do i move my taskbar back to bottom of my screen?
my Start button and taskbar shifted to the right side of my screen, how do i move it back to the bottom of my screen? KrisE wrote: > my Start button and taskbar shifted to the right side of my screen, > how do i move it back to the bottom of my screen? Drag them back to where you want them. Note - this is a Microsoft Outlook group. Your question would be best posted in one for your OS. "=?Utf-8?B?S3Jpc0U=?=" <KrisE@discussions.microsoft.com> wrote in news:65D28727-E334-4D05-ABAA-37F0B9CAEC66@microsoft.com: > my Start button and taskbar shifted to the right sid...

Populate cell with a value if another cell has a certain value
All, good morning. i have a issue, i need to populate cell E4 with a value CX/025966 when cell F4 has a text Bond Street. Is there a simple way of doin this? EXCEL 2007 Try:- =IF((F4="Bond Street"),"CX/025966","") If my comments have helped please hit Yes. Thanks. "B2ORL" wrote: > All, good morning. i have a issue, i need to populate cell E4 with a value > CX/025966 when cell F4 has a text Bond Street. > > Is there a simple way of doin this? Use IF() =IF(F4="Bond Street","CX/02...

Show zero values
When a formula returns a zero value, the zero value is not being displayed. I have reviewed the following: - conditional formatting - zero values are checked (Tools/Options/View) - stepped through my VB code (it inserts formulae based on a Worksheet Change event) - locked and hidden values unchecked with and without protection (this should have no effect when the sheet is unprotected) Are there any other circumstances that result in a zero value not being displayed? Are there any other reasons for this occurrence? Cheers John Check also normal cell formatting! there are formats that hide ...

Moving Exchange 5.5 #2
I am having problems with my hardware and want to move Exchange onto another box. We currently have Exchange 5.5 sp4 running on Windows NT. I want to buy a new server and run Exchange 5.5 sp4 on Windows 2k. They will not let me upgrade the version of Exchange. Are there any white sheets or any links that could be suggested to handle this migration from an NT server to a W2k server? best way to do this is to bring up your new server, joining the site, then move all the mailboxes, and replicate all public folders...then see this article about removing the first server... http://support.microsof...

Conditional formatting a date range
If I have a column of dates that are manual entered what is the formula to conditionally format them based on a date range of three months before the current date to the current date and another three months after the current date to the current date? Assume the dates are in column A, starting with A1. Highlight all the dates, with A1 as the active cell, and click on Format | Conditional Formatting. In the dialogue box you should select Formula Is rather than Cell Value Is and then enter this formula: =3DAND(A1>=3DTODAY()-91,A1<=3DTODAY()) Click on the Format button and choose the ...

Want to modify this procedure to have a range of periods rather than a single period
This is a multi-part message in MIME format. ------=_NextPart_000_00BF_01CB206B.89E3AC60 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable I have the procedure below I want simply modify the periods procedure below to be -- perpost >=3D = '201001' and <=3D '201012' so I want a range of periods rather than a = single period as in the example below I would really appreciate it if someone can help me out on this. Thanks=20 Sammy USE DEMOAPP Declare @perpost nvarchar(8) /* Do not edit Above=20 ...

CButton::GetButtonStyle()
hi! is it possible that the function GetButtonStyle() returns one single style value? where can i get information when to use a mask and when to use the plain value? how about other controls/windows? actually i am a little bit confused about this... thanks, philipp "Philipp Huber" <philipp.huber@innval.com> wrote in message news:bonpkq$7hi$04$1@news.t-online.com... > hi! > > is it possible that the function GetButtonStyle() returns one single style > value? I'm not sure what you're asking, but GetButtonStyle returns the LOBYTE of the GetWindowLong/GW...

Comparing values #2
I have two sheets in a workbook. The first column in each sheet has part numbers. I would like to compare worksheet1 column a to worksheet 2 column a and identify duplicate values. I have not been able to make this work. -- Dirk Chip Pearson has lots of ways to work with duplicates/uniques at: http://www.cpearson.com/excel/duplicat.htm Dirk_Bob wrote: > > I have two sheets in a workbook. The first column in each sheet has part > numbers. > I would like to compare worksheet1 column a to worksheet 2 column a and > identify duplicate values. I have not been able to make...

Return address of first item in a range
I'm sure there must be a simple solution for this I cannot think of. I am looking for a formula that will return the value of the first item in an input range. For example, I want to put a formula in cell A10 that will return the first item that is input in the range Range A1:A9 (not nesessarily the first cell). If A4=200, A7=150 and all other cells are empty, the formula should return 200 (the first item in the column). Thanks for your assistance. Ashley Ashley, One way is to use the array formula (entered with Ctrl-Shift-Enter): =INDIRECT("A"&MIN(IF(A1:A9<...