MEDIANIF formula resulting in a VALUE! error

[u/Szydlikj]

Hello again everyone. Reposting from last week after having tried some of your solutions to no avail. I am trying to calculate a median value from a subset of information. On Excel 2019 version 16.

The guide I followed suggested this format, but it returns a VALUE! error:

=MEDIAN(IF(L2:L200, “Same”, Y2:Y200))

Also tried your suggestion:

=MEDIAN(IF(L2:L200=“Same”, Y2:Y200))

Which outputs 0, but there are no 0s in the subset of data.

L column is checking for subset "Same", and Y column contains the number values I want the median for. Is it possible that the issue is because the values in Y column are the result of a formula?

Thanks in advance to all you wizards!

[u/Szydlikj]

As another commenter suggested elsewhere, using the formulas with CSE with no text data in the range "-" appears to be working correctly

I have some rows with a "-" with the intention of them being ignored by my formula, but it seems to be causing the error. Perhaps I can omit unwanted rows another way?

[u/PaulieThePolarBear → u/Szydlikj]

Are you saying column Y has something other than numerical values?

[u/Szydlikj → u/PaulieThePolarBear]

No, but Column O does, see range values screenshot

[u/PaulieThePolarBear → u/Szydlikj]

I can't replicate the issue you are reporting with a mix of text and numerical data in my equivalent to column O.

Please provide the screenshots requested in my previous comment

[u/Szydlikj → u/PaulieThePolarBear]

Completed, uploaded

[u/PaulieThePolarBear → u/Szydlikj]

It appears your first screenshot is after you have pressed Evaluate for the first time. Please provide a screenshot prior to pressing Evaluate.

[u/Szydlikj → u/PaulieThePolarBear]

Uploaded - see Eval 0

After some trial and error, I noticed some values in Y column have incomplete data and return #NUM! - when I deleted those blanks, the formula seems to work. So I think it must be the errors in Y column that break the median formula

Edit: Seems to break down with larger ranges selected, results in "0" output. Maybe blank cells in O and Y are being counted as 0s, bringing the median down?

[u/PaulieThePolarBear → u/Szydlikj]

Your edit seems to be correct. A blank cell in column O will be considered equal to 0 by Excel.

Change to

=MEDIAN(IF((O2:O6 = 0) *(O2:O6<>""), Y2:Y6))

The size of your range should have no impact on how Excel works, per se. But the larger the range, the higher the possibility of "bad" data.

It's almost always best to fix bad data at source rather than "code" round it. Why do you have blanks in your data? Do they have meaning?

[u/Szydlikj → u/PaulieThePolarBear]

The relevant data here gets filled when I close a client's file, so files that are still open have blank data (formulas waiting for source numbers).

I have my formulas set up in advance for the whole sheet to generate stats in real-time as I enter more data. I limited my range to where there is existing data, and replaced Y column blanks with "-" and this seems to be working correctly now.

Edit: tried your alternate formula, which also works splendidly. Thanks again!

[u/Szydlikj → u/PaulieThePolarBear]

Solution verified - thanks so much for all your help today!

[u/reputatorbot → u/Szydlikj]

You have awarded 1 point to PaulieThePolarBear.

---

^{I am a bot - please contact the mods with any questions}