Auto fill - how to increment by an interval?

  • Thread starter Thread starter P. Lui
  • Start date Start date
P

P. Lui

Hi,

I would like to find the monthly average of some daily data. When I
tried to use autofill, the references to the cells will only increment
by one. How can I have it increment by 31 or 30 etc. depending on the
month?

I've searched on google and found that I'd need to use the offset
function somehow. I can't quite figure out how to have the correct
incrementation. The reference cell is still referenced by one in the
offset function. How do I make it work in my case?


Patrick
 
Hi
could you provide some more details. e.g. how your spreadsheet is
strucutred (post some sample rows as plain text - no attachments
please)
 
There's a way to do it with a custom fill series. I've never done i
myself, but there's instructions in the Excel help screens
 
To increment the autofill by months using code......
With Sheets("Sheet1")
..Range("B1").AutoFill .Range("B1:B20"), xlFillMonths
End With

To increment the autofill by months manually......
1) Select the cell which contains the starting month.
2) Hover your mouse over the bottom right corner of the starting month cell
until the cursor turns into a cross.
3) Right click and keep the mouse pressed down and drag over the required
range
4) Release the right click and from the menu select "Fill months"

--
XL2002
Regards

William

(e-mail address removed)

| Hi,
|
| I would like to find the monthly average of some daily data. When I
| tried to use autofill, the references to the cells will only increment
| by one. How can I have it increment by 31 or 30 etc. depending on the
| month?
|
| I've searched on google and found that I'd need to use the offset
| function somehow. I can't quite figure out how to have the correct
| incrementation. The reference cell is still referenced by one in the
| offset function. How do I make it work in my case?
|
|
| Patrick
 
Hi Frank,

Thanks for the reply. Here's a sample of the structure of my
spreadsheets:

Year|Month|Day|Temperature
70|01|01|-10
70|01|02|-12
.....
70|02|01|-13
.....
99|12|31|-22

That's from one sheet. I want to find the monthly average for each
month on another sheet. For example:

Year|Month|Temperature
70|01|-14.15
.....
etc.

The problem is once I have one cell filled with the formula for
average, if I want to auto fill it, the cell would only advance by one
(ex. if I'm calculating the avg. for January and try auto-filling for
Feb., it'll start averaging on Jan. 2nd, not Feb. 1st. ) I hope I'm
making it clearer instead of confusing you even more.

Thanks,

Patrick
 
I tried the following code:

Sub STD_Mean()
Dim offset As Integer
Dim year As Integer


For year = 70 To 99
offset = year - 70
Cells(2 + offset, 6).Formula =
"=AVERAGE(Main!Cells(6+95*offset,24):Main!Cells(36+95*offset,24))"
Cells(3 + offset, 6).Formula =
"=AVERAGE(Main!Cells(37+95*offset,24):Main!Cells(64+95*offset,24))"
Cells(4 + offset, 6).Formula =
"=AVERAGE(Main!Cells(70+95*offset,24):Main!Cells(100+95*offset,24))"

Next year

End Sub

But it gives me the #NAME? error on the spread sheet. What am I doing
wrong?

Patrick

[snip]
 
Hi
no wonder as you post
Main!Cells(6+95*offset,24) as a string and Excel does not know a name
Main!Cells
 
Hi,

I think in my case, I can't use Pivot Table because the field from
sheet one is actually called Date (ex. 01/01/1970) instead of in 3
seperate columns as I've stated.

So how can I modify the code to make it work?


Patrick


Frank Kabel said:
Hi
no wonder as you post
Main!Cells(6+95*offset,24) as a string and Excel does not know a name
Main!Cells

--
Regards
Frank Kabel
Frankfurt, Germany

P. Lui said:
I tried the following code:

Sub STD_Mean()
Dim offset As Integer
Dim year As Integer


For year = 70 To 99
offset = year - 70
Cells(2 + offset, 6).Formula =
"=AVERAGE(Main!Cells(6+95*offset,24):Main!Cells(36+95*offset,24))"
Cells(3 + offset, 6).Formula =
"=AVERAGE(Main!Cells(37+95*offset,24):Main!Cells(64+95*offset,24))"
Cells(4 + offset, 6).Formula =
"=AVERAGE(Main!Cells(70+95*offset,24):Main!Cells(100+95*offset,24))"

Next year

End Sub

But it gives me the #NAME? error on the spread sheet. What am I doing
wrong?

Patrick

[snip]
Click to expand...
Click to expand...
 
Patrick:

Two things:

If you have a date in A1, then formulae for B1,C1,D1 as follows wil
separate out the parts of the date.

B1: =day(A1)
C1: =month(A1)
D1: =year(A1)

Second point: it is never a great idea to use a reserved word (in thi
case OFFSET) as a variable name. I tend to use Setoff...

al
 
AlfD said:
Patrick:

Two things:

If you have a date in A1, then formulae for B1,C1,D1 as follows will
separate out the parts of the date.

B1: =day(A1)
C1: =month(A1)
D1: =year(A1)

Second point: it is never a great idea to use a reserved word (in this
case OFFSET) as a variable name. I tend to use Setoff...

alf
Click to expand...


Alf:

Thanks. Points taken. About the code, I'm very much a newbie. Could
you tell me how to make this work?

Sub STD_Mean()
Dim setoff As Integer
Dim year As Integer


For year = 70 To 99
setoff = year - 70
Cells(2 + setoff, 6).Value =
"=AVERAGE(Main!Cells(6+95*setoff,24).Value:Main!Cells(36+95*setoff,24).Value)"
Cells(3 + setoff, 6).Value =
"=AVERAGE(Main!Cells(37+95*setoff,24).Value:Main!Cells(64+95*setoff,24).Value)"
Cells(4 + setoff, 6).Value =
"=AVERAGE(Main!Cells(70+95*setoff,24).Value:Main!Cells(100+95*setoff,24).Value)"

Next year

End Sub


Patrick
 
Hi!

Cells is a VBA code, not an Excel spreadsheet function/attribute or
whatever. Your formula needs "ordinary addresses" in it, either A1
style or R1C1 style.

Alf
 
AlfD said:
Hi!

Cells is a VBA code, not an Excel spreadsheet function/attribute or
whatever. Your formula needs "ordinary addresses" in it, either A1
style or R1C1 style.

Alf
Click to expand...

Hi Alf,

That's where I wrote the code. I wrote it in a module in VBA. Shouldn't it work?

Patrick
 
Hi!

Yes: you wrote it in a module. But, under your instructions, the modul
then wrote it in the worksheet.

That's what
Cells(2 + setoff, 6).Value =
"=AVERAGE(Main!Cells(6+95*setoff,24).Value:Main!Cells(36+95*setoff,24).Value)"
does.

So now the worksheet processes this inserted formula and says "What i
this strange name 'Cells'?"

You must give instructions, in the module, for it to put workshee
functions in the worksheet cells. (If that is the way you choose t
solve your problem).

Al
 
You must log in or register to reply here.

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Incrementing values 2
How do I fill adjacent cells with incrementing numbers 2
Using macro recorder increment cell by one? 4
INCREMENT LINKED CELLS IN A CHECKBOXES? 1
Auto Fill Defaults 3
Increment cell prefix 2
How do you force a copy to increment by 1 in Excel? 3
suspend auto increment 6

Back
Top