Hold a cell range for an "average formula"

I add a column every week to my work sheet and want the formula to always 
calculate the current 5 column range.


0
Utf
5/28/2010 8:30:01 PM
excel.worksheet.functions 4936 articles. 2 followers. Follow

2 Replies
766 Views

Similar Articles

[PageSpeed] 22

Wendy Akers wrote:
> I add a column every week to my work sheet and want the formula to always 
> calculate the current 5 column range.
> 
> 

Add a column WHERE?

What "formula"?

Where is the "current 5 column range"?
0
Glenn
5/28/2010 8:45:38 PM
Hi,

Let's say your data is in range C3:H3.  In cell K3, enter the following 
array formula (Ctrl+Shift+Enter) to get the average

=AVERAGE(OFFSET(INDIRECT(ADDRESS(3,MAX(ISNUMBER(C3:J3)*COLUMN(C3:J3))+1)),0,-5,1,5))

-- 
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"Wendy Akers" <Wendy Akers@discussions.microsoft.com> wrote in message 
news:6BB10794-0B64-4768-B2F8-F5234474698F@microsoft.com...
> I add a column every week to my work sheet and want the formula to always
> calculate the current 5 column range.
>
> 
0
Ashish
5/28/2010 11:14:43 PM
Reply:

Similar Artilces:

Need Help on Average formula!!!
This is what i have Z5:Z65=Different Names Of People AA5:AA65=If (L) is entered =Laidoff AE5:AE65= Hours worked for each name in cells Z5:Z65 In AB5:AB65 I want to Average hours of AE5:AE65 If an L is entered for each name & hours in AA5:AA65 & If there is no L entered I need a zero value in cells AB5:AB65 If L is in aa5 do you wish to include the hours worked by that person? The way you describe it will never change the average. If I underrstand correctly. So far I have =IF(AA5="L",AVERAGE($AE$5:$AE$65),0) "Mike" wrote: > This is wha...

Average formula #2
I need a formula that will figure the average of any range of numbers dropping the lowest number Thomas Goldstein One way: =(SUM(rng)-MIN(rng))/(COUNT(rng)-1) This assumes that you mean to drop only one value if there are multiple instances of the "lowest number". In article <k_OAe.4091$IU1.2057@fe11.lga>, "thomas wald" <thomaswald@microsoft.com> wrote: > I need a formula that will figure the average of any range of numbers > dropping the lowest number > > Thomas Goldstein Or an array formula to exclude all instances of the minimum ...

average formula
what is the formula for calculating a rolling average Hi see your post in Excel.misc P.S.: please don't multipost -- Regards Frank Kabel Frankfurt, Germany tbone wrote: > what is the formula for calculating a rolling average ...

Need help with AVERAGE formula
With Frank's help, am trying a new formula to average a field of 13 numbers, 9 of which contain zeros that I want the system to ignore, and 4 of which have numbers. It's not working, as I'm getting the #value! error message instead of my average. Here's my current formula, what's wrong =AVERAGE(IF(D6:D18<>0,D6:D18) Thanks in advance for your assistance! Please email me at work (jessica.w@zimshipping.com) if you have more info for me Jessica Hi Jessica! It's an array formula. Enter the formula by pressing and holding down Ctrl + Shift and then pressing Ent...

Average formula #6
What formula do I use to calculate a weekly average as the monthly tota changes? Example: july total value divided by 28weeks, august value divided b 32 weeks, sept value divided by 36 weeks and so on In other words, a weekly average as each month ends and the value i entered I hope someone understands this and can help thanks so much lesli -- onyx481 ----------------------------------------------------------------------- onyx4813's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=2630 View this thread: http://www.excelforum.com/showthread.php?threadid=47137 As...

average formula #4
Hi all, How to get the real average, when you have cells containing formula returning zero? I have tried "AVERAGE" but that don't work when I have formulas in th cells that I want to get average. Example: Without formula in A1:A5 A1 A2 A3 A4 A5 10 10 10= 10 average (right) With formula in A1:A5 A1 A2 A3 A4 A5 10 10 10= 6 average (wrong) I want that also to be 10 as average value. What to be done? Any idea's? //Thoma -- Message posted from http://www.ExcelForum.com AVERAGE(A1:A5) equals SUM(A1:A5)/COUNT(A1:A5) i.e. f...

I Need Help with Average Formula I Need Assistance!!!
This is what i am trying to use but get Value or False errors & also tried as an array =IF(AR6="L",OR(AW6>""),AVERAGE(IF(AU$6:AU$66>=0,AU$6:AU$66),AU6,"")) This is what i need if AR6=L or AW6>"" then have to Average AU6:AU66 IF AR6="" OR AW6="" I need to use value in AU6 then if cells are blank use "" Mike, I'm not 100% sure of what you are asking for, but I will point out corrections that I see: 1) your OR argument is out of order. Try something like this: =IF(OR(AR6="L",AW6...

Average formular
Hello, I am trying to work out the average turn over for the year of a supermarket. I have =Average(B1:B52) But the true average doesn't show until all 52 weeks have been put in. Is there a way to work out the average as the year goes on? EG: week 1 $13235.92 Week 2 $18231.56 Average = $15733.74 Week 3 $16453.76 Average = $15973.74 Not like current Week 1 $13235.92 Average = $254.54 Week 2 $18231.56 Average = $605.14 Week 3 $16453.76 Average = $921.56 Justin, I bet the reason why this formula =AVERAGE(B1:B52) is giving you an "incorrect" result is because you've ente...

Help with average formula
Hi, I'm trying to calculate average of figures as follows: 1.1.2003 10 2.1.2003 20 3.1.2003 15 4.1.2003 5.1.2003 5 So the question is that if there is one row that has no numbers in it how do I exclude it from the average... meaning that average would b counted like this (10+20+15+5)/4 and not (10+20+15+5)/5. Thanks //juh -- Message posted from http://www.ExcelForum.com Juha If a cell is blank, it won't be included in the average. Andy. "Juha" <Juha.ybp1y@excelforum-nospam.com> wrote in message news:Juha.ybp1y@excelforum-nospam.com... > Hi, > > I...

Average Formula #5
Hi, can anybody advise of a formula to work out an average on a given number on the days passed in the month.....?? eg data below for first 5 days of month day1,20 day2,25 day3,32 day4,10 day5,9 so the average thus far in the month will be 19.2 (first 5days data divided by 5) if days 6 and 7 are added the formula would then work out the average for the first 7 days.....and so on hope thats clear thanks I would recommend putting the "day1" and the "20" in separate columns. Then use the Average function to find your average - it will only include cells with numbers in...

average formula help #2
We have a range of cells A1:A10 which contain values as follows:- 1 2 4 5 0 0 6 8 9 10 We need Cell A11 to display an average of A1 to A10 but ignoring the 0 values. Is this possible with a formula? Thanks in advance Kevin Kevin, try this =AVERAGE(IF(A1:A10 <>0,A1:A10 )) -- Paul B Always backup your data before trying something new Please post any response to the newsgroups so others can benefit from it Feedback on answers is always appreciated! Using Excel 97 & 2000 ** remove news from my email address to reply by email ** "kevin carter&quo...

AVERAGE formula with a twist
Hello - I need to average a range of cells B2:Y31. Normally, I'd use the formula =AVERAGE(B2:Y31)... easy enough. The twist is that I need to average that range of cells, BUT any cell that has a value less than 1 or more than 900 cannot be used in the calculation. These values are considered invalid for my purposes. All cell values are referencing a cell value in another worksheet that is part of the spreadsheet; for instance =bam!B2 How can I use a formula to automatically calculate the average of the range without calculating the invalid values? TIA! --- Phil =AVERAGE(IF((...

Conditional Formatting with average formula and variance
Hello: Can somebody please advise how to include a variance in conditional formating when using average? i.e. I'd like to highlight cells in red if the average of a range is +/- 2% of the number (so if H7 is 24% and the average of B7:G7 is 28% the H7 cell will be in red and if average of B7:G7 is 25%, which is within the 2% variance the H7 cell will be in green). Simple average worked (=H7>AVERAGE(B7:G7) --- red)....but can't figure out how to include the variance of 2% up and down from average... Thank you. Monika Try =ABS(H7-AVERAGE(B7:G7))<=2 ...

help creating average formula
I have a spreadsheet that has 29 clients information for the year 2004, broken down by month and they've asked me to average out collections, services provided etc. I need to have this reflect the average per month. I first tried totalling all columns and then put basic average formula like this in =average(d4:d541) and figured, but I'm not sure that using this formula has given me the average per month. Would this have given me the average per month for the entire year? Thanks in advance for your help Deb, To accomplish this you should use the subtotal feature. Make sure yo...

Average formula where blank cells are counted as zeros
I am trying to write an average formula that takes into account the blank cells. I had only cells A2, A4, A6 filled out, but when I do =average(A1:A6), it is not treating the blanks as zeros so the answer is much higher than it should be. Should I do a logic formula within the cells? If you know the range, you could do the division yourself: =sum(a1:a6)/6 But this would include other non-numeric cells in the count of cells (6), too. krwelling wrote: > > I am trying to write an average formula that takes into account the blank > cells. > > I ha...

Question with regard to Average formula
Hi all, I have a work sheet called 'HSO Front Sheet' and another one called 'HSO Data sheet'. On the front sheet it averages 5 cells from the data sheet using the following forumla: ='HSO Data sheet'!D4+'HSO Data sheet'!F4+'HSO Data sheet'!H4+'HSO Data sheet'!J4+'HSO Data sheet'!L4 If no data is contained within the data sheet the cell on the front sheet returns: #DIV/0! This then messes up an average of the averages sum i've got elsewhere :P I've tried ='HSO Data sheet'!D4+'HSO Data sheet'!F4+'HSO Data sh...

Simplify Weighted Average Formula
I have a s/s that has the months of the year across the top and below 3 categories for each month and I want a weighted avg for each product. eg: A B C D E F G H I January February March etc. to Dec Days Oil Gas Days OIl Gas Days Oil Gas 6 12 200 4 5 180 7 8 300 formula: =(a1*b1+D1*E1+G1*h1)/(a1+d1+g1) Is there a way to simplify this? Thank in advance for any help. -- ferne If you could rearrange your data table then you could use SUMPRODUCT and SUM to get you the weight...

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! Needs...

rolling average formula
I'm sure this a snap for the sharks... I wish to create a formula for a basic daily rolling average, up to now I have been typing in a formula for each day which is time consuming and very inefficient, as follows... Sequence Value Average formula day #1 25 25 `= 25/1 day #2 5 15 `=30/2 day #3 3 11 `=33/3 day #4 6 9.8 `=39/4 day #5 13 10.4 `=52/5 day #6 10 10.3 `=62/6 day #7 12 10.6 `=74/7 day #8 25 12.4 `=99/8 and so on thanks for any help Gra On Oct 14, 12:4...

Average Formula
How can I write the average formula so it does not count the cells with zeros in it... A1= 12 A2= 0 A3 = 12 A4 = 0 A5= 12 A6 =0 if I write the formula as =average(a1:a6) the value is 6, but I want it to read 12. {=AVERAGE(IF(A1:A6<>0,A1:A6))} *** This is an array formula, and is to be confirmed with CTRL+SHIFT+ENTER rather than with simply ENTER. The curly brackets {} are not to be typed manually, those are entered by the “Excel”, when the formula is entered as an Array formula. *** Micky "Peter" wrote: > How can I write the average formula so it ...

Average Formula with Criteria
Hi, I have a spreadsheet that is used to average mechnical and electrical costs for a number of projects, split into years. Each project has a Work Area (m2) and a specific contractor (there are three different contractors that are responsible for the projects). At the bottom of the spreadsheet I have a number of average tables, one for each contractor. I currently average the Work Area by using an average function and manually selecting all the work areas for the specific contractor. However, when I add projects this becomes a bit of a hasle. Ideally I want a formula that says: Av...

I need help to make a not-so-easy AVERAGE formula!
Here's a description of spreadsheet that I am making for the sales of a store: - Column A has all the dates of the year (January 1, January 2, January 3, etc.) for the entire year listed one after another (so a total of 365 rows, starting in row 1). - Column B has the day of the week (Monday, Tuesday, Wednesday, etc.) corresponding to the date next to it in column A. - Column C has the sales amount (example: $125.50) corresponding to the date/day listed in the same row of columns A and B. I have the following formula (which works great) which gives me the SUM of all sales mad...

Want average formula.
I want to make this symmetry curve more smooth. these pair is 0 and 27, 1 and 26, 2 and 25, 3 and 24.........etc. the highest of curve is on center, and the lowest of curve is on two side. i try take pair points on two side, averages to a new point. it will be a smooth symmetry curve. how i do it by formula? thanks. 0 0 1 0.001724138 2 0.005172414 3 0.006896552 4 0.017241379 5 0.027586207 6 0.027586207 7 0.046551724 8 0.043103448 9 0.067241379 10 0.056896552 11 0.075862069 12 0.051724138 13 0.081034483 14 0.081034483 15 0.084482759 16 0.060344828 17 0.055172414 18 0.053448276 19 0.04137...

Hold a cell range for an "average formula"
I add a column every week to my work sheet and want the formula to always calculate the current 5 column range. Wendy Akers wrote: > I add a column every week to my work sheet and want the formula to always > calculate the current 5 column range. > > Add a column WHERE? What "formula"? Where is the "current 5 column range"? Hi, Let's say your data is in range C3:H3. In cell K3, enter the following array formula (Ctrl+Shift+Enter) to get the average =AVERAGE(OFFSET(INDIRECT(ADDRESS(3,MAX(ISNUMBER(C3:J3)*COLUMN(C3:J3))+1)),0,...

Average Formula #3
I need help creating an average formula that will only average those numbers that are greater then 0. For example if I have 3,0,3,3,6,5 I need a formula that will average only 3,3,3,6,5. Any help would be appreciated. Thanks ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~ View and post usenet messages directly from http://www.ExcelForum.com/ One way: =SUMIF(A1:A10,">0")/COUNTIF(A1:A10,">0") HTH Jason Atlanta, GA >-----Original Message----- >I need help creating an average formula that will only ...