# How to increase cell numbers with an auto fill.

• ### Question

• I have two sheets. The first is a list of years in column A and data point in column b. There are 47 data points for each year and 65 years. I am trying to find the average for each year in column A. In the second sheet I have all 65 years across row 1. I would like to have the Max, Min, Average, and Median listed for each year. I am currently using this formula column 1 "=MAX(data!\$D2:\$D48)", column 2 "=MAX(data!\$D49:\$D96)". Is it possible to auto-fill the rest continuing the pattern of adding 47 to the row numbers?

Thanks!

• Edited by Tuesday, August 11, 2015 4:43 AM
Tuesday, August 11, 2015 4:42 AM

• If your formulas start in column B (the 2nd column), use

=MAX(OFFSET(data!\$D\$2:\$D\$48,47*(COLUMN()-2),0))

If your formulas start in column C (the 3rd column), use

=MAX(OFFSET(data!\$D\$2:\$D\$48,47*(COLUMN()-3),0))

Etc.

Regards, Hans Vogelaar (http://www.eileenslounge.com)

Tuesday, August 11, 2015 6:03 AM

### All replies

• If you want the formula to fill down:

=MAX(OFFSET(data!\$D\$2:\$D\$48,47*(ROW()-1),0))

If you want the formula to fill across:

=MAX(OFFSET(data!\$D\$2:\$D\$48,47*(COLUMN()-1),0))

You may have to adjust the -1 depending on where you start.

Regards, Hans Vogelaar (http://www.eileenslounge.com)

Tuesday, August 11, 2015 5:20 AM
• I really appreciate your timely response. I am hoping to "fill across." I am unfamiliar with the "offset" command and when I use that formula I get 225 as the max result when in fact 661 should be the max. If it helps, here are the first few years. Thanks again for all your help!
```DATE	TPCP
1948/01	134
1948/01	205
1948/01	167
1948/01	194
1948/02	119
1948/02	137
1948/02	149
1948/02	142
1948/03	15
1948/03	16
1948/03	18
1948/03	20
1948/04	126
1948/04	151
1948/04	161
1948/04	162
1948/05	222
1948/05	344
1948/05	586
1948/05	661
1948/06	154
1948/06	232
1948/06	231
1948/06	396
1948/07	130
1948/07	156
1948/07	239
1948/07	192
1948/08	35
1948/08	6
1948/08	35
1948/08	32
1948/09	8
1948/09	18
1948/09	48
1948/10	82
1948/10	66
1948/10	77
1948/10	62
1948/11	89
1948/11	109
1948/11	99
1948/11	125
1948/12	147
1948/12	156
1948/12	112
1948/12	165
1949/01	6
1949/01	20
1949/01	9
1949/01	14
1949/02	82
1949/02	126
1949/02	218
1949/02	103
1949/03	131
1949/03	93
1949/03	85
1949/03	121
1949/04	10
1949/04	12
1949/04	3
1949/04	6
1949/05	34
1949/05	56
1949/05	44
1949/05	48
1949/06	21
1949/06	5
1949/06	2
1949/06	5
1949/07	0
1949/07	0
1949/07	0
1949/07	1
1949/08	0
1949/08	7
1949/08	3
1949/08	5
1949/09	42
1949/09	93
1949/09	64
1949/09	54
1949/10	50
1949/10	42
1949/10	48
1949/10	89
1949/11	183
1949/11	199
1949/11	217
1949/11	222
1949/12	6
1949/12	48
1949/12	17
1949/12	26
1950/01	192
1950/01	211
1950/01	166
1950/01	227
1950/02	69
1950/02	129
1950/02	111
1950/02	148
1950/03	156
1950/03	168
1950/03	128
1950/03	192
1950/04	61
1950/04	119
1950/04	57
1950/04	57
1950/05	25
1950/05	35
1950/05	25
1950/05	53
1950/06	209
1950/06	195
1950/06	138
1950/06	217
1950/07	19
1950/07	14
1950/07	16
1950/07	33
1950/08	0
1950/08	4
1950/08	2
1950/08	0
1950/09	0
1950/09	7
1950/09	1
1950/09	1
1950/10	278
1950/10	335
1950/10	270
1950/10	305
1950/11	72
1950/11	134
1950/11	68
1950/11	73
1950/12	134
1950/12	173
1950/12	146
1950/12	138```

• Edited by Tuesday, August 11, 2015 5:34 AM
Tuesday, August 11, 2015 5:33 AM
• If your formulas start in column B (the 2nd column), use

=MAX(OFFSET(data!\$D\$2:\$D\$48,47*(COLUMN()-2),0))

If your formulas start in column C (the 3rd column), use

=MAX(OFFSET(data!\$D\$2:\$D\$48,47*(COLUMN()-3),0))

Etc.

Regards, Hans Vogelaar (http://www.eileenslounge.com)

Tuesday, August 11, 2015 6:03 AM