Solved

# Auto Fill problems

Posted on 2013-02-05
286 Views
I have a sheet with values for every 15 minutes across a 24 hour period I want to break the 15 minute values down into 5 minute blocks across a 24 hour timeline broken down into 5 minute blocks relevant to the matching time period. Auto fill doesnt seem to be working as planned i can do the formulas manually but there is a lot to do. Maybe some sotrt of if statement may work but the autofill problem keeps causing isses
autofill.xlsx
0
Question by:titanium123
• 3
• 2
• 2

LVL 33

Expert Comment

ID: 38858106
Do you mean the formula in row 12?

If you do then try this formula, in column A and copy across.

=MATCH(A11,1:1,1)
0

LVL 14

Expert Comment

ID: 38858133
Try this formula. You should be able to autofill it to the right.
``````=OFFSET(\$A\$2,0,(-INT(COLUMN()/-3))-1)
``````
0

Author Comment

ID: 38858141
yes thats right the formula in row 12. I tried the match formula it counts in groups of 3 but continues to increase as below not return the value

=MATCH(A11,1:1,1)
1      1      1      2      2      2      3      3      3      4      4      4
0

Author Comment

ID: 38858165
Hi Faustulus tried =OFFSET(\$A\$2,0,(-INT(COLUMN()/-3))-1) just returns a #
0

LVL 33

Accepted Solution

Norie earned 500 total points
ID: 38858192
Sorry, forgot to complete the formula.

=INDEX(2:2,1,MATCH(A11,1:1,1) )
0

Author Comment

ID: 38858204
excellent thanks for your help saved me hours
0

LVL 14

Expert Comment

ID: 38858207
Yes, I was able to generate the response you got. The reason is the cell formatting. If you increase the column width to accommodate the result you will see the result instead of the pound signs. You can also format the number to Integer, so long as it fits the column width.
0

## Featured Post

### Suggested Solutions

This collection of functions covers all the normal rounding methods of just about any numeric value.
No matter the version of Windows you are using, you may have some problems with Windows Search running too slow or possibly not running at all. Before jumping into how you can solve this issue, just know there are many other viable alternative deskt…
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.