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/PaulieThePolarBear]

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}