Ozgrid Excel Help & Best Practices Forums

Excel Training / Excel Dashboards Reports

Thread: Calculate Weighted Median

1. I agreed to these rules
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.

Excel Video Tutorials / Excel Dashboards Reports

2. Super Moderator
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. I agreed to these rules
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. Super Moderator
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. I agreed to these rules
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. Re: Calculate Weighted Median

In any cell other than A2:F2.

7. I agreed to these rules
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?

-
N

Excel Video Tutorials / Excel Dashboards Reports

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

9. I agreed to these rules
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.

Regards,

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

Excel Video Tutorials / Excel Dashboards Reports

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.

Users Browsing this Thread

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

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