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.

 

[awoohaw]

Try this:

mr excel questions 13.xlsm
	A	B	C
1	183	50	6613
2	184	50	6420
3	102	50	6303
4	174	50	6503
5	32	50	6716
6	93	50	6841
7	66	50	6651
8	142	50	5417
9	227	50	5493
10	181	50	6511
11	218	0
12	191	0
13	59	0
14	19	0
15	243
16	49
17	224
18	197
19	234
20	130
21	81
22	36
23	241
24	82
25	61
26	164
27	121
28	33
29	151
30	222
31	173
32	124
33	43
34	146
35	79
36	23
37	181
38	62
39	120
40	89
41	28
42	184
43	195
44	146
45	96
46	159
47	68
48	142
49	195
50	220
51	39
52	175
53	107
54	84
55	30
56	43
57	222
58	211
59	11
60	215
61	60
62	154
63	159
64	107
65	248
66	107
67	176
68	130
69	52
70	205
71	133
72	226
73	85
74	162
75	116
76	206
77	134
78	128
79	119
80	59
81	177
82	125
83	22
84	215
85	157
86	211
87	193
88	19
89	177
90	24
91	85
92	101
93	50
94	118
95	246
96	25
97	218
98	63
99	86
100	205
101	21
102	230
103	117
104	15
105	241
106	181
107	211
108	131
109	17
110	69
111	207
112	194
113	225
114	105
115	239
116	10
117	50
118	173
119	44
120	118
121	98
122	153
123	130
124	56
125	140
126	25
127	56
128	168
129	174
130	35
131	237
132	184
133	195
134	159
135	248
136	39
137	103
138	93
139	45
140	83
141	71
142	185
143	240
144	70
145	24
146	170
147	210
148	191
149	110
150	13
151	101
152	52
153	86
154	248
155	243
156	230
157	135
158	82
159	239
160	16
161	100
162	60
163	97
164	211
165	17
166	211
167	58
168	198
169	143
170	241
171	168
172	166
173	157
174	173
175	56
176	112
177	67
178	46
179	208
180	25
181	235
182	23
183	54
184	114
185	247
186	132
187	156
188	210
189	77
190	28
191	50
192	72
193	60
194	13
195	138
196	217
197	192
198	173
199	163
200	203
201	65
202	193
203	171
204	218
205	18
206	170
207	206
208	241
209	12
210	227
211	157
212	202
213	59
214	119
215	36
216	220
217	30
218	103
219	186
220	136
221	78
222	203
223	179
224	182
225	182
226	245
227	53
228	129
229	58
230	250
231	214
232	84
233	77
234	92
235	118
236	195
237	90
238	87
239	35
240	213
241	58
242	32
243	137
244	217
245	76
246	229
247	152
248	214
249	21
250	47
251	132
252	37
253	17
254	206
255	205
256	71
257	184
258	76
259	170
260	130
261	69
262	227
263	99
264	75
265	138
266	172
267	133
268	147
269	245
270	217
271	73
272	228
273	169
274	138
275	49
276	20
277	155
278	138
279	171
280	82
281	17
282	81
283	221
284	244
285	186
286	200
287	111
288	203
289	233
290	151
291	129
292	20
293	79
294	126
295	51
296	200
297	250
298	140
299	160
300	66
301	48
302	159
303	34
304	83
305	226
306	173
307	146
308	78
309	96
310	159
311	223
312	196
313	229
314	98
315	17
316	170
317	114
318	162
319	91
320	236
321	213
322	201
323	174
324	159
325	180
326	153
327	176
328	90
329	58
330	199
331	205
332	91
333	77
334	173
335	57
336	68
337	142
338	21
339	62
340	219
341	132
342	81
343	198
344	40
345	239
346	40
347	49
348	162
349	64
350	190
351	47
352	74
353	138
354	101
355	197
356	17
357	23
358	181
359	70
360	124
361	106
362	122
363	54
364	33
365	173
366	63
367	55
368	48
369	241
370	187
371	190
372	248
373	246
374	22
375	137
376	69
377	33
378	117
379	62
380	48
381	14
382	49
383	246
384	211
385	155
386	140
387	181
388	64
389	88
390	103
391	23
392	144
393	75
394	219
395	48
396	185
397	55
398	40
399	52
400	99
401	85
402	86
403	126
404	36
405	76
406	84
407	29
408	201
409	142
410	186
411	146
412	45
413	136
414	51
415	156
416	171
417	174
418	179
419	186
420	33
421	220
422	30
423	136
424	156
425	131
426	206
427	179
428	13
429	101
430	243
431	69
432	15
433	20
434	17
435	60
436	63
437	22
438	178
439	166
440	234
441	44
442	60
443	128
444	57
445	46
446	155
447	110
448	64
449	80
450	162
451	199
452	33
453	78
454	212
455	128
456	140
457	240
458	108
459	33
460	222
461	232
462	141
463	125
464	145
465	23
466	155
467	146
468	69
469	47
470	62
471	160
472	237
473	213
474	185
475	193
476	172
477	138
478	195
479	106
480	28
481	80
482	133
483	89
484	214
485	236
486	155
487	47
488	128
489	13
490	202
491	30
492	149
493	45
494	157
495	25
496	199
497	176
498	150
499	72
500	46
Sheet9

Cell Formulas
Range	Formula
C1:C10	C1	=SUM(INDEX($A$1:$A$500,((ROW(A1)-1)*50)+1,1):INDEX($A$1:$A$500,((ROW(A1))*50),1))
B1:B14	B1	=COUNT(INDEX($A$1:$A$500,((ROW(A1)-1)*50)+1,1):INDEX($A$1:$A$500,((ROW(A1))*50),1))

 

[Fluff]

I just forgot to translate

Not a problem.

 

[awoohaw]

Here are the formulas for each half of the index function being used to obtain cell references:

=(INDEX($A$1:$A$500,((ROW(A1)-1)*50)+1,1))

=INDEX($A$1:$A$500,((ROW(A1))*50),1)

Put a colon between the two and it behaves as a a cell/range reference:

=INDEX($A$1:$A$500,((ROW(A1)-1)*50)+1,1):INDEX($A$1:$A$500,((ROW(A1))*50),1)

So, to get Maximum:

=MAX(INDEX($A$1:$A$500,((ROW(A1)-1)*50)+1,1):INDEX($A$1:$A$500,((ROW(A1))*50),1))

 

[Fluff]

My take on this

=MAX(INDEX(A:A,ROWS(A$1:A1)*100-99):INDEX(A:A,ROWS(A$1:A1)*100-50))

 

[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.

I've just arrived home, so I'll be able to illustrate my whole problem with a picture, maybe that will help you understand.

I have a sinusoidal graph (see the picture) which corresponds to the position x(t) in meters of a mass (column B) as a function of time (t) in seconds (column A). The mass oscillates from right to left and its position therefore alternates between negative and positive.

I need to know the average amplitude of the movement. To do this I need to know the extremums of my function. So I had the idea to look for my extremum each time on a given interval which differs from +50 each time.

In red, I look for the minimum of my column B of the intervals B1:B50 then B100:B150 then B200:B250 and so on, the results are reported in the column "Min".

In green, I look for the maximum in the remaining intervals, B50:B100 then B150:B200 then B250:B300 and so on. The results are reported in the "Max" column.

The idea is that each new row in the "Min" column calculates the minimum of the next interval (so B300:B350 then B400:B450 ect...) and that each new row in the "Max" column does the same for the remaining intervals (so B350:B400 then B450:B500 ect...)

 

[Nathaaan30]

My take on this

=MAX(INDEX(A:A,ROWS(A$1:A1)*100-99):INDEX(A:A,ROWS(A$1:A1)*100-50))

So I tried to use this by adapting the formula to column B but the software gives me an error on the first "$B$1:$B$500".

Here are the formulas for each half of the index function being used to obtain cell references:

=(INDEX($A$1:$A$500,((ROW(A1)-1)*50)+1,1))

=INDEX($A$1:$A$500,((ROW(A1))*50),1)

Put a colon between the two and it behaves as a a cell/range reference:

=INDEX($A$1:$A$500,((ROW(A1)-1)*50)+1,1):INDEX($A$1:$A$500,((ROW(A1))*50),1)

So, to get Maximum:

=MAX(INDEX($A$1:$A$500,((ROW(A1)-1)*50)+1,1):INDEX($A$1:$A$500,((ROW(A1))*50),1))

Same here, I have an error that I don't know how to solve

 

[Fluff]

What error did you get?

 

[Nathaaan30]

What error did you get?

It doesn't do anything for me, I just get the error message that the formula doesn't fit and then the software highlights that area (see image).

Note that I tried to replace the "B3" with a simple "B" but it didn't change anything.

 

[Fluff]

Ok, you need to change the commas to semi-colons

 

[Nathaaan30]

Ok, you need to change the commas to semi-colons

Ok I did it, now I have an error #NAME? Error due to an invalid name.

I tried again to swap the "B" with "B3" to select only the numeric values but it didn't change anything.