r/excel • u/Szydlikj • 9d ago

MEDIANIF formula resulting in a VALUE! error solved

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!

1 Upvotes
  • permalink
  • link
  • reddit
  • reddit

100% Upvoted

46 comments sorted by

View all comments

Show parent comments

1

u/Szydlikj 9d ago

Just got it uploaded to screenshots

1

u/PaulieThePolarBear 1438 9d ago

I see no reason why this isn't working at the moment.

Change your main formula to use rows 2 to 6 in both ranges.

From the Formulas ribbon, select Evaluate Formula. A pop up box will appear. Screenshot this. Click Evaluate, Screenshot the box. Repeat until you can no longer select Evaluate

1

u/Szydlikj 9d ago

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?

1

u/PaulieThePolarBear 1438 9d ago

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

1

u/Szydlikj 9d ago

No, but Column O does, see range values screenshot

1

u/PaulieThePolarBear 1438 9d ago

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

1

u/Szydlikj 9d ago

Completed, uploaded

1

u/PaulieThePolarBear 1438 9d ago

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

1

u/Szydlikj 9d ago edited 9d ago

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?

1

u/PaulieThePolarBear 1438 9d ago

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?

→ More replies (0)