Create offset range for use in UDF 1

[Mikeb1970]

Hi all,

i have a large list of numbers in column A, and would like to calculate an average in column B. This average period number will be entered in another sheet or userform, because it has to be dynamic (a period ranging from 5 to 200 periods should be possible). The reason why i wanna make this in a UDF is very simple, i need to make those calculations very often, and wanna get rid of the problem once and for all.

The biggest problem i have is the next one, how to set the offset using VBA, i know how to do it in excel

the function would look something like this

Function MA(Column, Period)

End Function

Where Column is the column where the data is placed, and period is the number of periods that should be used.

Letts say i enter =MA(A:A;12) in cell B45

I would like the cells A45:B56 to be summed and then divided by 12

Any help here is much appreciated.

With regards

Mike

 

[SkipVought]

Hi,

Try this...

Function Func1(rng As Range, iVal As Integer) As Single
    If iVal = 0 Then
        Func1 = 0
    Else
        Func1 = Application.Subtotal(9, rng) / iVal
    End If
End Function

Skip,

http://www.TheOfficeExperts.com

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[Mikeb1970]

Hi Skip,

first of all thanks for your answer,

but i may not have explained this correctly,

the first variable is only used to determine in which column the data is in, (A:A) or(B:B).....

Then depending on the row number where i insert the formula (example B46 would be row 46) i need to calculate the sum from the next n (n is second variable) , so when using the numbers i used in my post 12 it would calculate the sum of the cells A46:A56 (just saw i entered an error in first post ).

Then this number needs to be devided by n giving the average of those 12 numbers.

The part i can't figure out is how to make the range dynamic based on the n entry and the column.
Once i can get this to work i'm pretty sure i can move on and incorporate error catching (example no calc when not enough data is present in the range).
If not, i won't hesitate to ask you,

The formula you created calculates the standard deviation, which is also very helpful in another project i'm working on.

Hope you can help me

With regards

Mike

 

[SkipVought]

Function Func1(rng As Range, iVal As Integer) As Single
    If iVal = 0 Then
        Func1 = 0
    Else
        With rng
            Set rng2 = Range(Cells(.Row, .Column), Cells(.Row, .Column).Offset(iVal - 1, 0))
        End With
        Func1 = Application.Subtotal(9, rng2) / iVal
    End If
End Function

Hope I read your requirements correctly

Skip,

http://www.TheOfficeExperts.com

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[Mikeb1970]

Hi Skip,

can't thank you enough for your patience, but it still isn't quite doing what it want it to do, maybe i'll better explain with an example

A B
1
2
3 =Func1(A:A;4)
4
5
6
7
8
9 =Func1(A:A;8)
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30

The first entry (B3) should have 4.5 as a result, because it should sum 4 cells located in column A and then divide that result by 4 (the average)
The 4 cells are, the cell located in the same row, but in column A (therefore the first variable) and then downwards

so whe get A3-A4-A5-A6

The second variable is not only used to determine the division factor, but also for determening the offset.

The second (B9) entry should sum the following cells

A9-A10-A11-A12-A13-A14-A15-A16

this result is 100 and should then be divided by 8

giving 12.5 as a result.

I'm sorry if i didn't explain this very well in previous posts, but english isn't my mother tonghue, i'm trying to do the best i can.

thnx again for your help

Mike

 

[SkipVought]

You enter...

=Func1(A4,12)

will, starting from A4 average 12 consecutive cells.

Sorry that I did not explain how the function worked.

Skip,

http://www.TheOfficeExperts.com

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[Mikeb1970]

Hi Skip,

This works great, the only reason i didn't get it to work is because i kept entering (A:A) ranges.

I also understand how this works now, which is the most important thing off all.

Thank you very much

With regards

Mike

 

[Mikeb1970]

Hi all,

Just wanted to lett you know what happened with the UDF.

First off all, it works great,

but, yes there is a but

It is slow, really slow compared to using normal formulas, and i'm not talking about a short formula.

Considering naming the functions now, but still will also need some testing.

Anyway, wanna thank everyone that posted in this thread, and if anyone has some new ideas, they will be appreciated

Mike

 

[SkipVought]

Under what conditions are you using it?

How slow?

Skip,

http://www.TheOfficeExperts.com

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[Mikeb1970]

Hi,

Using at home on a windows XP pro machine, with office 2002 pro.

On the speed part, i tried getting some numbers on that, maybe better when i mail something to you, so you could look at it, made the function volatile and made it calculate 30000 values.

btw converted it to an addin, maybe this is also having some affect on it

With regards

Mike

 

[SkipVought]

send to

skip @ theofficeexperts.com

removeing the spaces, of course.

Skip,

http://www.TheOfficeExperts.com

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884