none
How to increase cell numbers with an auto fill. RRS feed

  • 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 Beenlouis Tuesday, August 11, 2015 4:43 AM
    Tuesday, August 11, 2015 4:42 AM

Answers

  • 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 Beenlouis 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