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/finickyone 1662 9d ago

I'm happy to follow up, but it's always better to take questions into dedicated posts as people don't dive into "Solved" threads as much, other people may want to weigh in, and also it would help people that come here later with the specific issue you're raising.

Mostly this will depend on the data in this range that you want to apply a condition to. There's a few things that are important to note here.

  1. Formulas and imports (inc data fetch via PQ etc) that seem to bring in values (numbers), don't always actually do so. They may import or generate strings that contain figures. This can be a tricky concept to digest, but in example, =RIGHT(12345,2), appears to generate 45, but actually generates "45". That will be a text value. If you subject that output to SUM, you'll get 0, and the text doesn't have any absolute value, any more than "cat" does.
  2. With that said, Excel does consider any text as being higher in value than any value. Text does have relative value, so that it can be sorted. In example, ="Dog">"Cat" returns True. However, so does ="5">"45, as does ="45">50. It is always important to check that what you think are values, actually are. =ISTEXT(cell) is a simple way to check that.

Overall, issues you're having will depend on your data. I'd suggest a new post with detailed screenshots.

Below are some examples of what I'm describing above, in case you want to mull them over.

https://preview.redd.it/qg3nivvrr6pd1.png?width=1256&format=png&auto=webp&s=c7179e49558e51c297b065b11d47a5c6debf7542