Trouble With VBA Median Function XL2010

[NeedyHelpExcelMan]

Hello All:

I am writing code in XL2010 and I have a question RE Median Functions in VBA. I have a column of data in column B and F. I want to produce a value in column G (calculated using data from Column F) given a criteria in column B. Below is an example of the data.

I'd like a function that would be able to recognize for all "100"s in column B, pull the correspond data in column F and find the median of it. Then take the value in column F and subtract the median of the corresponding median. For example cell G2 would be equal to: =F2-MEDIAN(F2:F5); G3 would be: =F3-MEDIAN(F2:F5) and so on and so forth. If this needs further explanation, please comment and let me know. Thanks in advance

B	F	G
100	10000
100	10000
100	12000
100	15000
101	50000

<TBODY>
</TBODY>

 

[NeedyHelpExcelMan]

Bump

 

[Andrew Poulsom]

Formula in G2 copied down:

Excel 2010
	B	C	D	E	F	G
1	B				F	G
2	100				10000	-1000
3	100				10000	-1000
4	100				12000	1000
5	100				15000	4000
6	101				50000	50000
Sheet1

Cell Formulas
Range	Formula
G2	{=IF(B2=100,F2-MEDIAN(IF(B$2:B$6=100,F$2:F$6)),F2)}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[NeedyHelpExcelMan]

Andrew -- Thanks for the response: a good start but a few issues and things I should disclose about my data

I was looking more for a vba code that will do this for me as there are hundreds of different values in Column B -- here is an example of the code that I have thus far, although way off from what I am trying to acheive:

Dim JCode As Variant 
'Column B
Dim Diff As Variant 
'Column G Values

Dim SalR As Range
' The range of values I want to calculate the median from

Dim Sal As Variant
'Column F Values

N = Application.WorksheetFunction.CountA(Range(Range("B2"), Range("B2").End(xlDown)
JCode = "100"
For i = 1 To N
Diff = Range("B2").Offset(i, 0).Value = Application.WorksheetFunction.
'This is where I get lost / as far as I've got

Hopefully this provides some context as to what I am trying to accomplish

As in I want provide excel with the criteria to search for in column B -- given that information, I would like excel to find the corresponding value in column F as well as find the median for all values, the range of them, of column F that are subject to the criteria given in column B. Finally in column G I want to have excel to produce that value found in Column F subtracted by the median found in Column F

 

[NeedyHelpExcelMan]

Bump

 

[Andrew Poulsom]

Does this work for you?

Sub Test()
    Dim LastRow As Long
    With ActiveSheet
        LastRow = .Range("B" & .Rows.Count).End(xlUp).Row
        With .Range("G2")
            .FormulaArray = "=RC[-1]-MEDIAN(IF(R2C[-5]:R6C[-5]=RC[-5],R2C[-1]:R6C[-1]))"
            .Copy Range("G3:G" & LastRow)
        End With
        With .Range("G2:G" & LastRow)
            .Value = .Value
        End With
    End With
End Sub

 

[NeedyHelpExcelMan]

Yes! Thank you so much, I really appreciate your help!

 

[NeedyHelpExcelMan]

Just kidding - I now get the error "You cannot change part of an array" on the following portion of the code

.Copy Range("G3:G" & LastRow)

 

[Andrew Poulsom]

My code doesn't create an array. So clear the cells before running it.

 

[NeedyHelpExcelMan]

Rejoice it works - thank you so much

My final question is, however, if I want to copy the code and search for different values in column B (i.e. "100024" instead of "100") how would I go about doing that?
Thanks again