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!
2
u/getmorecoffee 6 9d ago
What version of Excel/Office are you working with?
=median(filter(y2:y200, l2:l200=“Same”)) would do the trick, but may not work on older versions.
1
1
u/excelevator 2832 9d ago
=MEDIAN(IF(L2:L200= "Same", Y2:Y200,""))
1
u/Szydlikj 9d ago
Try it with more data and different categorical values, or see my test document linked in comments - I still get VALUE! from this
1
1
u/Decronym 9d ago edited 8d ago
Acronyms, initialisms, abbreviations, contractions, and other phrases which expand to something larger, that I've seen in this thread:
NOTE: Decronym for Reddit is no longer supported, and Decronym has moved to Lemmy; requests for support and new installations should be directed to the Contact address below.
Beep-boop, I am a helper bot. Please do not verify me as a solution.
11 acronyms in this thread; the most compressed thread commented on today has acronyms.
[Thread #37092 for this sub, first seen 16th Sep 2024, 12:47]
[FAQ] [Full list] [Contact] [Source code]
1
u/Szydlikj 9d ago edited 9d ago
Test document - feel free to edit the document and add a new attempt
1
u/cbr_123 221 9d ago
Very helpful to post this, thank you. I added mine as attempt 7. Tip: don't centre numbers as you've done in the C column. Excel by default right aligns numbers and left aligns text. One reason that would cause the MEDIAN formula to fail is having numbers treated as text. Centering the column makes it much harder to spot.
2
1
u/finickyone 1662 9d ago
Is it possible that the issue is because the values in Y column are the result of a formula?
Not that creates a barrier to using a formula for this analysis/query, but it might be worth checking that an example in L does actually equal “Same”, and hasn’t been created with any leading or trailing spaces (ie, as “ Same “ or similar).
Otherwise, the second examples should work. Your first example doesn’t use a test in the first IF argument: for each row you’re just asking
IF(“Same”,”Same”,data)
IF won’t understand what you mean by giving it “Same” as a test to apply, so will value error out.
To note is that where modern editions of Excel can process that formula by default, older versions require that it be entered using Ctrl+Shift+Enter, so that IF doesn’t try to intersect those x2:x200 ranges and test a single row.
1
u/AutoModerator 9d ago
I have detected code containing Fancy/Smart Quotes which Excel does not recognize as a string delimiter. Edit to change those to regular quote-marks instead. This happens most often with mobile devices. You can turn off Fancy/Smart Punctuation in the settings of your Keyboard App.
I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.
1
u/Szydlikj 9d ago
To be honest I only understood SOME of what you said. I can confirm there are no leading/trailing spaces after any "Same" entries in L column.
Are you saying I'm using the IF function incorrectly? How do I fix that to get the result I'm after?
2
u/finickyone 1662 9d ago
That’s ok. If you go to a blank worksheet, to B7 and enter =A1:A5, you should get an array returned of 5 rows of 0s. If you get an error, then you can’t use something like
=IF(range=xxx,…By default, as your version of Excel doesn’t support that functionality. If that is the case, then all you need to do when you enter your formula is apply it with CSE rather than just Enter. I can explain why/what this does if you want to know, but I’ll save it for now.
The result would be your formula being commited as
{=MEDIAN(IF(L2:L200="Same",Y2:Y200))}
But you wouldn’t add the curly brackets yourself; that’s just a result of using CSE.
1
u/Szydlikj 9d ago edited 9d ago
When I press CSE nothing happens - so my version is new enough? (Excel 2019 version 16)Learned today - input formula THEN press CSE.
2
u/finickyone 1662 9d ago
It’s the using B7 for A1:A5 test that really validates what you need to use. Where Excel can process arrays in functions like IF, it just ignores use of CSE, so that doesn’t confirm anything.
1
u/Szydlikj 9d ago
Solution verified - thank you! First time using CSE for something
1
u/reputatorbot 9d ago
You have awarded 1 point to finickyone.
I am a bot - please contact the mods with any questions
1
u/finickyone 1662 9d ago
Very welcome. I noticed elsewhere in this post that you're using Excel 2019. There's nothing wrong at all with that version. Newer ones just have some functions which make some tasks a bit easier (such as FILTER). The key difference though is around this CSE / Dynamic arrays capability.
In short, up to quite recently, if you gave Excel a formula which yielded more than one result, it didn't really know what to do. In example, with 1, 2 and 3 in cells A1:A3, if we ask Excel =ISODD(A1:A3), it didn't have any process to provide you those three results (True, False and True). As in there was no way to share those results with you in the one cell as an answer to that formula.
As such, Excel generally just wouldn't process it, unless you committed that formula in one of rows 1, 2 or 3. In that case, it would "intersect" the range you were referring to. So if executed in F2, Excel would treat it as =ISODD(A2), and return the result of that one test.
The same applies when using the function(IF(rangeA=X,rangeB)) approach, to apply function to only the data in B where A = x, as you're doing with MEDIAN. IF couldn't generate those 199 results of either Lx or FALSE, so MEDIAN isn't supplied any data. The way past this was to disable Implicit Intersecetion, which we did via CSE.
1
u/Szydlikj 9d ago
I know the thread is closed but in the same vein, but if i can ask you one more question:
I am now using this formula to check a different column for a subset where values are either "0", "1", or ">1". While checking for text values like "SAME" or "INCREASE" worked fine with your help, changing to a numerical subset gives me a #NUM! error. Any ideas?
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.
- 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.- 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.
1
u/PaulieThePolarBear 1438 9d ago
=MEDIAN(IF(L2:L200=“Same”, Y2:Y200))
Should work.
Are you committing the formula using CTRL+SHIFT+ENTER (CSE)? Read https://exceljet.net/glossary/array-formula for more information on array formula and the need for CSE in Excel versions (like yours) that do not support dynamic arrays.
1
u/Szydlikj 9d ago edited 9d ago
I just click the cell and enter the keys right?
Edit: No, I just didn't know how to use CSE. This works! Thank you so much! Solution verified.
1
u/reputatorbot 9d ago
You have awarded 1 point to PaulieThePolarBear.
I am a bot - please contact the mods with any questions
1
u/PaulieThePolarBear 1438 9d ago
I just click the cell and enter the keys right?
The only difference between a regular formula and an array formula is that you use CSE to commit it rather than just enter. You type your formula as you would normally.
If so, CSE doesn't do anything in my version of excel.
What does "doesn't do anything" mean?
Please provide an image showing what you mean by this. This image should include row and column labels as well as the formula you entered visible in the formula bar. You should NOT be in edit mode on this cell.
1
u/Szydlikj 9d ago
I know the thread is closed but in the same vein, but if i can ask you one more question:
I am now using this formula to check a different column for a subset where values are either "0", "1", or ">1". While checking for text values like "SAME" or "INCREASE" worked fine with your help, changing to a numerical subset gives me a #NUM! error. Any ideas?
1
u/PaulieThePolarBear 1438 9d ago
I am now using this formula to check a different column for a subset where values are either "0", "1", or ">1".
Tell me about these values. How are they populated?
1
u/Szydlikj 9d ago edited 9d ago
0, 1, 2, 3... any whole integers are possible values, manually input.
Removing the quotes from my formula appears to have worked for 1, but not for 0 or >1.
1
u/PaulieThePolarBear 1438 9d ago
After you input a value, is it left aligned or right aligned?
What EXACT formula are you using?
1
u/Szydlikj 9d ago
Here are some screenshots
Formula and using CSE
=MEDIAN(IF(O2:O500=0,Y2:Y500))
=MEDIAN(IF(O2:O500=1,Y2:Y500))
=MEDIAN(IF(O2:O500>1,Y2:Y500))Value is centre aligned on O column, but cell is formatted as number
1
u/PaulieThePolarBear 1438 9d ago
Please do the following
- Find a range from column O of about 5 cells that includes at least one value of 0
- In a blank cell, enter =range and press ENTER
- Choose to edit this formula, Highlight your range and press F9. Screenshot the formula bar.
- Press Esc to cancel
1
u/Szydlikj 9d ago
1
u/PaulieThePolarBear 1438 9d ago
No. Your formula is
=O2:O6
I don't care what this returns as long as it's not an error.
Edit the formula, highlight O2:O6, press F9. You should end with something like below - the values will be yours
={1, 2, 3, 4, 5}I want to see a screenshot of the formula bar resembling this
1
•
u/AutoModerator 9d ago
/u/Szydlikj - Your post was submitted successfully.
Solution Verifiedto close the thread.Failing to follow these steps may result in your post being removed without warning.
I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.