Median of the last 7 non-blank values, offset from a cell

[MatrixBS]

All,

I've scoured the internet looking for an answer to this problem, but am left unsatisfied. Maybe i'm over complicating things, & I hope someone can help me out.

I currently have a spreadsheet that is updated on a monthly basis where I need to calculate the median of the last 7 reportable (i.e. non-zero) values prior to my current data. I cannot simply "remove blanks" as I have additional pertinent information on adjacent columns. Here's a sample dataset:

[TABLE="width: 300"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]1/1/16[/TD]
[TD]1[/TD]
[TD]0.2[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]2/1/16[/TD]
[TD]5[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]3/1/16[/TD]
[TD]15[/TD]
[TD]6[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]4/1/16[/TD]
[TD][/TD]
[TD]9[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]5/1/16[/TD]
[TD][/TD]
[TD]15[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]6/1/16[/TD]
[TD]4[/TD]
[TD]24[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]7/1/16[/TD]
[TD]5[/TD]
[TD]17[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]8/1/16[/TD]
[TD][/TD]
[TD]10[/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]9/1/16[/TD]
[TD][/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]10/1/16[/TD]
[TD][/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD]11/1/16[/TD]
[TD][/TD]
[TD]15[/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD]12/1/16[/TD]
[TD]0.5[/TD]
[TD]56[/TD]
[/TR]
[TR]
[TD]13[/TD]
[TD]1/1/17[/TD]
[TD][/TD]
[TD]54[/TD]
[/TR]
[TR]
[TD]14[/TD]
[TD]2/1/17[/TD]
[TD]11[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]15[/TD]
[TD]3/1/17[/TD]
[TD]13[/TD]
[TD]9[/TD]
[/TR]
[TR]
[TD]16[/TD]
[TD]4/1/17[/TD]
[TD]17[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]17[/TD]
[TD]Median of last 7 value[/TD]
[TD]FORMULA? [/TD]
[TD]9[/TD]
[/TR]
</tbody>[/TABLE]

Assuming 4/1/17 is my current dataset, I need to calculate the median of the last 7 data points in column B prior to 4/1/17 (i.e. last 7 values from 1/1/16 through 3/1/17 [rows 1 through 15]) to compare to my current data. I am using the array formula:

=MEDIAN(INDIRECT("B"&LARGE(ISNUMBER(B1:B15)*ROW(B1:B15),7)&":B15"))

which ultimately calculates the correct result, but that array formula is cumbersome. I also have thousands of these discrete datasets where the "B" in INDIRECT("B" and the ":B15") at the end of the formula don't carry when I copy from row-to-row and column-to-column. Is there a more elegant (preferably non-array) formula I can use that I haven't thought of?

As an added bonus, is there any way I could make the formula automatically update as I insert data for 5/1/17 and beyond (i.e. insert data into row 17)?

Thank you for all of the help I've gotten over the years from the experts at Mr.Excel.

 

[J.Ty.]

Hi,

Is the following (with a helper column) any better than what you already have?

J.Ty.

Excel 2016 (Windows) 64 bit
	A	B	C	D	E
1	id	date	number	info	9
2	1	01.01.2016	1	0.2	8
3	2	02.01.2016	5	3	7
4	3	03.01.2016	15	6	6
5	4	04.01.2016		9	6
6	5	05.01.2016		15	6
7	6	06.01.2016	4	24	5
8	7	07.01.2016	5	17	4
9	8	08.01.2016		10	4
10	9	09.01.2016		2	4
11	10	10.01.2016		3	4
12	11	11.01.2016		15	4
13	12	12.01.2016	0.5	56	3
14	13	01.01.2017		54	3
15	14	02.01.2017	11	3	2
16	15	03.01.2017	13	9	1
17	16	04.01.2017	17	1	0
18	17	Median of last 7 value	11	9
Sheet2

Cell Formulas
Range	Formula
E1	=IF(ISNUMBER(C2),1+E2,E2)
E2	=IF(ISNUMBER(C3),1+E3,E3)
E3	=IF(ISNUMBER(C4),1+E4,E4)
E4	=IF(ISNUMBER(C5),1+E5,E5)
E5	=IF(ISNUMBER(C6),1+E6,E6)
E6	=IF(ISNUMBER(C7),1+E7,E7)
E7	=IF(ISNUMBER(C8),1+E8,E8)
E8	=IF(ISNUMBER(C9),1+E9,E9)
E9	=IF(ISNUMBER(C10),1+E10,E10)
E10	=IF(ISNUMBER(C11),1+E11,E11)
E11	=IF(ISNUMBER(C12),1+E12,E12)
E12	=IF(ISNUMBER(C13),1+E13,E13)
E13	=IF(ISNUMBER(C14),1+E14,E14)
E14	=IF(ISNUMBER(C15),1+E15,E15)
E15	=IF(ISNUMBER(C16),1+E16,E16)
E16	=IF(ISNUMBER(C17),1+E17,E17)
C18	=MEDIAN(INDEX(C1:C17,MATCH(0,E1:E17,0)), INDEX(C1:C17,MATCH(1,E1:E17,0)), INDEX(C1:C17,MATCH(2,E1:E17,0)), INDEX(C1:C17,MATCH(3,E1:E17,0)), INDEX(C1:C17,MATCH(4,E1:E17,0)), INDEX(C1:C17,MATCH(5,E1:E17,0)), INDEX(C1:C17,MATCH(6,E1:E17,0)) )

 

[Aladin Akyurek]

What is the expected value? If 9, that seems to be related to range in column C while your non-working formula refers to the range in column B!...

 

[J.Ty.]

What is the expected value? If 9, that seems to be related to range in column C while your non-working formula refers to the range in column B!...

Aladin,

Column C is definitely not the intended results. Look at rows 3, 4 and 5. According to the OP the value of the formula should not change, because there are no new values in column B, while the values in column C do change.

J.Ty.

 

[Aladin Akyurek]

Thanks T.Ty for clarification.

Sheet1 (data plus processing)

[Table="width:, class:grid"][tr][td]Row\Col[/td][td]

A​

[/td][td]

B​

[/td][td]

C​

[/td][/tr][tr][td]

1​

[/td][td]

1/1/2016​

[/td][td]

1​

[/td][td]

0​

[/td][/tr]
[tr][td]

2​

[/td][td]

2/1/2016​

[/td][td]

5​

[/td][td]

3​

[/td][/tr]
[tr][td]

3​

[/td][td]

3/1/2016​

[/td][td]

15​

[/td][td]

6​

[/td][/tr]
[tr][td]

4​

[/td][td]

4/1/2016​

[/td][td][/td][td]

9​

[/td][/tr]
[tr][td]

5​

[/td][td]

5/1/2016​

[/td][td][/td][td]

15​

[/td][/tr]
[tr][td]

6​

[/td][td]

6/1/2016​

[/td][td]

4​

[/td][td]

24​

[/td][/tr]
[tr][td]

7​

[/td][td]

7/1/2016​

[/td][td]

5​

[/td][td]

17​

[/td][/tr]
[tr][td]

8​

[/td][td]

8/1/2016​

[/td][td][/td][td]

10​

[/td][/tr]
[tr][td]

9​

[/td][td]

9/1/2016​

[/td][td][/td][td]

2​

[/td][/tr]
[tr][td]

10​

[/td][td]

10/1/2016​

[/td][td][/td][td]

3​

[/td][/tr]
[tr][td]

11​

[/td][td]

11/1/2016​

[/td][td][/td][td]

15​

[/td][/tr]
[tr][td]

12​

[/td][td]

12/1/2016​

[/td][td]

1​

[/td][td]

56​

[/td][/tr]
[tr][td]

13​

[/td][td]

1/1/2017​

[/td][td][/td][td]

54​

[/td][/tr]
[tr][td]

14​

[/td][td]

2/1/2017​

[/td][td]

11​

[/td][td]

3​

[/td][/tr]
[tr][td]

15​

[/td][td]

3/1/2017​

[/td][td]

13​

[/td][td]

9​

[/td][/tr]
[tr][td]

16​

[/td][td]

4/1/2017​

[/td][td]

17​

[/td][td]

1​

[/td][/tr]
[tr][td]

17​

[/td][td]Median of last 7 value[/td][td]

5​

[/td][td][/td][/tr]
[/table]

Define the following names one by one in Formulas | Name Manager.

Drow as referring to:

=MATCH(9.99999999999999E+307,Sheet1!$A:$A)-1

Data as referring to:

=Sheet1!$B$1:INDEX(Sheet1!$B:$B,Drow)

Now we can invoke in the last row, cuurently in B17...

=MEDIAN(IF(ROW(Data)>=LARGE(IF(ISNUMBER(Data),ROW(Data)),MIN(7,SUM(IF(ISNUMBER(Data),1)))),IF(ISNUMBER(Data),Data)))

Note that this set up will adjust itself dynamically to adding new records in-between the end of the data and the formula cell.