Results 1 to 12 of 12

With Custom Modified Average Formula

Threaded View

  1. #1
    Registered User
    Join Date
    06-17-2010
    Location
    Orlando, Florida
    MS-Off Ver
    Excel 2003
    Posts
    8

    Exclamation With Custom Modified Average Formula

    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.
    Last edited by royUK; 06-17-2010 at 02:51 PM. Reason: add code tags

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1