Require formula

[Ron99]

Hello,

I have data in rows

I need a max formula for the last 12 consecutive months.Formula should calculate value more than zero and the calculation should start from right.

Formula result should show For RON its 24 & Jude its 9.

	J	F	M	A	M	J	J	A	S	O	N	D	J	F	M	A	M	J	J	A	S	O	N	D	J	F	M	A	M	J	J	A	S	O	N	D
RON	12	2	0	8	0	0	0	0	6	2	3	8	9	4	8	0	0	0	0	0	0	0	0	0	1	6	8	9	24	0	0	1	3	6	1	0
JUDE	0	0	2	5	6	9	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	2	5	8	0	0	9	5	0	0	2	3

<tbody>
</tbody>

 

[Aladin Akyurek]

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH	AI	AJ	AK	AL
1			J	F	M	A	M	J	J	A	S	O	N	D	J	F	M	A	M	J	J	A	S	O	N	D	J	F	M	A	M	J	J	A	S	O	N	D
2	24	RON	12	2	0	8	0	0	0	0	6	2	3	8	9	4	8	0	0	0	0	0	0	0	0	0	1	6	8	9	24	0	0	1	3	6	1	0
3	9	JUDE	0	0	2	5	6	9	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	2	5	8	0	0	9	5	0	0	2	3
Sheet1

In A2 control+shift+enter, not just enter, and copy down:

=MAX(IF(COLUMN(C2:AL2)>=LARGE(IF(ISNUMBER(1/C2:AL2),COLUMN(C2:AL2)),MIN(12,COUNTIFS(C2:AL2,">0"))),IF(ISNUMBER(1/C2:AL2),C2:AL2)))

 

[Ron99]

Excellent!! that worked. With the same scenario I have one more requirement.

I need a formula for the max 12 month in this range

max(sum(C2:N2),SUM(D2:O2),SUM(E2:P2)..and so on

Is there a better formula As I have range for 5 years by month.

Thanks,
Ron

 

[Aladin Akyurek]

Excellent!! that worked.

Great.

With the same scenario I have one more requirement.

I need a formula for the max 12 month in this range

max(sum(C2:N2),SUM(D2:O2),SUM(E2:P2)..and so on

Is there a better formula As I have range for 5 years by month.

[…]

I don't understand the request neither the formula you provide as a description. Care to elaborate? Use a 3 months window instead of 12 for reasons of an easier explanation and layout.

 

[Tetra201]

... I need a formula for the max 12 month in this range

max(sum(C2:N2),SUM(D2:O2),SUM(E2:P2)..and so on

Is there a better formula As I have range for 5 years by month...

See if the following array formula (to be entered using Ctrl+Shift+Enter, not just Enter) works for you:

=MAX(SUBTOTAL(9,OFFSET($C2,,ROW(INDIRECT("1:"&COUNT($C2:$ABC2)-11))-1,1,12)))

 

[Peter_SSs]

For the original problem I think you could also use this standard-entry formula.
(If there is a row consisting of all 0 values this formula will return 0. If that is possible with your data and is a problem result for you, an amendment could be made)

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH	AI	AJ	AK	AL
1			J	F	M	A	M	J	J	A	S	O	N	D	J	F	M	A	M	J	J	A	S	O	N	D	J	F	M	A	M	J	J	A	S	O	N	D
2	24	RON	12	2	0	8	0	0	0	0	6	2	3	8	9	4	8	0	0	0	0	0	0	0	0	0	1	6	8	9	24	0	0	1	3	6	1	0
3	9	JUDE	0	0	2	5	6	9	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	2	5	8	0	0	9	5	0	0	2	3
Max