"How can I change the ranges on these functions?"

[Sikandar Ali]

Book2
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	Observation			I want to calculate the following indices from above Observation data
2	Date	Prec		Data	Statistics \Seasons	Mean (mm/ day)	SD (mm/ day)	Percentile (99th)	5-day max rainfall (mm)	% Wet days	Max dry spell length	Max wet spell length
3	1/1/1977	0
4	1/2/1977	0
5	1/3/1977	0		Observation	Annual	2.89977534	10.3149405	60.706	179.07	20%	68	5	offset	max
6	1/4/1977	0			Winter=(Oct-March)	0.90156044	3.87714426	22.0726	73.66	9%	63	4	2	5
7	1/5/1977	0			3month=(April-June)	2.96147253	8.39680101	36.703	90.17	22%	15	5	8	2
8	1/6/1977	0			3months(July-Sept)	6.79173913	17.3970642	79.5401	179.07	39%	10	5	5	2
9	1/7/1977	0
10	1/8/1977	0
11	1/9/1977	0
Sheet4

Cell Formulas
Range	Formula
F5	F5	=AVERAGE(B3:B367)
G5	G5	=STDEV.S(B3:B367)
H5	H5	=PERCENTILE($B$3:$B$367,0.99)
I5	I5	=AGGREGATE(14,6,SUBTOTAL(9,OFFSET($B$3,ROW($B$3:$B$363)-ROW($B$3),0,5)),1)
J5	J5	=COUNTIF(B3:B367,">.1")/365
K5	K5	=MAX(FREQUENCY(IF($B$3:$B$367<0.1,ROW($B$3:$B$367)),IF($B$3:$B$367>=0.1,ROW($B$3:$B$367))))
L5	L5	=MAX(FREQUENCY(IF($B$3:$B$367>=0.1,ROW($B$3:$B$367)),IF($B$3:$B$367<0.1,ROW($B$3:$B$367))))
F6:F8	F6	=SUMPRODUCT($B$3:$B$367,--(MOD(MONTH($A$3:$A$367)+M6,12)<=N6))/SUMPRODUCT(--(MOD(MONTH($A$3:$A$367)+M6,12)<=N6))
G6:G8	G6	=STDEV.S(IF(MOD(MONTH($A$3:$A$367)+M6,12)<=N6,$B$3:$B$367))
H6:H8	H6	=PERCENTILE(IF(MOD(MONTH($A$3:$A$367)+M6,12)<=N6,$B$3:$B$367),0.99)
I6:I8	I6	=AGGREGATE(14,6,IF(MOD(MONTH($A$3:$A$367)+M6,12)<=N6,SUBTOTAL(9,OFFSET($B$3,ROW($B$3:$B$363)-ROW($B$3),0,5))),1)
J6:J8	J6	=SUMPRODUCT(--($B$3:$B$367>=0.1),--(MOD(MONTH($A$3:$A$367)+M6,12)<=N6))/SUMPRODUCT(--(MOD(MONTH($A$3:$A$367)+M6,12)<=N6))
K6:K8	K6	=MAX(FREQUENCY(IF(($B$3:$B$367<0.1)*(MOD(MONTH($A$3:$A$367)+M6,12)<=N6),ROW($B$3:$B$367)),IF($B$3:$B$367>=0.1,ROW($B$3:$B$367))))
L6:L8	L6	=MAX(FREQUENCY(IF(($B$3:$B$367>=0.1)*(MOD(MONTH($A$3:$A$367)+M6,12)<=N6),ROW($B$3:$B$367)),IF($B$3:$B$367<0.1,ROW($B$3:$B$367))))
Press CTRL+SHIFT+ENTER to enter array formulas.

Dear experts,
I need your help to modify the ranges like winter from Oct-March to Oct-Feb and 3months from April-June to 4months March-June, remaining (July-Sep) will be same. So kindly help me in this regard, your kind assistance will be highly appreciated.

 

[Eric W]

The rules for the offset and max column are as follows:

Offset:
Convert the starting month to a number (October = 10), and subtract from 12. So M6 = 2.

Max:
The max column is the number of months you want to include in the range, minus 1. So Oct-Mar has 6 months (Oct, Nov, Dec, Jan, Feb, Mar), subtract 1 and you get 5, which is what N6 says. If you want 5 months (Oct-Feb), change N6 to 4.

You should be able to figure out the change to N7 you need to make.

 

[Sikandar Ali]

The rules for the offset and max column are as follows:

Offset:
Convert the starting month to a number (October = 10), and subtract from 12. So M6 = 2.

Max:
The max column is the number of months you want to include in the range, minus 1. So Oct-Mar has 6 months (Oct, Nov, Dec, Jan, Feb, Mar), subtract 1 and you get 5, which is what N6 says. If you want 5 months (Oct-Feb), change N6 to 4.

You should be able to figure out the change to N7 you need to make.Th

I got it and thank you very much for your fruitful response