OaklandJim
Well-known Member
- Joined
- Nov 29, 2018
- Messages
- 849
- Office Version
- 365
- Platform
- Windows
Team Mr. Excel
I am quite disappointed about this. I have an investment portfolio workbook that took a lot of effort to complete. For that workbook I wrote a pretty basic UDF that returns a weighted average of values excluding zero values. It also excludes hidden rows so the values are correct when filtering data.
It worked well before but now it returns a #NAME? error. Other UDFs work.
I tried renaming the UDF but that did not work. Other UDFs do work.
I sure hope that someone can assist with this frustrating situation. The UDF is below. I could provide the workbook if necessary.
BTW, if there is a way to do this with worksheet functions that'd be great. But could not imagine how to do that.
Have a great day.
I am quite disappointed about this. I have an investment portfolio workbook that took a lot of effort to complete. For that workbook I wrote a pretty basic UDF that returns a weighted average of values excluding zero values. It also excludes hidden rows so the values are correct when filtering data.
It worked well before but now it returns a #NAME? error. Other UDFs work.
I tried renaming the UDF but that did not work. Other UDFs do work.
I sure hope that someone can assist with this frustrating situation. The UDF is below. I could provide the workbook if necessary.
BTW, if there is a way to do this with worksheet functions that'd be great. But could not imagine how to do that.
Have a great day.
VBA Code:
Option Explicit
' ----------------------------------------------------------------
' Procedure Name: WeightedValuesNoZeros
' Purpose: Calculate the weighted average of a series of numbers, weighted
' by the value of the respective investment holding.
' Procedure Kind: Function
' Procedure Access: Public
' Parameter prHoldingValues (Range): Holds the range containing investment holdings' value.
' Parameter prValuesToWeight (Range): Holds the values to be weighted.
' Author: Jim
' Date: 7/22/2024
' ----------------------------------------------------------------
Function WeightedValuesNoZeros(prHoldingValues As Range, prValuesToWeight As Range)
Dim rCell As Range
Dim iRow As Long
Dim dHoldingValuesRowValue As Double
Dim dTotalHoldingsValue As Double
Dim dValueToWeight As Double
Dim dTotalValuesToWeight As Double
Dim iIndexHeaderRow As Long
' Actual data begins in the row after data headers. Need to know that
' row to adjust the rCell row value used to index the value in prValues
' to get the value for dHoldingValuesRowValue.
iIndexHeaderRow = [Portfolio].Range("Header_Index").Row
For Each rCell In prValuesToWeight
' Only process visible rows.
If Not rCell.EntireRow.Hidden _
Then
' Row # to index the range prValues, where the holding's value is located.
iRow = rCell.Row - iIndexHeaderRow
' Exclude non-numeric values and values = 0.
If IsNumeric(rCell.Value) And rCell.Value > 0.000001 _
Then
' Get holding value for current row in range prValues.
dHoldingValuesRowValue = prHoldingValues.Cells(iRow, 1).Value
' Add the holding value for the current row being processed to
' the var that sums up those values.
dTotalHoldingsValue = dTotalHoldingsValue + dHoldingValuesRowValue
' Get the row value for the value to weight.
dValueToWeight = rCell.Value
' Add the weighted value to the var used to sum the weighted values.
WeightedValuesNoZeros = WeightedValuesNoZeros + (dValueToWeight * dHoldingValuesRowValue)
End If
End If
Next rCell
' Return the weighted value.
WeightedValuesNoZeros = WeightedValuesNoZeros / dTotalHoldingsValue
End Function
Last edited: