MEDIAN IF Contains Matching Text

frankendoc

New Member
Joined
Apr 2, 2021
Messages
1
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
  2. MacOS
HI,
I'm trying to do the Median equivalent of AVERAGEIF(A1:A10,"*"&C12"&*",B1:B10) where Column C has text some of which is contained within a longer string in Column A and Column B has numerical values that I want to find the median of.
I found other threads where this was possible with the wildcards if the search term was typed in but not if the search term is in another cell (C12 in this case).
Any help would be greatly appreciated.
 

Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple

kweaver

Well-known Member
Joined
May 8, 2018
Messages
2,137
Office Version
  1. 365
  2. 2010
Welcome to Mr. Excel.

It would help if you show a few examples and use XL2BB to do that.
 

Habtest

Board Regular
Joined
Jul 30, 2020
Messages
198
Office Version
  1. 365
Platform
  1. Windows
Hi there, something like:
Excel Formula:
{=MEDIAN(IF(ISNUMBER(FIND(C12,A1:A10)),B1:B10))}
and enter with CTRL+SHIFT+ENTER
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
51,842
Office Version
  1. 365
Platform
  1. Windows
Hi there, something like:
Excel Formula:
{=MEDIAN(IF(ISNUMBER(FIND(C12,A1:A10)),B1:B10))}
and enter with CTRL+SHIFT+ENTER
Not sure whether this is actually relevant to the OPs data as we don't know much about that, but to be truly equivalent to the AVERAGEIF formula posted, that formula would need to use SEARCH instead of FIND.

Just an aside too: If the OP is using this in their Excel 365 as opposed to Excel 2016, CTRL+SHIFT+ENTER confirmation would not be required.

.. and another 365, but not 2016, option would be

Excel Formula:
=MEDIAN(FILTER(B1:B10,ISNUMBER(SEARCH(C12,A1:A10))))
 

Forum statistics

Threads
1,148,193
Messages
5,745,263
Members
423,941
Latest member
CluelessAboutExcel

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
Top