Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Averaging last 25 non zero values.
Hi,
Have a dynamic list of sales values and want to make several calculations on them, most of which I can do myself.... However, I want to find an average of the last 25 (non-zero) values without using a macro is this possible?? Many thanks in advance, Peter |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Averaging last 25 non zero values.
Maybe
=AVERAGE(LARGE( SUBTOTAL(9, OFFSET(A1:A100,LARGE(IF(A1:A100<0,ROW(A1:A100)-MIN(ROW(A1:A100))),ROW(INDIRECT("1:25"))),0,1)),{1 ,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20, 21,22,23,24,25})) It's an array so enter with Ctrl+Shift+Enter Works on the range A1 - A100 so change to suit. There must be a better way so lets wait for other solutions Mike " wrote: Hi, Have a dynamic list of sales values and want to make several calculations on them, most of which I can do myself.... However, I want to find an average of the last 25 (non-zero) values without using a macro is this possible?? Many thanks in advance, Peter |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Averaging last 25 non zero values.
wrote in message
... Hi, Have a dynamic list of sales values and want to make several calculations on them, most of which I can do myself.... However, I want to find an average of the last 25 (non-zero) values without using a macro is this possible?? Many thanks in advance, Peter Here's an adaptation of one I developed some time ago. It's an array formula, so you need to use CTRL+SHIFT+ENTER (rather than just ENTER) when you first enter it and whenever you edit it. =AVERAGE(OFFSET(A1,LARGE(ISNUMBER(A1:A100)*(ROW(A1 :A100)-ROW(A1)),25),0,ROWS(A1:A100)-LARGE(ISNUMBER(A1:A100)*(ROW(A1:A100)-ROW(A1)),25))) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Averaging without the highest or lowest values | Excel Discussion (Misc queries) | |||
HELP: Averaging values if they meet certain criteria | Excel Worksheet Functions | |||
Averaging Values between Two Dates/Times | Excel Worksheet Functions | |||
Averaging Values in Auto Filter | Excel Worksheet Functions | |||
Averaging selected values | Excel Worksheet Functions |