Announcement

Collapse
No announcement yet.

Calculate Weighted Median

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • 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

  • #2
    Re: Multiply Seperate & Calculate Median.

    Here's a custom formula approach which seems to work:
    Code:
    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

    Comment


    • #3
      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

      Comment


      • #4
        Re: Calculate Weighted Median

        Hi,

        UDF,

        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
        Kris

        ExcelFox

        Comment


        • #5
          Re: Calculate Weighted Median

          Where do I enter..

          =MYMEDIAN(A2:F2)

          after I paste the code in the module?

          --
          Nimish

          Comment


          • #6
            Re: Calculate Weighted Median

            In any cell other than A2:F2.

            Comment


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

              Comment


              • #8
                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.

                Comment


                • #9
                  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

                  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, 17:48.

                  Comment


                  • #10
                    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
                    Please do not U2U (private message) me directly for questions that should be posted to the forum; any such U2Us will be ignored.

                    Comment


                    • #11
                      Re: Calculate Weighted Median

                      So I need to enter the formula in a cell every time I need to calculate the median of a set of values in a row?

                      and if yes What if I have like 40 - 50 spread sheets containing similar data ?

                      Thank you for mentioning the steps!

                      --

                      Nimish

                      Comment


                      • #12
                        Re: Calculate Weighted Median

                        Yes, one formula for each set of data. You can copy and paste the formula as long as the cell you are pasting into is in the same relative position to the data it is using to calculate.
                        Please do not U2U (private message) me directly for questions that should be posted to the forum; any such U2Us will be ignored.

                        Comment


                        • #13
                          Re: Calculate Weighted Median

                          Thanks a lot for your and everyone else help !
                          I really appreciate it.

                          Regards,

                          Nimish

                          Comment

                          Working...
                          X