Loading
Ozgrid Excel Help & Best Practices Forums

Excel Training / Excel Dashboards Reports



Page 1 of 2 1 2 LastLast
Results 1 to 10 of 13

Thread: Calculate Weighted Median

  1. #1
    Join Date
    21st August 2008
    Posts
    10

    Calculate Weighted Median

    I have a task wherein I have to separate data in the Excel sheet and then calculate its median.

    For example the data is recorded as follows.

    Excellent Very Good Good Fair Poor Very Poor
    3 3 3 2 0 0 0 0


    This means that excellent has occurred 3 times, Very good 3 times and so on.

    The convention followed is Excellent = 5, Very good = 4, Good = 3, Fair = 2, Poor = 1 and Very Poor = 0.

    Hence The above data in the table indicates the following values

    5,5,5,4,4,4,3,3,3,2,2

    Hence the median of these values is 4. There are several rows in the worksheet and I need to calculate the median values for each row in the above manner.

    Please let me know if there would be any Macro code to automate this process.

    I am attaching the excel workbook that contains the data.
    Attached Files. REMINDER! OzGrid accepts no responsibility for ANY adverse effects as a result from downloading attached files. ALWAYS run an up-to-date virus scan and disable macros. Attached Files. REMINDER! OzGrid accepts no responsibility for ANY adverse effects as a result from downloading attached files. ALWAYS run an up-to-date virus scan and disable macros.

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    21st February 2006
    Location
    London, UK
    Posts
    3,831

    Re: Multiply Seperate & Calculate Median.

    Here's a custom formula approach which seems to work:
    VB:
    Function Median2(rng As Range) As Long 
         
        Dim t As Long, i As Long, rt As Long 
         
        t = WorksheetFunction.Sum(rng) 
         
        For i = 1 To 6 
            rt = rt + rng(1, i) 
            If t Mod 2 = 0 Then 
                If rt >= t / 2 Then 
                    Median2 = (Choose(i, 5, 4, 3, 2, 1, 0) + Choose(i + 1, 5, 4, 3, 2, 1, 0)) / 2 
                    Exit Function 
                End If 
            Else 
                If rt >= (t + 1) / 2 Then 
                    Median2 = Choose(i, 5, 4, 3, 2, 1, 0) 
                    Exit Function 
                End If 
            End If 
        Next i 
         
    End Function 
    
    
    So in G2 =median2(A2:F2) etc

    Excel Video Tutorials / Excel Dashboards Reports


  3. #3
    Join Date
    21st August 2008
    Posts
    10

    Re: Calculate Weighted Median

    I'm a newbie at VBA...can you please let me know how do I approach to this problem step by step in Excel ?

    -
    Nimish

    Excel Video Tutorials / Excel Dashboards Reports


  4. #4
    Join Date
    18th November 2004
    Location
    God's Own Country
    Posts
    4,648

    Re: Calculate Weighted Median

    Hi,

    UDF,

    VB:
    Function MYMEDIAN(r As Variant) 
        Dim a, i As Long, n As Long, v(), j As Long 
        a = Array(5, 4, 3, 2, 1, 0) 
        For i = 0 To 5 
            If r(i + 1) > 0 Then 
                For n = 1 To r(i + 1) 
                    j = j + 1: Redim Preserve v(1 To j) 
                    v(j) = a(i) 
                Next 
            End If 
        Next 
        If j > 0 Then 
            MYMEDIAN = Application.Median(v) 
        End If 
    End Function 
    
    
    Open Excel, hit Alt+F11, Go to Insert > Module and paste the code there.

    Now use;

    =MYMEDIAN(A2:F2)

    HTH

  5. #5
    Join Date
    21st August 2008
    Posts
    10

    Re: Calculate Weighted Median

    Where do I enter..

    =MYMEDIAN(A2:F2)

    after I paste the code in the module?

    --
    Nimish

    Excel Video Tutorials / Excel Dashboards Reports


  6. #6
    Join Date
    24th January 2003
    Location
    Australia
    Posts
    31,784

  7. #7
    Join Date
    21st August 2008
    Posts
    10

    Re: Calculate Weighted Median

    When I click on run it asks me to select a macro and when I enter =MYMEDIAN(A2:F2) in a cell it shows #NAME?

    Please help

    -
    N

    Excel Video Tutorials / Excel Dashboards Reports


  8. #8
    Join Date
    12th September 2006
    Location
    The Great State of Texas
    Posts
    4,329

    Re: Calculate Weighted Median

    A simple weighted average is a common measure of a Likert scale response. Considering Excellent = 5 to Very Poor = 0, then for your example,

    =SUMPRODUCT({5,4,3,2,1,0}, A2:F2) / SUM(A2:F2) returns 4.125
    Entia non sunt multiplicanda sine necessitate.

  9. #9
    Join Date
    21st August 2008
    Posts
    10

    Re: Calculate Weighted Median

    I'm still unable to get the solution.

    This is what I am doing..

    1. In the Excel sheet, I create a new module and then past the code

    VB:
    Function Median2(rng As Range) As Long 
         
        Dim t As Long, i As Long, rt As Long 
         
        t = WorksheetFunction.Sum(rng) 
         
        For i = 1 To 6 
            rt = rt + rng(1, i) 
            If t Mod 2 = 0 Then 
                If rt >= t / 2 Then 
                    Median2 = (Choose(i, 5, 4, 3, 2, 1, 0) + Choose(i + 1, 5, 4, 3, 2, 1, 0)) / 2 
                    Exit Function 
                End If 
            Else 
                If rt >= (t + 1) / 2 Then 
                    Median2 = Choose(i, 5, 4, 3, 2, 1, 0) 
                    Exit Function 
                End If 
            End If 
        Next i 
         
    End Function 
    
    
    2. After this I record a macro and goto the module and run it. It asks me to select a macro after which I select the macro that I am just recording.

    3. Nothing happens, then I put =MYMEDIAN(A2:F2) In any cell other than A2:F2.

    But still I dont get the results.

    Please reply and let me know...

    Regards,

    Nimish
    Last edited by Krishnakumar; September 5th, 2008 at 17:48.

    Excel Video Tutorials / Excel Dashboards Reports


  10. #10
    Join Date
    15th April 2003
    Location
    USA
    Posts
    7,528

    Re: Calculate Weighted Median

    You are working too hard. No need to record a macro or run a macro. Once the code has been pasted into a module, you onlu=y need to enter a formula in a worksheet cell. See the attached. I've entered the formulas for you in column I.
    Attached Files. REMINDER! OzGrid accepts no responsibility for ANY adverse effects as a result from downloading attached files. ALWAYS run an up-to-date virus scan and disable macros. Attached Files. REMINDER! OzGrid accepts no responsibility for ANY adverse effects as a result from downloading attached files. ALWAYS run an up-to-date virus scan and disable macros.
    Please do not U2U (private message) me directly for questions that should be posted to the forum; any such U2Us will be ignored.

Thread Information

Users Browsing this Thread

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

Possible Answers

  1. Calculate Weighted Average Costs
    By Preston Bandy in forum Excel General
    Replies: 2
    Last Post: August 5th, 2008, 05:48
  2. Calculate Weighted Correlation Coefficient
    By Michael Bourne in forum Excel General
    Replies: 7
    Last Post: January 28th, 2008, 15:42
  3. Calculate Weighted Percentage
    By PhiLLaY in forum Excel General
    Replies: 12
    Last Post: October 18th, 2007, 21:32
  4. Calculate Weighted Average
    By CaveMan in forum Excel General
    Replies: 3
    Last Post: December 19th, 2006, 20:47
  5. Calculate median for a Range
    By msteven in forum Excel General
    Replies: 2
    Last Post: August 2nd, 2005, 05:25

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
  •  
porno