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]

what happened when you tried this formula in cell B1:

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

?

If you put it in other rows, you must subract the appropriate row number, so if you put it in B3, the row statement should be ROW(A3)-3.

 

[Fluff]

What language version do you use?

 

[awoohaw]

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

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

what was the error? Did you put each of the 4 formulas in cells to test them out?

 

[awoohaw]

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")

 

[Nathaaan30]

what happened when you tried this formula in cell B1:

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

?

If you put it in other rows, you must subract the appropriate row number, so if you put it in B3, the row statement should be ROW(A3)-3.

I tried that by modifying according to your indication the values (see image), the values indeed start at B3 so I modified as you indicated.

I still have an error and the software highlights the area that causes it problems (see image).

 

[Nathaaan30]

What language version do you use?

If you mean the language of my software, it is in French but most of the functions keep the same name as with their English versions.

 

[awoohaw]

I tried that by modifying according to your indication the values (see image), the values indeed start at B3 so I modified as you indicated.

I still have an error and the software highlights the area that causes it problems (see image).

I do not see png3 what post number is it in?

 

[Fluff]

it is in French

In that case try

=MAX(INDEX(A:A;LIGNES(A$1:A1)*100-99):INDEX(A:A;LIGNES(A$1:A1)*100-50))

 

[Nathaaan30]

In that case try

=MAX(INDEX(A:A;LIGNES(A$1:A1)*100-99):INDEX(A:A;LIGNES(A$1:A1)*100-50))

Good news, there are no more mistakes, I have figures! However, they are not the right ones. I had started to type the formulas by hand for the first cells (the cells with the little green triangle) but the values don't match:

 

[Nathaaan30]

I do not see png3 what post number is it in?

The software highlights the first "$B$500," I replaced the comma with a semicolon and now I get an invalid name error (#NAME)