How to calculate conditional median without accessing worksheet function?

C

cfadr

New Member
Joined
Oct 17, 2019
Messages
28
I have a large set of data and I need to calculate the median of certain values which are based on certain conditions in an if statement in a loop - in the code below I need to calculate the median of all the value_1 fields

- does someone have code that can be used to calculate a median like this? (I don't want to access the worksheet function given the amount of data I am processing)
- I also need to output all the data_set(i, 12) and data_set(i, 13) values when the condition is met (so when condition_1 > 5) in a list somewhere else in the excel workbook so that I can see them; how can I do this?

Many thanks


Code: 
Sub conditional_median_calc()
Application.ScreenUpdating = False
Dim data_set As Variant
data_set = Range("A3:D50000")
Dim i As Long
For i = 1 to UBound(data_set, 1)
	condition_1 = data_set(i, 10)
	If condition_1 > 5 Then
	value_1 = data_set(i, 12) + data_set(i, 13) + data_set(i, 14)
	End If
Next i
End Sub
 

Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)
Sort by date Sort by votes
1. Perhaps you could post a small set of sample data (say 10 rows?) and explain your expected result in relation to that sample data?

2. What can you tell us about the code you posted? It doesn't make sense. data_set is an array that takes data from a range 3 columns wide. Yet later in the code, it tries to access information from columns 10, 12, 13 and 14 of that array. :huh:
 
Upvote 0
sorry it's just a short example; hopefully the below makes more sense.

- I need code that produces the median of value_1 (sum of column_a, _b and _c values when condition_1 is >5) which in the example data below would be 1,362
- Secondly, I need to output (elsewhere in the worksheet) a list of the column_a, _b and _c values on the rows where condition_1 is >5 (have included an example output list below for this data)

Code: 
Sub conditional_median_calc()
Application.ScreenUpdating = False
Dim data_set As Variant
data_set = Range("A2:D50000")
Dim i As Long
For i = 1 To UBound(data_set, 1)
    condition_1 = data_set(i, 1)
    If condition_1 > 5 Then
    value_1 = data_set(i, 2) + data_set(i, 3) + data_set(i, 4)
    End If
Next i
End Sub

Data:

[TABLE="width: 272"]
<colgroup><col><col span="3"></colgroup><tbody>[TR]
[TD]condition_1[/TD]
[TD]column_a[/TD]
[TD]column_b[/TD]
[TD]column_c[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]1265[/TD]
[TD="align: right"]5720[/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]45[/TD]
[TD="align: right"]90[/TD]
[/TR]
[TR]
[TD="align: right"]10[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]4864[/TD]
[TD="align: right"]92[/TD]
[/TR]
[TR]
[TD="align: right"]15[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]46[/TD]
[TD="align: right"]856[/TD]
[/TR]
[TR]
[TD="align: right"]6[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]856[/TD]
[TD="align: right"]564[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD="align: right"]56[/TD]
[TD="align: right"]545[/TD]
[TD="align: right"]546[/TD]
[/TR]
[TR]
[TD="align: right"]4[/TD]
[TD="align: right"]456[/TD]
[TD="align: right"]564[/TD]
[TD="align: right"]9[/TD]
[/TR]
[TR]
[TD="align: right"]21[/TD]
[TD="align: right"]814[/TD]
[TD="align: right"]546[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="align: right"]485[/TD]
[TD="align: right"]851[/TD]
[TD="align: right"]625[/TD]
[/TR]
[TR]
[TD="align: right"]25[/TD]
[TD="align: right"]596[/TD]
[TD="align: right"]145[/TD]
[TD="align: right"]55[/TD]
[/TR]
</tbody>[/TABLE]



[TABLE="width: 192"]
<colgroup><col span="3"></colgroup><tbody>[TR]
[TD="colspan: 3"]Output List Required:[/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD="align: right"]4864[/TD]
[TD="align: right"]92[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD="align: right"]46[/TD]
[TD="align: right"]856[/TD]
[/TR]
[TR]
[TD="align: right"]9[/TD]
[TD="align: right"]856[/TD]
[TD="align: right"]564[/TD]
[/TR]
[TR]
[TD="align: right"]814[/TD]
[TD="align: right"]546[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD="align: right"]596[/TD]
[TD="align: right"]145[/TD]
[TD="align: right"]55[/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
Thanks for the clarifications. Give this a try in a copy of your workbook.

Rich (BB code): 
Sub conditional_median_calc()
  Dim data_set As Variant
  Dim AL As Object
  Dim i As Long, condition_1 As Long
  Dim k As Double, cond_median As Double
  
  Set AL = CreateObject("System.Collections.ArrayList")
  Application.ScreenUpdating = False
  With Range("A1", Range("D" & Rows.Count).End(xlUp))
    data_set = .Value
    For i = 2 To UBound(data_set, 1)
      condition_1 = data_set(i, 1)
      If condition_1 > 5 Then
        AL.Add data_set(i, 2) + data_set(i, 3) + data_set(i, 4)
      End If
    Next i
    If AL.Count > 0 Then
      AL.Sort
      k = AL.Count / 2
      If k = Int(k) Then
        cond_median = (AL.Item(k) + AL.Item(k - 1)) / 2
      Else
        cond_median = AL.Item(k - 1)
      End If
      .AutoFilter Field:=1, Criteria1:=">5"
      .Copy Destination:=.Offset(, .Columns.Count + 1).Cells(1)
      .AutoFilter
      .Offset(, .Columns.Count * 2 + 2).Cells(1).Resize(, 2).Value = Array("Median:", cond_median)
      .Columns(.Columns.Count + 2).Delete
    Else
      MsgBox "No data met the condition"
    End If
  End With
  Application.ScreenUpdating = True
End Sub


My data (A:D) & results (F:K)

<b>Sheet1</b><br /><br /><table border="1" cellspacing="0" cellpadding="0" style="font-family:Arial,Arial; font-size:8pt; background-color:#ffffff; padding-left:2pt; padding-right:2pt; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:85px;" /><col style="width:75px;" /><col style="width:75px;" /><col style="width:75px;" /><col style="width:23px;" /><col style="width:75px;" /><col style="width:75px;" /><col style="width:75px;" /><col style="width:23px;" /><col style="width:65px;" /><col style="width:47px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td><td >B</td><td >C</td><td >D</td><td >E</td><td >F</td><td >G</td><td >H</td><td >I</td><td >J</td><td >K</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td style="font-size:10pt; text-align:right; ">condition_1</td><td style="font-size:10pt; text-align:right; ">column_a</td><td style="font-size:10pt; text-align:right; ">column_b</td><td style="font-size:10pt; text-align:right; ">column_c</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; text-align:right; ">column_a</td><td style="font-size:10pt; text-align:right; ">column_b</td><td style="font-size:10pt; text-align:right; ">column_c</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; ">Median:</td><td style="font-size:10pt; text-align:right; ">1362</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td style="font-size:10pt; text-align:right; ">1</td><td style="font-size:10pt; text-align:right; ">4</td><td style="font-size:10pt; text-align:right; ">1265</td><td style="font-size:10pt; text-align:right; ">5720</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; text-align:right; ">3</td><td style="font-size:10pt; text-align:right; ">4864</td><td style="font-size:10pt; text-align:right; ">92</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td style="font-size:10pt; text-align:right; ">3</td><td style="font-size:10pt; text-align:right; ">1</td><td style="font-size:10pt; text-align:right; ">45</td><td style="font-size:10pt; text-align:right; ">90</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; text-align:right; ">2</td><td style="font-size:10pt; text-align:right; ">46</td><td style="font-size:10pt; text-align:right; ">856</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td style="font-size:10pt; text-align:right; ">10</td><td style="font-size:10pt; text-align:right; ">3</td><td style="font-size:10pt; text-align:right; ">4864</td><td style="font-size:10pt; text-align:right; ">92</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; text-align:right; ">9</td><td style="font-size:10pt; text-align:right; ">856</td><td style="font-size:10pt; text-align:right; ">564</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >5</td><td style="font-size:10pt; text-align:right; ">15</td><td style="font-size:10pt; text-align:right; ">2</td><td style="font-size:10pt; text-align:right; ">46</td><td style="font-size:10pt; text-align:right; ">856</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; text-align:right; ">814</td><td style="font-size:10pt; text-align:right; ">546</td><td style="font-size:10pt; text-align:right; ">2</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >6</td><td style="font-size:10pt; text-align:right; ">6</td><td style="font-size:10pt; text-align:right; ">9</td><td style="font-size:10pt; text-align:right; ">856</td><td style="font-size:10pt; text-align:right; ">564</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; text-align:right; ">596</td><td style="font-size:10pt; text-align:right; ">145</td><td style="font-size:10pt; text-align:right; ">55</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >7</td><td style="font-size:10pt; text-align:right; ">2</td><td style="font-size:10pt; text-align:right; ">56</td><td style="font-size:10pt; text-align:right; ">545</td><td style="font-size:10pt; text-align:right; ">546</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >8</td><td style="font-size:10pt; text-align:right; ">4</td><td style="font-size:10pt; text-align:right; ">456</td><td style="font-size:10pt; text-align:right; ">564</td><td style="font-size:10pt; text-align:right; ">9</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >9</td><td style="font-size:10pt; text-align:right; ">21</td><td style="font-size:10pt; text-align:right; ">814</td><td style="font-size:10pt; text-align:right; ">546</td><td style="font-size:10pt; text-align:right; ">2</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >10</td><td style="font-size:10pt; text-align:right; ">1</td><td style="font-size:10pt; text-align:right; ">485</td><td style="font-size:10pt; text-align:right; ">851</td><td style="font-size:10pt; text-align:right; ">625</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >11</td><td style="font-size:10pt; text-align:right; ">25</td><td style="font-size:10pt; text-align:right; ">596</td><td style="font-size:10pt; text-align:right; ">145</td><td style="font-size:10pt; text-align:right; ">55</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td></tr></table>
 
Upvote 0
How about using the Median formula.

Code: 
Sub Calculate_Conditional_Median2()
  Dim lr As Long
  Range("A1:D" & Range("A" & Rows.Count).End(xlUp).Row).AutoFilter 1, ">5"
  ActiveSheet.AutoFilter.Range.Range("B1:D" & Range("A" & Rows.Count).End(xlUp).Row).Copy Range("F1")
  ActiveSheet.ShowAllData
  lr = Range("F" & Rows.Count).End(xlUp).Row
  Range("K1") = Evaluate("=SUMPRODUCT(MEDIAN((F2:F" & lr & ")+(G2:G" & lr & ")+(H2:H" & lr & ")))")
End Sub
 
Last edited:
Upvote 0
DanteAmor said:
When I saw your reply, it was when I read the title completely :laugh:
Click to expand...
:) Anyway, I wonder why the OP want to avoid the worksheet function? Perhaps we will find out later.
 
Last edited:
Upvote 0
Peter_SSs said:
:) Anyway, I wonder why the OP want to avoid the worksheet function? Perhaps we will find out later.
Click to expand...

Yes, I would also like an explanation.

While here I fix my disaster.

Code: 
Sub Calculate_Conditional_Median2()
  Dim lr As Long, i As Long, n As Double, num As New Collection, addo As Boolean, j As Long
  Range("F:K").ClearContents
  Range("A1:D" & Range("A" & Rows.Count).End(xlUp).Row).AutoFilter 1, ">5"
  ActiveSheet.AutoFilter.Range.Range("B1:D" & Range("A" & Rows.Count).End(xlUp).Row).Copy Range("F1")
  ActiveSheet.ShowAllData
  lr = Range("F" & Rows.Count).End(xlUp).Row
  For i = 2 To lr
    n = Range("F" & i) + Range("G" & i) + Range("H" & i)
    addo = False
    For j = 1 To num.Count
      If num(j) > n Then
        num.Add n, Before:=j
        addo = True
        Exit For
      End If
    Next
    If addo = False Then num.Add n
  Next
'  Range("K1") = Evaluate("=SUMPRODUCT(MEDIAN((F2:F" & lr & ")+(G2:G" & lr & ")+(H2:H" & lr & ")))")
  Range("K1") = IIf(num.Count Mod 2, num((num.Count + 1) / 2), (num(num.Count / 2) + num(num.Count / 2 + 1)) / 2)
End Sub
 
Last edited:
Upvote 0
You must log in or register to reply here.

Similar threads

N
  • Question Question
Take the Median of a Dataset with Multiple Criteria
Replies
6
Views
889
kevin9999
K
F
  • Question Question
Calculation when 3 conditions are met
Replies
8
Views
700
Fidbeck
F
DB73
  • Question Question
calculating percentage based on cell values
Replies
2
Views
104
DB73
DB73
M
  • Locked
  • Question Question
Trying to input time calculation so it does not change to 24 hour format
Replies
3
Views
100
Peter_SSs
Peter_SSs
94mustang
  • Solved
Median which Excludes Zeros Using the Index/Match Function across Worksheets
Replies
8
Views
820
Fluff
Fluff

Forum statistics

Threads
1,223,707
Messages
6,174,000
Members
452,542
Latest member
Bricklin

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top