I am attempting to create an averaging formula that will average 2 numbers my spreadsheet. Because of inconsistent data, I am having to take in 3 numbers, find the two most revelent numbers that are there, and average the two of them. If I am missing 2 or more of the numbers, I simply return an empty string.
Because I am having to replicate this formula on several different spreadsheets, I decided to make a User Defined Formula for it to make my life easier.
I am wanting to convert the following formula into a User Defined Formula:
=IF(AND(CELL1<>"",CELL2<>""),AVERAGE(CELL1,CELL2),IF(AND(CELL1<>"",CELL3<>""),AVERAGE(CELL1,CELL3),IF(AND(CELL2<>"",CELL3<>""),AVERAGE(CELL2,CELL3),"")))
I have absolutely no experience with the Module Programming and very little experience in Excel Formulas, but I have experience with programming languages, so I was able to work myself around the syntax. Here is my attempt on programming the formula:
Function AverageData(Cell1 As Double, Cell2 As Double, Cell3 As Double) As Double
If (Cell1 <> "") Then
If (Cell2 <> "") Then
AverageData= Evaluate("AVERAGE(CELL1,CELL2)")
End If
End If
If (Cell1 <> "") Then
If (Cell3 <> "") Then
AverageData= Evaluate("AVERAGE(CELL1,CELL3)")
End If
End If
If (Cell2 <> "") Then
If (Cell3 <> "") Then
AverageData= Evaluate("AVERAGE(CELL2,CELL3)")
End If
End If
AverageData= ""
End Function
This currently returns: #VALUE!
I assume my mistake is miniscule, but I can use help nonetheless.
Bookmarks