•1 min read•from Microsoft Excel | Help & Support with your Formula, Macro, and VBA problems | A Reddit Community
This is probably the most complicated Excel formula I’ve ever seen.
Our take
Navigating complex Excel formulas can be daunting, especially when you encounter something as intricate as the one shared by a user. This formula, featuring multiple nested functions, works effectively but can leave even seasoned users scratching their heads. It raises an important question: is this typical for advanced Excel users, or should one avoid tampering with what functions well? Additionally, exploring alternatives like XLOOKUP and FILTER might simplify the approach.
I came across this formula in a file at work and honestly… I just sat there staring at it for a good minute.
It works, but trying to actually understand what it’s doing is a whole different story.
Here’s the formula:
=IFERROR(INDEX($B$2:$B$100, MATCH(1, (IF($D$2="All",1,($C$2:$C$100=$D$2))) * ($A$2:$A$100=MAX(IF($D$2="All",$A$2:$A$100,IF($C$2:$C$100=$D$2,$A$2:$A$100)))), 0)), "No Result").
I can kind of follow pieces of it, but the whole thing together feels like someone just kept stacking functions until it magically worked.
Is this normal for advanced Excel users or is this more of a “don’t touch it if it works” situation.
Also curious — would you rewrite this using something like XLOOKUP / FILTER, or just leave it alone.
Would really appreciate if someone could explain this in plain English.
[link] [comments]
Read on the original site
Open the publisher's page for the full experience
Related Articles
- Excel formula automatically rewriting itself??Hi all, this is a really odd problem to run into and I am unsure how to proceed from here. I am currently entering data and I have Excel performing an =SUM(B5:AC5) for my data that is being entered. Because the data entry can go pretty fast sometimes, I would keep entering data past AC5 before I realize what happened. When I look at my SUM column, Excel has flagged my =SUM(B5:AC5) formula with the error below, which is fine and whatever, but it is automatically changing my =SUM(B5:AC5) formula to =SUM(B5:AF5) or something of similar depending on what column I stopped at. Just for reference, data from AD5 to AO5 is being summed up in another column with SUM=(AD5:AO5). Formula error being flagged by Excel I have no idea why it is doing this and I tried to ignore error, but it still automatically rewriting my formula anyways! This worksheet and formula worked smoothly 4 months ago so I don't know what is going on. submitted by /u/WildKhanine [link] [comments]
- Populate Cells From a Random Range of Cells, Based on the Contents of Different Cells.Hello Reddit pros, I am trying to create/find a formula that will do the following: Check if A2 matches values in D1:D6 and return a random value from E1:E6 into B2. The hard part is this; if A2 equals D1:D3 then the random value of B2 is from E1:E3, but if A2 equals D4:D6 then the random value of B2 is from E4:E6. For example: if A2 says Input 5, then B2 could be either Result D, Result E, or Result F. The values in the screenshot are just for demonstration; I will be able to extrapolate what I learn onto the spreadsheet I need it for. I am just curious if there is a formula to do that, or am I just having a lot of high hopes? I have tried using IF combined with INDEX and RANDBETWEEN but I cannot seem to get the formula correct for doing even the first part of what I need, let alone the second part. It looks like this: =IF(A2:A19,INDEX(D1:D3,RANDBETWEEN(1,COUNTA(E1:E3))),"") This obviously is not correct, and it returns a #VALUE error that I cannot figure out. I do not know the correct way to phrase the question to get a viable answer via internet searching, so I am once again turning to the experts on Reddit. Thanks for any insight. I am aware the formula on the screenshot is different than my post body; I deleted the top row but didn't fix the formula u/Connect_Camel_5998 solved it for me. Thanks everybody! The formula that was posted works great for what I needed! submitted by /u/ClandestineGhost [link] [comments]
Tagged with
#Excel alternatives for data analysis#AI formula generation techniques#Excel compatibility#formula generator#Excel alternatives#natural language processing for spreadsheets#generative AI for data analysis#rows.com#Excel formula#IFERROR#INDEX#MATCH#XLOOKUP#FILTER#functions#data lookup#MAX#advanced Excel#understanding formula#range