Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Dynamic Average
Hi There,
I have a spreadsheet that each week i have to average the last 8 week inputs, as you can image each week the last 8 weeks information changes. Any help would be appreciated. thanks Big H |
#2
|
|||
|
|||
Dynamic Average
Hi!
So, what you want to do is average the last 8 entries, correct? Is the data in a column or a row? Are there any empty cells within the range? Biff "Big H" wrote in message ... Hi There, I have a spreadsheet that each week i have to average the last 8 week inputs, as you can image each week the last 8 weeks information changes. Any help would be appreciated. thanks Big H |
#3
|
|||
|
|||
Dynamic Average
Hi Biff,
The data is in the row and if there is a zero, i would want to include it in the calculation thanks Harry "Biff" wrote in message ... Hi! So, what you want to do is average the last 8 entries, correct? Is the data in a column or a row? Are there any empty cells within the range? Biff "Big H" wrote in message ... Hi There, I have a spreadsheet that each week i have to average the last 8 week inputs, as you can image each week the last 8 weeks information changes. Any help would be appreciated. thanks Big H |
#4
|
|||
|
|||
Dynamic Average
Try this *array* formula for data in A1 to Z1:
=AVERAGE(Z1:INDEX(A1:Z1,LARGE(COLUMN(A1:Z1)*(A1:Z1 <""),8))) -- Array formulas are entered using CSE, <Ctrl <Shift <Enter, instead of the regular <Enter, which will *automatically* enclose the formula in curly brackets, which *cannot* be done manually. -- HTH, RD ================================================== === Please keep all correspondence within the Group, so all may benefit! ================================================== === "Big H" wrote in message ... Hi Biff, The data is in the row and if there is a zero, i would want to include it in the calculation thanks Harry "Biff" wrote in message ... Hi! So, what you want to do is average the last 8 entries, correct? Is the data in a column or a row? Are there any empty cells within the range? Biff "Big H" wrote in message ... Hi There, I have a spreadsheet that each week i have to average the last 8 week inputs, as you can image each week the last 8 weeks information changes. Any help would be appreciated. thanks Big H |
#5
|
|||
|
|||
Dynamic Average
Hi!
Another way: (assumes no empty cells within the range and that cell A1 is header) =AVERAGE(OFFSET(A1,,COUNT(1:1),,-8)) You may want to make sure there are at least 8 values to average: =IF(COUNT(1:1)<8,"Insufficient Data",AVERAGE(OFFSET(A1,,COUNT(1:1),,-8))) Biff "Big H" wrote in message ... Hi Biff, The data is in the row and if there is a zero, i would want to include it in the calculation thanks Harry "Biff" wrote in message ... Hi! So, what you want to do is average the last 8 entries, correct? Is the data in a column or a row? Are there any empty cells within the range? Biff "Big H" wrote in message ... Hi There, I have a spreadsheet that each week i have to average the last 8 week inputs, as you can image each week the last 8 weeks information changes. Any help would be appreciated. thanks Big H |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
plotted Average | Charts and Charting in Excel | |||
What is this kind of average called? | Excel Worksheet Functions | |||
Label an average line in a dynamic chart | Charts and Charting in Excel | |||
Average Formula with Criteria | Excel Discussion (Misc queries) | |||
Can't create dynamic charts | Charts and Charting in Excel |