Increment cells in a specific way

[Nathaaan30]

Hello, I have a fairly specific question and not knowing how to formulate it, I did not find an answer on the internet.

Let's take a B1 cell, I want in this box B1 to have the maximum value among the values of A1:A50, so I write =MAX(A1:A50).

Now in my next box (B2), this time I want to have the maximum value of boxes A100:A150.

The problem is that by using autofill, the software writes =MAX(A2:A51) and not =MAX(A100:A150).

I tried to "fix" the dozens and units of my colones with a $ (with =MAX(A0$1:A0$50) but it doesn't work, so I rely on you.

Thank you in advance.

 

[Nathaaan30]

Here is a worksheet with every other row being MIN or MAX, I stopped the copy at 250 rows, but you can take it for as many rows as you need.

mr excel questions 13.xlsm
	A	B	C	D	E	F	G
1	76	50	14	MIN	$A$1	$A$50
2	129	50	245	MAX	$A$51	$A$100
3	163	50	23	MIN	$A$101	$A$150
4	106	50	250	MAX	$A$151	$A$200
5	19	50	14	MIN	$A$201	$A$250
6	249	50	249	MAX	$A$251	$A$300
7	227	50	10	MIN	$A$301	$A$350
8	79	50	248	MAX	$A$351	$A$400
9	139	50	12	MIN	$A$401	$A$450
10	235	50	250	MAX	$A$451	$A$500
11	36	50	16	MIN	$A$501	$A$550
12	26	50	241	MAX	$A$551	$A$600
13	83	50	11	MIN	$A$601	$A$650
14	188	50	241	MAX	$A$651	$A$700
15	216	50	17	MIN	$A$701	$A$750
16	14	50	242	MAX	$A$751	$A$800
17	217	50	16	MIN	$A$801	$A$850
18	160	50	248	MAX	$A$851	$A$900
19	244	50	17	MIN	$A$901	$A$950
20	164	50	250	MAX	$A$951	$A$1000
21	165			MIN
22	207			MAX
23	98			MIN
24	25			MAX
25	176			MIN
26	156			MAX
27	112			MIN
28	132			MAX
29	96			MIN
30	104			MAX
31	166			MIN
32	138			MAX
33	193			MIN
34	109			MAX
35	108			MIN
36	231			MAX
37	248			MIN
38	144			MAX
39	80			MIN
40	147			MAX
41	237			MIN
42	188			MAX
43	151			MIN
44	126			MAX
45	233			MIN
46	42			MAX
47	65			MIN
48	130			MAX
49	100			MIN
50	200			MAX
51	106			MIN
52	66			MAX
53	171			MIN
54	93			MAX
55	31			MIN
56	91			MAX
57	245			MIN
58	71			MAX
59	46			MIN
60	180			MAX
61	202			MIN
62	223			MAX
63	25			MIN
64	39			MAX
65	90			MIN
66	139			MAX
67	196			MIN
68	81			MAX
69	234			MIN
70	139			MAX
71	96			MIN
72	200			MAX
73	223			MIN
74	165			MAX
75	143			MIN
76	161			MAX
77	76			MIN
78	211			MAX
79	13			MIN
80	208			MAX
81	64			MIN
82	178			MAX
83	161			MIN
84	87			MAX
85	211			MIN
86	209			MAX
87	107			MIN
88	141			MAX
89	24			MIN
90	25			MAX
91	106			MIN
92	180			MAX
93	162			MIN
94	240			MAX
95	175			MIN
96	102			MAX
97	235			MIN
98	214			MAX
99	194			MIN
100	221			MAX
101	191			MIN
102	218			MAX
103	101			MIN
104	69			MAX
105	219			MIN
106	121			MAX
107	34			MIN
108	172			MAX
109	137			MIN
110	174			MAX
111	129			MIN
112	239			MAX
113	23			MIN
114	54			MAX
115	153			MIN
116	229			MAX
117	98			MIN
118	209			MAX
119	113			MIN
120	64			MAX
121	189			MIN
122	217			MAX
123	207			MIN
124	222			MAX
125	229			MIN
126	148			MAX
127	119			MIN
128	226			MAX
129	244			MIN
130	147			MAX
131	216			MIN
132	129			MAX
133	208			MIN
134	70			MAX
135	106			MIN
136	151			MAX
137	79			MIN
138	223			MAX
139	164			MIN
140	205			MAX
141	130			MIN
142	191			MAX
143	199			MIN
144	97			MAX
145	117			MIN
146	46			MAX
147	40			MIN
148	34			MAX
149	183			MIN
150	206			MAX
151	167			MIN
152	87			MAX
153	227			MIN
154	102			MAX
155	140			MIN
156	174			MAX
157	63			MIN
158	147			MAX
159	250			MIN
160	139			MAX
161	65			MIN
162	142			MAX
163	162			MIN
164	175			MAX
165	149			MIN
166	120			MAX
167	120			MIN
168	175			MAX
169	241			MIN
170	24			MAX
171	132			MIN
172	214			MAX
173	162			MIN
174	160			MAX
175	178			MIN
176	82			MAX
177	18			MIN
178	84			MAX
179	242			MIN
180	73			MAX
181	48			MIN
182	115			MAX
183	35			MIN
184	168			MAX
185	145			MIN
186	116			MAX
187	120			MIN
188	154			MAX
189	244			MIN
190	224			MAX
191	234			MIN
192	143			MAX
193	51			MIN
194	140			MAX
195	71			MIN
196	220			MAX
197	107			MIN
198	199			MAX
199	145			MIN
200	81			MAX
201	186			MIN
202	77			MAX
203	238			MIN
204	249			MAX
205	23			MIN
206	44			MAX
207	24			MIN
208	133			MAX
209	178			MIN
210	107			MAX
211	187			MIN
212	250			MAX
213	197			MIN
214	66			MAX
215	198			MIN
216	126			MAX
217	178			MIN
218	200			MAX
219	171			MIN
220	30			MAX
221	121			MIN
222	51			MAX
223	96			MIN
224	41			MAX
225	170			MIN
226	229			MAX
227	82			MIN
228	43			MAX
229	234			MIN
230	97			MAX
231	44			MIN
232	15			MAX
233	191			MIN
234	240			MAX
235	86			MIN
236	92			MAX
237	57			MIN
238	129			MAX
239	188			MIN
240	219			MAX
241	229			MIN
242	210			MAX
243	167			MIN
244	229			MAX
245	14			MIN
246	73			MAX
247	60			MIN
248	218			MAX
249	86			MIN
250	139			MAX
Sheet9

Cell Formulas
Range	Formula
B1:B20	B1	=COUNT(INDEX($A$1:$A$1000,((ROW(A1)-1)*50)+1,1):INDEX($A$1:$A$1000,((ROW(A1))*50),1))
C1:C20	C1	=IF(ISEVEN(ROW(A1)-1),MIN(INDEX($A$1:$A$1000,((ROW(A1)-1)*50)+1,1):INDEX($A$1:$A$1000,((ROW(A1))*50),1)), MAX(INDEX($A$1:$A$1000,((ROW(A1)-1)*50)+1,1):INDEX($A$1:$A$1000,((ROW(A1))*50),1)))
E1:E20	E1	=CELL("address",(INDEX($A$1:$A$1000,((ROW(A1)-1)*50)+1,1)))
F1:F20	F1	=CELL("address",INDEX($A$1:$A$1000,((ROW(A1))*50),1))
D1:D250	D1	=IF(ISEVEN(ROW(A1)-1),"MIN","MAX")

From what I see on your document, this is more or less what I want to achieve, the only difference being that the values are in column B (from B3). I will try to transpose this to my document but I still wonder if there is a way to avoid using so many columns.

 

[awoohaw]

Here it is according to your images:
It is only 250 rows but the formula is for 1000.
I've also changed the formula to an aggregate function, which is less typing.
But it is still MIN and MAX formulas. (Odd Rows are MIN, Even Rows are MAX)

mr excel questions 13.xlsm
	A	B	C
1		Masse A
2	t	x(t)
3	94	13	MIN
4	215	441	MAX
5	164	29	MIN
6	13	441	MAX
7	81	15	MIN
8	279	431	MAX
9	398	17	MIN
10	166	450	MAX
11	261	15	MIN
12	388	441	MAX
13	79	16	MIN
14	291	445	MAX
15	239	11	MIN
16	14	448	MAX
17	41	24	MIN
18	331	439	MAX
19	43	11	MIN
20	127	428	MAX
21	158
22	279
23	358
24	190
25	99
26	370
27	25
28	206
29	277
30	195
31	205
32	414
33	171
34	91
35	19
36	152
37	417
38	87
39	33
40	227
41	35
42	252
43	272
44	250
45	187
46	101
47	144
48	217
49	290
50	399
51	161
52	111
53	424
54	315
55	64
56	397
57	358
58	162
59	352
60	208
61	70
62	423
63	426
64	203
65	246
66	351
67	406
68	339
69	55
70	437
71	95
72	336
73	214
74	247
75	435
76	305
77	441
78	187
79	156
80	420
81	382
82	228
83	160
84	418
85	356
86	154
87	305
88	167
89	109
90	102
91	249
92	257
93	73
94	421
95	159
96	306
97	149
98	375
99	330
100	387
101	196
102	49
103	387
104	138
105	388
106	446
107	29
108	54
109	384
110	375
111	409
112	392
113	304
114	53
115	41
116	227
117	413
118	145
119	254
120	338
121	340
122	69
123	424
124	148
125	326
126	346
127	56
128	35
129	51
130	49
131	420
132	353
133	86
134	348
135	264
136	169
137	112
138	240
139	53
140	313
141	330
142	447
143	147
144	95
145	240
146	381
147	315
148	126
149	231
150	288
151	176
152	336
153	55
154	74
155	318
156	374
157	24
158	176
159	31
160	58
161	370
162	118
163	171
164	233
165	259
166	143
167	44
168	403
169	131
170	86
171	30
172	339
173	344
174	170
175	386
176	379
177	344
178	308
179	111
180	297
181	441
182	10
183	341
184	321
185	197
186	29
187	40
188	430
189	235
190	87
191	36
192	96
193	437
194	289
195	24
196	392
197	401
198	365
199	397
200	362
201	134
202	95
203	313
204	241
205	201
206	310
207	29
208	193
209	428
210	184
211	361
212	224
213	308
214	50
215	210
216	139
217	226
218	371
219	15
220	183
221	104
222	332
223	145
224	404
225	237
226	186
227	427
228	278
229	98
230	155
231	30
232	200
233	261
234	379
235	123
236	282
237	326
238	448
239	87
240	245
241	300
242	122
243	408
244	161
245	340
246	195
247	371
248	235
249	133
250	435
251	192
252	186
Sheet9

Cell Formulas
Range	Formula
A3:A1002	A3	=RANDARRAY(1000,1,10,450,1)
B3:B20	B3	=AGGREGATE(IF(ISEVEN(ROW(B3)),4,5),4, INDEX($A$3:$A$1002,((ROW(B3)-3)*50)+1,1):INDEX($A$3:$A$1002,((ROW(B3)-2)*50),1) )
C3:C20	C3	=IF(ISEVEN(ROW(A3)-1),"MIN","MAX")
Dynamic array formulas.

 

[Fluff]

Can you post some sample data?

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

The formula I supplied works for me.

Fluff.xlsm
	A	B	C	D	E
1
2
3		183		243	243
4		184		248	248
5		102		250	250
6		174			239
7		32			243
8		93			72
9		66			0
10		142			0
11		227			0
12		181
13		218
14		191
15		59
16		19
17		243
18		49
19		224
20		197
21		234
22		130
23		81
24		36
25		241
26		82
27		61
28		164
29		121
30		33
31		151
32		222
33		173
34		124
35		43
36		146
37		79
38		23
39		181
40		62
41		120
42		89
43		28
44		184
45		195
46		146
47		96
48		159
49		68
50		142
51		195
52		220
53		39
54		175
55		107
56		84
57		30
58		43
59		222
60		211
61		11
62		215
63		60
64		154
65		159
66		107
67		248
68		107
69		176
70		130
71		52
72		205
73		133
74		226
75		85
76		162
77		116
78		206
79		134
80		128
81		119
82		59
83		177
84		125
85		22
86		215
87		157
88		211
89		193
90		19
91		177
92		24
93		85
94		101
95		50
96		118
97		246
98		25
99		218
100		63
101		86
102		205
103		21
104		230
105		117
106		15
107		241
108		181
109		211
110		131
111		17
112		69
113		207
114		194
115		225
116		105
117		239
118		10
119		50
120		173
121		44
122		118
123		98
124		153
125		130
126		56
127		140
128		25
129		56
130		168
131		174
132		35
133		237
134		184
135		195
136		159
137		248
138		39
139		103
140		93
141		45
142		83
143		71
144		185
145		240
146		70
147		24
148		170
149		210
150		191
151		110
152		13
153		101
154		52
155		86
156		248
157		243
158		230
159		135
160		82
161		239
162		16
163		100
164		60
165		97
166		211
167		17
168		211
169		58
170		198
171		143
172		241
173		168
174		166
175		157
176		173
177		56
178		112
179		67
180		46
181		208
182		25
183		235
184		23
185		54
186		114
187		247
188		132
189		156
190		210
191		77
192		28
193		50
194		72
195		60
196		13
197		138
198		217
199		192
200		173
201		163
202		203
203		65
204		193
205		171
206		218
207		18
208		170
209		206
210		241
211		12
212		227
213		157
214		202
215		59
216		119
217		36
218		220
219		30
220		103
221		186
222		136
223		78
224		203
225		179
226		182
227		182
228		245
229		53
230		129
231		58
232		250
233		214
234		84
235		77
236		92
237		118
238		195
239		90
240		87
241		35
242		213
243		58
244		32
245		137
246		217
247		76
248		229
249		152
250		214
Main

Cell Formulas
Range	Formula
D3	D3	=MAX(B1:B50)
D4	D4	=MAX(B101:B150)
D5	D5	=MAX(B201:B250)
E3:E11	E3	=MAX(INDEX(B:B,ROWS(B$3:B3)*100-99):INDEX(B:B,ROWS(B$3:B3)*100-50))