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

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.
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. :unsure:
 
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:

condition_1column_acolumn_bcolumn_c
1412655720
314590
103486492
15246856
69856564
256545546
44565649
218145462
1485851625
2559614555

<colgroup><col><col span="3"></colgroup><tbody>
</tbody>



Output List Required:
3486492
246856
9856564
8145462
59614555

<colgroup><col span="3"></colgroup><tbody>
</tbody>
 
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 :LOL:
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
Take the Median of a Dataset with Multiple Criteria
Replies
6
Views
595
kevin9999
K
F
  • Question
Calculation when 3 conditions are met
Replies
8
Views
406
Fidbeck
F
94mustang
  • Solved
Median which Excludes Zeros Using the Index/Match Function across Worksheets
Replies
8
Views
570
Fluff
Fluff
E
  • Question
Median for 255+ non-contiguous cell ranges
Replies
5
Views
936
StephenCrump
S
J
  • Solved
VBA to calculate and conditional format cell
Replies
6
Views
201
rollis13
rollis13

Forum statistics

Threads
1,215,064
Messages
6,122,941
Members
449,094
Latest member
teemeren

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