-2

I am trying to put a vba formula which helps me find the median for a range of cells if these values pertain to a paricular category.

Here is the sheet sample with data:

Snip of the Data

This is the code i want to put in Cell B2 and then be able to drag it down till B7:

=MEDIAN(IF(F2:F100=A2,G2:G100),"NA"))

I have tried innumerable ways but can't find its solution.

Thanks in advance for the help. Mayank

Scott Craner
  • 148,073
  • 10
  • 49
  • 81
Manky
  • 1
  • Does this answer your question? [Help needed with Median If in Excel](https://stackoverflow.com/questions/6752724/help-needed-with-median-if-in-excel) – Josh Eller Apr 13 '20 at 19:09
  • 1
    If you're using VBA, can you [edit] your question with the code you've tried? – BigBen Apr 13 '20 at 19:10
  • your formula has too many `)` should be `=MEDIAN(IF(F2:F100=A2,G2:G100,"NA"))` but the `,"NA"` can also be removed. :`=MEDIAN(IF(F2:F100=A2,G2:G100))` – Scott Craner Apr 13 '20 at 19:28
  • Where is the VBA in your post? Please show one or some of your *innumerable ways* you tried and specific issues (errors/undesired results) that occur. – Parfait Apr 13 '20 at 19:55

1 Answers1

-1

For Vba try FormulaArray property. 

Sheet1.Range("B2").FormulaArray = "=MEDIAN(IF(I:I=A2,J:J))"
Sheet1.Range("B2").Copy
Sheet1.Range("B3:B4").PasteSpecial Paste:=xlPasteFormulas

enter image description here

lostin
  • 720
  • 4
  • 10