Auto Fill problems

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
titanium123Asked:
Who is Participating?
 
NorieConnect With a Mentor VBA ExpertCommented:
Sorry, forgot to complete the formula.

=INDEX(2:2,1,MATCH(A11,1:1,1) )
0
 
NorieVBA ExpertCommented:
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
 
FaustulusCommented:
Try this formula. You should be able to autofill it to the right.
=OFFSET($A$2,0,(-INT(COLUMN()/-3))-1)

Open in new window

0
Cloud Class® Course: Certified Penetration Testing

This CPTE Certified Penetration Testing Engineer course covers everything you need to know about becoming a Certified Penetration Testing Engineer. Career Path: Professional roles include Ethical Hackers, Security Consultants, System Administrators, and Chief Security Officers.

 
titanium123Author Commented:
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
 
titanium123Author Commented:
Hi Faustulus tried =OFFSET($A$2,0,(-INT(COLUMN()/-3))-1) just returns a #
0
 
titanium123Author Commented:
excellent thanks for your help saved me hours
0
 
FaustulusCommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.