Calculate Weighted Geometric Average with Excel function

1
votes

I am a beginner with Excel-VBA and I need to create a function to calculate the geometric weighted average, given a list of values (Mi), and a list of weights (`Wi').

The Excel formula is: EXP((SUM(Wi*LN(Mi))/(sum(Wi))

I'm having difficulty with the "Sum" section of the formula. This is what I have done so far: 

Function GEOWAMC(Wi as Double, Mi as Double) As Double
    Range("A1").Formual="Sum("& Range(Cells(2,1), Cells(2,3)).Address(False,False)&")"
    GEOWAMC=((WorksheetFunction.Sum(Wi*ln(Mi))/(worksheetFunction.sum(Wi)))
End Function
1
vbaexcelfunctionformula
You cannot set another cell's formula from a udf on the worksheet. - user4039065
Would that means I need to create two different function? - Cocotte
You cannot set another cell's formula from a udf on the worksheet. so either type the formula into A1 or write a Sub (not a function) that writes the formula into A1. fwiw, it isn't at all clear why you are trying to write the formula into A1. - user4039065
Can you please give an example of data this would be used with? (You have a few issues there but the big one is that your function isn't going to accept more than one cell like it is.) - ashleedawg
The objective is to calculate the Geometric Weighted Average Market Cap of a portfolio. I could have 40 stocks ( as an example) and each of them would represent a certain percentage of my total portfolio. that would be "Wi". In order to calculate this, we also need the current market cap of each stock, which would be "Mi". (If I could I would attached my excel sheet example, I can't find the feature on this blog) - Cocotte

1 Answers

0
votes

I guarantee that you're mot the first person who's needed to calculate this - or any other math function you can think of - in Excel. A search for "weighted geometric mean excel" lead me to an answer (likely the same place you got the function).

It's good to see that you attempted to solve this yourself, however it's not the "Sum" section that's preventing your function from working.

Debugging Your VBA Function:

I'm afraid there are a number of things wrong with your code. That number is "Nine".

  • Most importantly, you transposed your formulas criteria in your explanation.
    The parameter that starts with the W is the Weight, and the other parameter is the Value.

  • If you want to use parameters with more than 1 cell, you use Range (not Double).

  • You cannot assign a value directly to a cell (or change a cell in any other way) from within a function. Functions return values to the cell from which it was called.

  • Address(False, False) isn't going to accomplish anything productive in this case.
    (You need better understanding of the purpose of relative vs. absolute references.)

  • Instead of using: Range(Cells(2,1), Cells(2,3)).Address(False,False)
    ...you could use A2:C2 because they mean exactly the same thing (and always will).

  • Therefore, all this:
    Range("A1").Formual="Sum("& Range(Cells(2,1), Cells(2,3)).Address(False,False)&")
    ...can be shortened to: [A1]=[A2]+[B2]+[C2]
    ...because they are mean exactly the same thing (and always will).

  • You're using worksheet functions in VBA, which is fine in some cases
    ...but the function is Application.WorksheetFunction.Sum.

  • The natural logarithm function LN accepts only one value when used one it's own
    ...not a "list" (or range of cells)

  • You should use Option Explicit at the top of each module, especially when learning or troubleshooting; it would have helped you catch at least a couple of these mistakes.

  • Also you have a couple unnecessary sets of parentheses, but that's not a big deal.

So, you could work on fixing all of that, or better yet, you could use it as a worksheet function and save yourself (or someone else) a lot of work for no reason.

Using the Worksheet Function

Syntax:

=EXP(SUMPRODUCT( WeightRange ,LN( ValueRange ))/SUM( WeightRange ))

  • note that the same *WeightRange* is entered twice.
  • the *WeightRange* and *ValueRange* must both be the same size.

Example:

If:
- cells A1 to A5 contains your values, and,
- cells B1 to B5 contain the weights for the values, and,
- you wanted the Weighted Geometric Mean in cell C1

...then in C1 you would enter:

=EXP(SUMPRODUCT(B1:B5,LN(A1:A5))/SUM(B1:B5))

I suspect that this will suffice as a solution, now that we've walked through it, but if you have an actual reason that you need this calculation to take place within a VBA Function then by all means let me know and me will figure it out. :-)