Fun with FUNCTIONS!!!

[Factor3]

So I am trying to create a Function (but Macro would work too) that works like so:

1) The user enters into the formula box "=moVital_Stats(" and then selects a range of returns... let's say "B4:B31"

With that range, I want to do the following:

Function moVS(rng As Range)
Dim rng As Range'This is where the range is input by the user
cell As Range
std As Long, meanR As Long, GeomMean As Long, varR As Long
Total As Double, RowCount As Double
Total = 0
RowCount = rng.Count 'This counts the rows, which number gets used in 'the Geometric Mean calculation
For Each cell In rng
Total = (cell + 1) * Total
Next cell
GeomMean = Total ^ (12 / RowCount) - 1 'This calculates the Geometric 'Mean return
meanR = Application.Average(rng) * 12 'This calculates the average return
varR = Application.Var(rng) * 12 'This calculates the variance
std = Application.sqrt(varR) 'This calculates the Standard Deviation USING 'the variance
'In a perfect world I would then like the output to be one row on top of 'the other, GeomMean, meanR, & std... could I actually use the offset 'property for the output??
End Function

The weird part, is that I can't even begin the debugging process, VBE won't let me 'step in' so I thought it was pretty bizarre... is that because it is a function?

Thanks,

Benjamin

[Charlize]

Try this macro (no function).Sub moVS()
Dim rng As Range
Dim cell As Range
Dim std As Long, meanR As Long, GeomMean As Long, varR As Long
Dim Total As Double, RowCount As Double
Set rng = Application.InputBox("Give range", "Give range", , , , , , 8)
Total = 0
RowCount = rng.Count 'This counts the rows, which number gets used in 'the Geometric Mean calculation
For Each cell In rng
Total = (cell.Value + 1) * Total
Next cell
GeomMean = Total ^ (12 / RowCount) - 1 'This calculates the Geometric 'Mean return
meanR = Application.WorksheetFunction.Average(rng) * 12 'This calculates the average return
varR = Application.WorksheetFunction.Var(rng) * 12 'This calculates the variance
std = Application.WorksheetFunction.DevSq(varR) 'This calculates the Standard Deviation USING 'the variance
MsgBox "GeomMean : " & GeomMean & vbCrLf & _
"meanR : " & meanR & vbCrLf & _
"varR : " & varR & vbCrLf & _
"std : " & std
'In a perfect world I would then like the output to be one row on top of 'the other, GeomMean, meanR, & std... could I actually use the offset 'property for the output??
End SubBeware that Total always will be zero because you multiply by zero.

Charlize

[mdmackillop]

If you do want a function in the form
=movs(D6: D16,"M")

Option Explicit
Option Compare Text

Function moVS(rng As Range, typ As String)
Dim cell As Range
Dim std As Long, meanR As Long, GeomMean As Long, varR As Long
Dim Total As Double, RowCount As Double
Total = 0
RowCount = rng.Count 'This counts the rows, which number gets used in 'the Geometric Mean calculation
For Each cell In rng
Total = (cell + 1) * Total
Next cell

GeomMean = Total ^ (12 / RowCount) - 1 'This calculates the Geometric 'Mean return
meanR = Application.Average(rng) * 12 'This calculates the average return
varR = Application.Var(rng) * 12 'This calculates the variance
std = varR ^ 0.5 'This calculates the Standard Deviation USING 'the variance
'In a perfect world I would then like the output to be one row on top of 'the other, GeomMean, meanR, & std... could I actually use the offset 'property for the output??

Select Case typ
Case "G"
moVS = GeomMean
Case "M"
moVS = meanR
Case "V"
moVS = varR
Case "S"
moVS = std
End Select

End Function

[Charlize]

Function moVS(rng As Range)
Dim cell As Range
Dim std As Long, meanR As Long, GeomMean As Long, varR As Long
Dim Total As Double, RowCount As Double
'Set rng = Application.InputBox("Give range", "Give range", , , , , , 8)
Total = 0
RowCount = rng.Count 'This counts the rows, which number gets used in 'the Geometric Mean calculation
For Each cell In rng
Total = (cell.Value + 1) * Total
Next cell
GeomMean = Total ^ (12 / RowCount) - 1 'This calculates the Geometric 'Mean return
meanR = Application.WorksheetFunction.Average(rng) * 12 'This calculates the average return
varR = Application.WorksheetFunction.Var(rng) * 12 'This calculates the variance
std = Application.WorksheetFunction.DevSq(varR) 'This calculates the Standard Deviation USING 'the variance
moVS = "GeomMean : " & GeomMean & "/meanR : " & meanR & "/varR : " & varR & "/std : " & std
End Function

[Bob Phillips]

I must be missing something, but won't this code ensure that the total is ALWAYS 0



Total = 0
RowCount = rng.Count 'This counts the rows, which number gets used in 'the Geometric Mean calculation
For Each cell In rng
Total = (cell.Value + 1) * Total
Next cell

[Factor3]

Charlize & xld,

Good catch, you are correct, because I am using multiplication (and not addition), it does need to be:

Total = 1
RowCount = rng.Count 'This counts the rows
For Each cell In rng
Total = (cell.Value + 1) * Total
Next cell


mdmackillop, like usual, you got me thinking about Phase 4 in Phase 1... so here's what I would like to do (in Phase 4, since your telepathic or something:thumb :
I would like to be able to use your format, but slightly differently:
Function moVS(rng As Range, typ As String)
'The typ variable would be either "M", "A", "D", or "Q". If someone put in '"M", everything would stay as is, but if they put in "Q" (for quarterly) then 'the function would change to
GeomMean = Total ^ (4 / RowCount) - 1
meanR = Application.Average(rng) * 4
varR = Application.Var(rng) * 4
std = varR ^ 0.5

So there would literally be 4 potential loops:
GeomMean = Total ^ (12 / RowCount) - 1 'if "M" is entered
meanR = Application.Average(rng) * 12
varR = Application.Var(rng) * 12
std = varR ^ 0.5

GeomMean = Total ^ (4/ RowCount) - 1 'If "Q" is entered
meanR = Application.Average(rng) * 4
varR = Application.Var(rng) * 4
std = varR ^ 0.5

GeomMean = Total ^ (1/ RowCount) - 1 'If "A" is entered
meanR = Application.Average(rng)
varR = Application.Var(rng)
std = varR ^ 0.5

GeomMean = Total ^ (365/ RowCount) - 1 'If "D" is entered
meanR = Application.Average(rng) * 365
varR = Application.Var(rng) * 365
std = varR ^ 0.5



I would then like function to output all of the variables (GeomMean, meanR, & std) in consecutive rows (if that's even possible). Any ideas?:bow:

Thanks,

Factor

[Charlize]

Try this one. Fill in formula and press enter (no tab).

Charlize

[Factor3]

Charlize,

That's pretty cool! Neat having everything separated within the same cell with a forward slash!

Benjamin

[mail2bharath]

thankzz good