If statement Median calculation returning #N/A instead of median

Hi,

Recently, management decided we need to report a figure as a median instead of an average. Since these figures were done in a pivot table before, this poses a problem.

So I did some googling and found that an if statement nested in a median function should be able to support the data I am going for.

Here is my Formula:
=MEDIAN(IF($F$2:$F$30409=I2,$G$2:$G$30409))

Where Column F contains my look up value (For example, Column F could include EMEA, APAC, NA, etc)

I2 currently just has "APAC" in it with no quotes.

Column G contains a number that I need to calculate a median for based on the value in column F.

I made sure to CSE on my forumla as this is an array, but still I cannot get Excel to return a value.

The #N/A error usually means your lookup value is incorrect (At least when I see it using VLOOKUP). However, I can confirm APAC is present multiple times in Column F as well as in cell I2. There are no spaces after APAC in either the source or the lookup value.

I have changed the Format of the cells from Text to General and back but this has not changed my results. In theory, this shouldn't matter, I have had no problems in the past using a text to text vlookup.

I thought that excel possibly could not handle a median of >30k data points, however when I do just a =median($G$2:$G$30409) it returns the correct value in no time at all.

Does anyone else have any idea what I could try to get this to work? Manually calculating median is easy in theory, but very time consuming to do with my dataset.

Huge thanks to anyone who can help or even has suggestions for things I can try!

Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

I wonder if you have a text or a cell that doesnt have a number in it. I would check for that.

Throw a filter on the entire dataset and check that column for any text or any errors.

I tried your formula on a small example and it works fine

Copy the file.
Take the last 10k rows and delete them, did the formula start working?
If not take the last 10k rows of THAT and delete it. Did it start working?
If not then delete 5k of the remaining.

Essentially you are trying to figure out at what point DOES it start working, then keep scoping in to find the problem.
I suspect it is a cell, like a value stored as a text or something.

Are there any #N/A errors present in either of the ranges?

Originally Posted by Tony Valko

Are there any #N/A errors present in either of the ranges?

Like Tony says maybe there is just one row of #N/A error that is disrupting this

Try this to filter out the errors, still as a CSE array formula

=MEDIAN(iferror(IF($F$2:$F$30409=I2,$G$2:$G$30409),FALSE))