Home |
Search |
Today's Posts |
#1
|
|||
|
|||
calculate last 4 data points in a column (with sometimes blank rows)
Hello-
I'd like a formula to place the average of the last 4 data points (sometimes there will be blanks) in a column and display that value in another column. By "last 4", I mean last 4 entries in the row selected, or above. See attachment. So- I would like to put, in column BA, the average of the last 4 entries in column AY, for that row and above. I'd like to be able to copy that formula down the row so I can get a snapshot for each month. So for instance, cell BA36 should have the average of the lowest 4 values in column AY, from row 36 and up (result should be 1.29). Likewise, cell BA54 should get the average of the lowest 4 entries in column AY, from row 54 an up (result should be average of cells AY47, AY47, AY49,AY51=1.89). Any help please? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
calculate last 4 data points in a column (with sometimes blank rows)
Modify to suit a to ay and 10 to 4
array formula (CRTL+Shift+Enter): =SUM(INDIRECT("A"&LARGE(IF(ISBLANK(A2:A10000),0,RO W(2:10000)), 10)):A10000) On Feb 15, 1:58*pm, puck1263 wrote: Hello- I'd like a formula to place the average of the last 4 data points (sometimes there will be blanks) in a column and display that value in another column. By "last 4", I mean last 4 entries in the row selected, or above. *See attachment. So- I would like to put, in column BA, the average of the last 4 entries in column AY, for that row and above. *I'd like to be able to copy that formula down the row so I can get a snapshot for each month. So for instance, cell BA36 should have the average of the lowest 4 values in column AY, from row 36 and up (result should be 1.29). Likewise, cell BA54 should get the average of the lowest 4 entries in column AY, from row 54 an up (result should be *average of cells AY47, AY47, AY49,AY51=1.89). Any help please? +-------------------------------------------------------------------+ |Filename: excel question.JPG * * * * * * * * * * * * * * * * * * * | |Download:http://www.excelbanter.com/attachment.php?attachmentid=295| +-------------------------------------------------------------------+ -- puck1263 |
#3
|
|||
|
|||
Excel says there are too many arguments in this function.
|
#4
|
|||
|
|||
So, I looked at this again and must have copied something wrong.
Now I get the #NAME? error all the way down the column. Here is what I have in the first cell (row 19) SUM(INDIRECT("AY"&LARGE(IF(ISBLANK(AY19:AY1000),0, RO AY(19:1000)),4)):AY1000) |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
calculate last 4 data points in a column (with sometimes blank rows)
I see a couple things wrong with your formula as posted but might be
typos or just the way you copied it. Try this based on Don's formula edited. =SUM(INDIRECT("AY"&LARGE(IF(ISBLANK(AY19:AY10000), 0,ROW(19:10000)),4)):AY10000) Gord On Mon, 27 Feb 2012 19:38:15 +0000, puck1263 wrote: So, I looked at this again and must have copied something wrong. Now I get the #NAME? error all the way down the column. Here is what I have in the first cell (row 19) SUM(INDIRECT("AY"&LARGE(IF(ISBLANK(AY19:AY1000),0 ,RO AY(19:1000)),4)):AY1000) +-------------------------------------------------------------------+ +-------------------------------------------------------------------+ |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
calculate last 4 data points in a column (with sometimes blank rows)
On Wed, 15 Feb 2012 19:58:23 +0000, puck1263 wrote:
Hello- I'd like a formula to place the average of the last 4 data points (sometimes there will be blanks) in a column and display that value in another column. By "last 4", I mean last 4 entries in the row selected, or above. See attachment. So- I would like to put, in column BA, the average of the last 4 entries in column AY, for that row and above. I'd like to be able to copy that formula down the row so I can get a snapshot for each month. So for instance, cell BA36 should have the average of the lowest 4 values in column AY, from row 36 and up (result should be 1.29). Likewise, cell BA54 should get the average of the lowest 4 entries in column AY, from row 54 an up (result should be average of cells AY47, AY47, AY49,AY51=1.89). Any help please? +-------------------------------------------------------------------+ |Filename: excel question.JPG | |Download: http://www.excelbanter.com/attachment.php?attachmentid=295| +-------------------------------------------------------------------+ This formula must be **array-entered**. Then select and fill down as far as required: BA36: =AVERAGE(IF(ROW($AY$1:AY36)=LARGE( ISNUMBER($AY$1:AY36)*ROW($AY$1:AY36), {1,2,3,4}),$AY$1:AY36)) ---------------------------------------- To **array-enter** a formula, after entering the formula into the cell or formula bar, hold down <ctrl<shift while hitting <enter. If you did this correctly, Excel will place braces {...} around the formula. |
#7
|
|||
|
|||
That works. Thanks very much.
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
calculate last 4 data points in a column (with sometimes blank rows)
On Mon, 5 Mar 2012 22:06:27 +0000, puck1263 wrote:
That works. Thanks very much. +-------------------------------------------------------------------+ +-------------------------------------------------------------------+ Glad to help. Thanks for the feedback. |
#9
|
|||
|
|||
Hello again -
I now have a variation on this. What the above is looking at is looking for the average of the "last" 4 values in a column, (skipping blanks). Those values happened to be averages of defects/month (# of defects in month/shipments in month) (See column E in attached spreadhseet...it looks at value in column D) That's all fine and good, except (see example) the # of shipments greatly varies month to month, making this not the best representation. What I would rather do is break up the avg calculation. (If column B is blank, column C will always be 0 as defects are tied to the shipment in the month it shipped.) Instead of (say all fields have a value as they do in this example) what I have now, which is AVG(D2:D5) or effectively: (C2/B2 + C3/B3 + C4/B4 + C5/B5)/4 I would rather have (C2+C3+C4+C5)/(B2+B3+B4+B5) where these are the last 4 rows that have values. I have simulated this manually in column F. Any idea how to do this? |
#10
|
|||
|
|||
I got it.
I changed the AVERAGE array formula provided above to SUM and made a column to sum numerator. I did same for denominator. Then took the average across the row of those two cells. (note - I had to insert an intermediate formula where if denominator is blank, numerator would be blank, not zero). Hope this helps anyone else. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Handling blank data points | Charts and Charting in Excel | |||
How do I delete blank rows (rows alternate data, blank, data, etc | Excel Discussion (Misc queries) | |||
how to calculate the number of non blank cells from any column? | Excel Worksheet Functions | |||
Blank cells and Charting multiple data points | Charts and Charting in Excel | |||
Loading Column Data with blank Rows into Data Validation Box | Excel Worksheet Functions |