Extract a range of values

[BigBeachBananas]

Hi all,

I have the following sample data. I have determined the number of usable months for my data in cell G2. Now, I need help extracting the usable months' value from columns D to E (highlighted in yellow). Essentially, it should be the last non-zero value in the Count Column (Column C), counting up to # of usable months (in this case 25). Let me know if this is unclear.

Excel question.xlsx
	A	B	C	D	E	F	G
1	Month	Claims	Count	Rolling 12 Months Average	Values to Use
2	1	$0	0			Months to use	25
3	2	$0	0
4	3	$0	0
5	4	$0	0
6	5	$0	0
7	6	$0	0
8	7	$0	0
9	8	$0	0
10	9	$0	0
11	10	$0	0
12	11	$0	0
13	12	$0	0	#DIV/0!
14	13	$0	0	#DIV/0!
15	14	$0	0	#DIV/0!
16	15	$0	0	#DIV/0!
17	16	$0	0	#DIV/0!
18	17	$0	0	#DIV/0!
19	18	$0	0	#DIV/0!
20	19	$891,543	1570	$567.86
21	20	$775,318	1576	$529.84
22	21	########	1583	$577.71
23	22	$997,811	1577	$591.47
24	23	$999,928	1580	$599.76
25	24	########	1576	$611.70
26	25	$949,805	1591	$609.59
27	26	$775,797	1582	$594.66
28	27	########	1595	$625.16
29	28	########	1598	$660.68
30	29	########	1603	$672.46
31	30	########	1598	$698.61
32	31	########	1606	$710.88
33	32	########	1622	$740.38
34	33	$888,167	1621	$729.67
35	34	########	1624	$744.11
36	35	########	1632	$778.88
37	36	########	1625	$791.12
38	37	########	1652	$797.95
39	38	########	1671	$810.85
40	39	########	1676	$815.76
41	40	########	1689	$798.80
42	41	########	1707	$817.86
43	42	########	1701	$821.09
44	43	########	1722	$820.74
45	44	########	1740	$820.93
46	45	########	1775	$846.20
47	46	########	1798	$845.88
48	47	########	1799	$832.41
49	48	########	1799	$845.78
50	49	########	2061	$909.88
51	50	########	2073	$935.70
52	51	########	2080	$936.90
53	52	########	2079	$938.26
54	53	########	2082	$918.43
55	54	########	2072	$899.64
56	55	$0	0	$914.17
57	56	$0	0	$920.20
58	57	$0	0	$926.24
59	58	$0	0	$939.67
60	59	$0	0	$947.95
61	60	$0	0	$944.02
Sheet1

Cell Formulas
Range	Formula
G2	G2	=COUNTIF(C2:C61,">0")-11
D13:D61	D13	=SUM(B2:B13)/SUM(C2:C13)

 

[awoohaw]

please repost your mini worksheet with the column widths wide enough to display values.
also, can you provide a sample of expected values in the cells you want to see the data in?

 

[BigBeachBananas]

please repost your mini worksheet with the column widths wide enough to display values.
also, can you provide a sample of expected values in the cells you want to see the data in?

Excel question.xlsx
	A	B	C	D	E	F	G
1	Month	Claims	Count	Rolling 12 Months Average	Values to Use		Months to use
2	1	$0	0		$ 698.61		25
3	2	$0	0		$ 710.88
4	3	$0	0		$ 740.38
5	4	$0	0		$ 729.67
6	5	$0	0		$ 744.11
7	6	$0	0		$ 778.88
8	7	$0	0		$ 791.12
9	8	$0	0		$ 797.95
10	9	$0	0		$ 810.85
11	10	$0	0		$ 815.76
12	11	$0	0		$ 798.80
13	12	$0	0	#DIV/0!	$ 817.86
14	13	$0	0	#DIV/0!	$ 821.09
15	14	$0	0	#DIV/0!	$ 820.74
16	15	$0	0	#DIV/0!	$ 820.93
17	16	$0	0	#DIV/0!	$ 846.20
18	17	$0	0	#DIV/0!	$ 845.88
19	18	$0	0	#DIV/0!	$ 832.41
20	19	$891,543	1570	$567.86	$ 845.78
21	20	$775,318	1576	$529.84	$ 909.88
22	21	$1,065,137	1583	$577.71	$ 935.70
23	22	$997,811	1577	$591.47	$ 936.90
24	23	$999,928	1580	$599.76	$ 938.26
25	24	$1,058,209	1576	$611.70	$ 918.43
26	25	$949,805	1591	$609.59	$ 899.64
27	26	$775,797	1582	$594.66
28	27	$1,382,508	1595	$625.16
29	28	$1,561,156	1598	$660.68
30	29	$1,264,492	1603	$672.46
31	30	$1,572,136	1598	$698.61
32	31	$1,150,540	1606	$710.88
33	32	$1,371,838	1622	$740.38
34	33	$888,167	1621	$729.67
35	34	$1,309,264	1624	$744.11
36	35	$1,708,064	1632	$778.88
37	36	$1,332,442	1625	$791.12
38	37	$1,130,311	1652	$797.95
39	38	$1,097,642	1671	$810.85
40	39	$1,544,120	1676	$815.76
41	40	$1,302,715	1689	$798.80
42	41	$1,723,401	1707	$817.86
43	42	$1,720,510	1701	$821.09
44	43	$1,238,686	1722	$820.74
45	44	$1,472,472	1740	$820.93
46	45	$1,525,541	1775	$846.20
47	46	$1,449,919	1798	$845.88
48	47	$1,572,452	1799	$832.41
49	48	$1,754,309	1799	$845.78
50	49	$2,831,177	2061	$909.88
51	50	$2,019,780	2073	$935.70
52	51	$1,948,335	2080	$936.90
53	52	$1,698,502	2079	$938.26
54	53	$1,624,853	2082	$918.43
55	54	$1,627,558	2072	$899.64
56	55	$0	0	$914.17
57	56	$0	0	$920.20
58	57	$0	0	$926.24
59	58	$0	0	$939.67
60	59	$0	0	$947.95
61	60	$0	0	$944.02
Sheet1

Cell Formulas
Range	Formula
G2	G2	=COUNTIF(C2:C61,">0")-11
D13:D61	D13	=SUM(B2:B13)/SUM(C2:C13)

 

[awoohaw]

Thanks, try this. I hope this is what your want.

Mr excel questions 58.xlsm
	A	B	C	D	E	F	G
1	Month	Claims	Count	Rolling 12 Months Average	Values to Use
2	1	0.00	0		778.88	Months to use	25
3	2	0.00	0		791.12
4	3	0.00	0		797.95
5	4	0.00	0		810.85
6	5	0.00	0		815.76
7	6	0.00	0		798.80
8	7	0.00	0		817.86
9	8	0.00	0		821.09
10	9	0.00	0		820.74
11	10	0.00	0		820.93
12	11	0.00	0		846.20
13	12	0.00	0		845.88
14	13	0.00	0		832.41
15	14	0.00	0		845.78
16	15	0.00	0		909.88
17	16	0.00	0		935.70
18	17	0.00	0		936.90
19	18	0.00	0		938.26
20	19	891,543.48	1,570	567.86	918.43
21	20	775,317.76	1,576	529.84	899.64
22	21	1,065,137.31	1,583	577.71	914.17
23	22	997,810.63	1,577	591.47	920.20
24	23	999,928.33	1,580	599.76	926.24
25	24	1,058,209.30	1,576	611.70	939.67
26	25	949,804.65	1,591	609.59	947.95
27	26	775,796.51	1,582	594.66	944.02
28	27	1,382,508.04	1,595	625.16
29	28	1,561,156.35	1,598	660.68
30	29	1,264,491.85	1,603	672.46
31	30	1,572,136.14	1,598	698.61
32	31	1,150,540.11	1,606	710.88
33	32	1,371,838.30	1,622	740.38
34	33	888,167.00	1,621	729.67
35	34	1,309,264.06	1,624	744.11
36	35	1,708,063.99	1,632	778.88
37	36	1,332,442.40	1,625	791.12
38	37	1,130,311.41	1,652	797.95
39	38	1,097,642.08	1,671	810.85
40	39	1,544,119.81	1,676	815.76
41	40	1,302,715.39	1,689	798.80
42	41	1,723,400.94	1,707	817.86
43	42	1,720,509.52	1,701	821.09
44	43	1,238,686.22	1,722	820.74
45	44	1,472,471.91	1,740	820.93
46	45	1,525,541.11	1,775	846.20
47	46	1,449,919.08	1,798	845.88
48	47	1,572,451.84	1,799	832.41
49	48	1,754,309.13	1,799	845.78
50	49	2,831,177.04	2,061	909.88
51	50	2,019,780.40	2,073	935.70
52	51	1,948,334.76	2,080	936.90
53	52	1,698,502.04	2,079	938.26
54	53	1,624,852.78	2,082	918.43
55	54	1,627,558.16	2,072	899.64
56	55	0.00	0	914.17
57	56	0.00	0	920.20
58	57	0.00	0	926.24
59	58	0.00	0	939.67
60	59	0.00	0	947.95
61	60	0.00	0	944.02
BigBeachBananas

Cell Formulas
Range	Formula
E2:E27	E2	=INDEX($D$2:$D$61,MAX(($A$2:$A$61)*($D$2:$D$61>0))-25):INDEX($D$2:$D$61,MAX(($A$2:$A$61)*($D$2:$D$61>0)))
G2	G2	=COUNTIF(C2:C61,">0")-11
D13:D61	D13	=IF(SUM(C2:C13)=0,"",SUM(B2:B13)/SUM(C2:C13))
Dynamic array formulas.

 

[Fluff]

How about

=CHOOSEROWS(D2:D100,SEQUENCE(G2,,XMATCH(TRUE,(C1:C100<>0),,-1)-G2))

 

[BigBeachBananas]

Thanks, try this. I hope this is what your want.

Mr excel questions 58.xlsm
	A	B	C	D	E	F	G
1	Month	Claims	Count	Rolling 12 Months Average	Values to Use
2	1	0.00	0		778.88	Months to use	25
3	2	0.00	0		791.12
4	3	0.00	0		797.95
5	4	0.00	0		810.85
6	5	0.00	0		815.76
7	6	0.00	0		798.80
8	7	0.00	0		817.86
9	8	0.00	0		821.09
10	9	0.00	0		820.74
11	10	0.00	0		820.93
12	11	0.00	0		846.20
13	12	0.00	0		845.88
14	13	0.00	0		832.41
15	14	0.00	0		845.78
16	15	0.00	0		909.88
17	16	0.00	0		935.70
18	17	0.00	0		936.90
19	18	0.00	0		938.26
20	19	891,543.48	1,570	567.86	918.43
21	20	775,317.76	1,576	529.84	899.64
22	21	1,065,137.31	1,583	577.71	914.17
23	22	997,810.63	1,577	591.47	920.20
24	23	999,928.33	1,580	599.76	926.24
25	24	1,058,209.30	1,576	611.70	939.67
26	25	949,804.65	1,591	609.59	947.95
27	26	775,796.51	1,582	594.66	944.02
28	27	1,382,508.04	1,595	625.16
29	28	1,561,156.35	1,598	660.68
30	29	1,264,491.85	1,603	672.46
31	30	1,572,136.14	1,598	698.61
32	31	1,150,540.11	1,606	710.88
33	32	1,371,838.30	1,622	740.38
34	33	888,167.00	1,621	729.67
35	34	1,309,264.06	1,624	744.11
36	35	1,708,063.99	1,632	778.88
37	36	1,332,442.40	1,625	791.12
38	37	1,130,311.41	1,652	797.95
39	38	1,097,642.08	1,671	810.85
40	39	1,544,119.81	1,676	815.76
41	40	1,302,715.39	1,689	798.80
42	41	1,723,400.94	1,707	817.86
43	42	1,720,509.52	1,701	821.09
44	43	1,238,686.22	1,722	820.74
45	44	1,472,471.91	1,740	820.93
46	45	1,525,541.11	1,775	846.20
47	46	1,449,919.08	1,798	845.88
48	47	1,572,451.84	1,799	832.41
49	48	1,754,309.13	1,799	845.78
50	49	2,831,177.04	2,061	909.88
51	50	2,019,780.40	2,073	935.70
52	51	1,948,334.76	2,080	936.90
53	52	1,698,502.04	2,079	938.26
54	53	1,624,852.78	2,082	918.43
55	54	1,627,558.16	2,072	899.64
56	55	0.00	0	914.17
57	56	0.00	0	920.20
58	57	0.00	0	926.24
59	58	0.00	0	939.67
60	59	0.00	0	947.95
61	60	0.00	0	944.02
BigBeachBananas

Cell Formulas
Range	Formula
E2:E27	E2	=INDEX($D$2:$D$61,MAX(($A$2:$A$61)*($D$2:$D$61>0))-25):INDEX($D$2:$D$61,MAX(($A$2:$A$61)*($D$2:$D$61>0)))
G2	G2	=COUNTIF(C2:C61,">0")-11
D13:D61	D13	=IF(SUM(C2:C13)=0,"",SUM(B2:B13)/SUM(C2:C13))
Dynamic array formulas.

Yes, this is what I want but Fluff's answer is more comprehensible for me. Appreciate the time.

 

[Fluff]

Glad we could help & thanks for the feedback.

 

[awoohaw]

Yes, this is what I want but Fluff's answer is more comprehensible for me. Appreciate the time.

Yes, it is a very elegant solution. If your users all have 365 version functions it works well. If this is shared with other users that have earlier versions of excel it may not work.

 

[Fluff]

Your formula will not work with 2019 or earlier as it uses a spill range.