Excel Formula WTF



  • A co-worker just sent this to me, immensely proud of his work:

    =(((SUM(INDIRECT("'SIM Pivot'!B" & (MATCH(C34, 'SIM Pivot'!B:B) + MATCH($A35, 'SIM Pivot'!$A:$A, 0) - 2)), INDIRECT("'SIM Pivot'!B" & (MATCH(C34, 'SIM Pivot'!B:B) + MATCH($A35, 'SIM Pivot'!$A:$A, 0) - 2) - (1 * ((COUNTA('SIM Pivot'!$A:$A) - 2) / (COUNTA('SIM Pivot'!$A:$A) - COUNTA('SIM Pivot'!$B:$B))))), INDIRECT("'SIM Pivot'!B" & (MATCH(C34, 'SIM Pivot'!$A:$A) + MATCH($A35, 'SIM Pivot'!$A:$A, 0) - 2) - (2 * ((COUNTA('SIM Pivot'!$A:$A) - 2) / (COUNTA('SIM Pivot'!$A:$A) - COUNTA('SIM Pivot'!$B:$B))))))/SUM(INDIRECT("'SIM Pivot'!B" & (MATCH(C34, 'SIM Pivot'!$A:$A) + MATCH("Industry", 'SIM Pivot'!$A:$A, 0) - 2)), INDIRECT("'SIM Pivot'!B" & (MATCH(C34, 'SIM Pivot'!$A:$A) + MATCH("Industry", 'SIM Pivot'!$A:$A, 0) - 2) - (1 * ((COUNTA('SIM Pivot'!$A:$A) - 2) / (COUNTA('SIM Pivot'!$A:$A) - COUNTA('SIM Pivot'!$B:$B))))), INDIRECT("'SIM Pivot'!B" & (MATCH(C34, 'SIM Pivot'!$A:$A) + MATCH("Industry", 'SIM Pivot'!$A:$A, 0) - 2) - (2 * ((COUNTA('SIM Pivot'!$A:$A) - 2) / (COUNTA('SIM Pivot'!$A:$A) - COUNTA('SIM Pivot'!$B:$B))))))) * Weights!$C$13) + ((AVERAGE((INDIRECT("'SIM Pivot'!C"&(MATCH(D34,'SIM Pivot'!$A:$A)-1)+(MATCH($A35,'SIM Pivot'!$A:$A,0)-1)))/((INDIRECT("'SIM Pivot'!C"&(MATCH(D34,'SIM Pivot'!$A:$A)-1)+(MATCH($A35,'SIM Pivot'!$A:$A,0)-1)))+((INDIRECT("'SIM Pivot'!D"&(MATCH(D34,'SIM Pivot'!$A:$A)-1)+(MATCH($A35,'SIM Pivot'!$A:$A,0)-1))*Weights!$C$15))), (INDIRECT("'SIM Pivot'!C"&(MATCH(C34,'SIM Pivot'!$A:$A)-1)+(MATCH($A35,'SIM Pivot'!$A:$A,0)-1)))/((INDIRECT("'SIM Pivot'!C"&(MATCH(C34,'SIM Pivot'!$A:$A)-1)+(MATCH($A35,'SIM Pivot'!$A:$A,0)-1)))+((INDIRECT("'SIM Pivot'!D"&(MATCH(C34,'SIM Pivot'!$A:$A)-1)+(MATCH($A35,'SIM Pivot'!$A:$A,0)-1))*Weights!$C$15))), (INDIRECT("'SIM Pivot'!C"&(MATCH(B34,'SIM Pivot'!$A:$A)-1)+(MATCH($A35,'SIM Pivot'!$A:$A,0)-1)))/((INDIRECT("'SIM Pivot'!C"&(MATCH(B34,'SIM Pivot'!$A:$A)-1)+(MATCH($A35,'SIM Pivot'!$A:$A,0)-1)))+((INDIRECT("'SIM Pivot'!D"&(MATCH(B34,'SIM Pivot'!$A:$A)-1)+(MATCH($A35,'SIM Pivot'!$A:$A,0)-1))*Weights!$C$15))))) * Weights!$C$14)) * 100

    Yeah... not much to add.



  •  For the non-excel-initated, care to explain what INDIRECT() does? Is that something like eval()?



  • @PSWorx said:

     For the non-excel-initated, care to explain what INDIRECT() does? Is that something like eval()?

    From Excel's Help:

    Description

    Returns the reference specified by a text string. References are immediately evaluated to display their contents. Use INDIRECT when you want to change the reference to a cell within a formula without changing the formula itself.

    So yeah it looks like it basically evals the expression and returns the reference it points to?



  •  So what is this mess supposed to do?



  • This is exactly why i say no, if someone asks a question starting with "i'm having a problem with excel ..."

    Now that i think about it, it does seem that excel is one of the last write only languages in use (that i can think of).



  • @locallunatic said:

    So what is this mess supposed to do?
    Based on the values of some cells in column C and D in a PivotTable and some cells in column A of the local sheet, it takes the weighted (by cells in column A of the PivotTable as well as a separate, named Weights range) average of various cells in column B of the PivotTable, not necessarily the same ones each time, and not necessarily only once per. sigh variable variables... I thought only PHP and JavaScript had that sort of mess! >_<



  • @TwelveBaud said:

    *sigh* variable variables... I thought only PHP and JavaScript had that sort of mess! >_<
     

    TwelveBaud, I'd like you to meet Rexx.



  • @TwelveBaud said:

    sigh variable variables... I thought only PHP and JavaScript had that sort of mess! >_<

    I think quite a few scripting languages do, actually, although not all have dedicated syntax elements for it. In Python, you can do globals()[varname] or locals()[varname], and there are getattr and setattr functions to access the attributes of an object by name. These constructs are verbose enough to make it abundantly clear what's being done though, as long as you have a basic grasp of the language.



  • @PSWorx said:

    For the non-excel-initated, care to explain what INDIRECT() does? Is that something like eval()?

    That's always been a pet peeve of mine. People post some big chunk of code with no explanation as to what it does or even why it's a WTF.

    While I agree that most people post from work and they don't have a lot of time to do it, many of us are also reading this from work and don't have time to decipher it. It also alienates anyone who isn't familiar with the language/framework/technique.



  • @Soviut said:

    That's always been a pet peeve of mine. People post some big chunk of code with
    no explanation as to what it does or even why it's a WTF.

    While I sympathise with your point of view, I think in this case simply looking at the formula, with no knowledge of it or the problem at all, is enough to see why it is a WTF.

    @locallunatic said:

     So what is this mess supposed to do?


    You know work is boring when you find yourself trying to answer this question.

    It's certainly pretty freaky. For one thing, it's using the ratio (count_a - 2) / (count_a - count_b) as an offset, where count_a and count_b are the number of non-empty cells in columns A and B respectively of the 'SIM Pivot' sheet. It follows that the number of populated cells in column A must be very close to, but not equal to, the number of populated cells in column B. The -2 part is probably to exclude header rows of some sort.

    Also, I think the two references to MATCH(C34, 'SIM Pivot'!B:B) are incorrect and should be MATCH(C34, 'SIM Pivot'!$A:$A) - all other calls to MATCH are done against $A:$A; I'd expect this was originally MATCH(C34, 'SIM Pivot'!A:A) and then got filled across to the next cell.

    The 'SIM Pivot' worksheet seems to have two parts: firstly a list of categories or descriptions, then some data that really should be tabular but is written out in a list; column A contains a price or value point and columns B-D contain related data. Each value point is repeated for all the categories. The formula is flexible about how many categories and value points there are, which accounts for a lot of the horribleness.

    Inputs are a category name in A35 and value points (not necessarily ones in the table) in B34-D34. The first part of the calculation looks up the data in column B for the given category and the value point in C34 (finding the largest value in the data less than or equal to C34), adds the data for the same category and the two preceding value points, and calculates the ratio of this total to the corresponding total for the "Industry" category. This is weighted by a certain amount (Weights!C13).

    In the second part of the calculation, for each of the supplied value points in B34-D34, we look up the data for the nominated category and calculate (value in column C / (value in column C + Weights!C15 * value in column D)). Then we take the average of those three and weight it by Weights!C14.

    These two halves of the calculation are added and the total multiplied by 100.

    I'm thinking B34-D34 could represent low, expected and high values for something. The name 'SIM Pivot' makes me think of mobile phones, so it could be estimated usage or something like that, and the whole thing could be some sort of plan chooser.



  • @TwelveBaud said:

    sigh variable variables... I thought only PHP and JavaScript had that sort of mess! >_<

    I seem to remember those in mirc script too! The only was to implement arrays.



  • @TwelveBaud said:

    I thought only PHP and JavaScript had that sort of mess! >_<
     

    ?

    Javascript doesn't have variable variables. It has a workaround for dynamic members, but PHP has an actual honest to god implementation of variable variables.



  • @esoterik said:

    This is exactly why i say no, if someone asks a question starting with "i'm having a problem with excel ..."

    Now that i think about it, it does seem that excel is one of the last write only languages in use (that i can think of).

     

    A lot - and I mean a lot - of business logic gets programmed in Excel in this fashion. Often because there is a clear divide between the business and IT, and only the IT department has any actual programming tools. Everyone else just has Excel. It was a big fight at my last employer to get Matlab.

     Best example and WTF I have seen, was when I learned that 40% of our Dutch national electricity grid was controlled by an Excel'95 spreadsheet running on Windows NT SP3, because it broke on anything newer and the "developer" had left over 10 years ago. This was as recent as 2007.



  • @RogerWilco said:

    ...

    Best example and WTF I have seen, was when I learned that 40% of our Dutch national electricity grid was controlled by an Excel'95 spreadsheet running on Windows NT SP3, because it broke on anything newer and the "developer" had left over 10 years ago. This was as recent as 2007.

    That story is just crazy enough that it is probably more true than anyone wants to belive. They are going to have a real fun time when that PC finally gives out, i mean what are the chances of windows NT installing - at all - on modern hardware? I was building a test machine a year or so ago and i wanted to put all the operating systems that we supported on it. Turns out that the Win2k installer would crash and reset on the (new, preinstalled w/vista) PC i tried; i guess it didn't like something about the hardware. 

     



  • @esoterik said:

    @RogerWilco said:

    ...

    Best example and WTF I have seen, was when I learned that 40% of our Dutch national electricity grid was controlled by an Excel'95 spreadsheet running on Windows NT SP3, because it broke on anything newer and the "developer" had left over 10 years ago. This was as recent as 2007.

    That story is just crazy enough that it is probably more true than anyone wants to belive. They are going to have a real fun time when that PC finally gives out, i mean what are the chances of windows NT installing - at all - on modern hardware? I was building a test machine a year or so ago and i wanted to put all the operating systems that we supported on it. Turns out that the Win2k installer would crash and reset on the (new, preinstalled w/vista) PC i tried; i guess it didn't like something about the hardware. 

     

    Meh, hopefully the IT department is smart enough to migrate it onto something like an ESXi box and just virtualize the original hardware config when the box dies. Problem* solved!



  • @Soviut said:

    @PSWorx said:
    For the non-excel-initated, care to explain what INDIRECT() does? Is that something like eval()?

    That's always been a pet peeve of mine. People post some big chunk of code with no explanation as to what it does or even why it's a WTF.

    While I agree that most people post from work and they don't have a lot of time to do it, many of us are also reading this from work and don't have time to decipher it. It also alienates anyone who isn't familiar with the language/framework/technique.

    Sorry, but the fact is I don't know what it does. I have to just trust him that it works, I'm not debugging that shit.

    The real WTF is the guy who wrote it knows VBA, but he wrote this in a cell formula instead of a much-easier-to-read VBA function.



  • @dhromed said:

    @TwelveBaud said:

    I thought only PHP and JavaScript had that sort of mess! >_<
     

    ?

    Javascript doesn't have variable variables. It has a workaround for dynamic members, but PHP has an actual honest to god implementation of variable variables.

    It doesn't have dynamic variables in the (extremely WTFy) way that PHP does, but you can eval() a string and get the value of the variable whose name is represented by the string. Which is pretty similar to what INDIRECT is doing here.



  • @esoterik said:

    @RogerWilco said:

    ...

    Best example and WTF I have seen, was when I learned that 40% of our Dutch national electricity grid was controlled by an Excel'95 spreadsheet running on Windows NT SP3, because it broke on anything newer and the "developer" had left over 10 years ago. This was as recent as 2007.

    That story is just crazy enough that it is probably more true than anyone wants to belive. They are going to have a real fun time when that PC finally gives out, i mean what are the chances of windows NT installing - at all - on modern hardware? I was building a test machine a year or so ago and i wanted to put all the operating systems that we supported on it. Turns out that the Win2k installer would crash and reset on the (new, preinstalled w/vista) PC i tried; i guess it didn't like something about the hardware. 

     

    By "NT" I'm going to assume we're talking Windows NT 4.0. Even when it was the current platform getting it to install out of the box on anything without blue screening was a real gamble. Most of NT4's installation issues were over storage controller drivers so I'm going to guess just like back then it should install ok on modern hardware as long as you can F6 a stable storage controller driver during setup.



  • @esoterik said:

    This is exactly why i say no, if someone asks a question starting with "i'm having a problem with excel ..."

    Now that i think about it, it does seem that excel is one of the last write only languages in use (that i can think of).

    What do you mean?  People are still using Perl and C++.



  • INDIRECT is indeed esoteric; but there is no other reasonable way to implement what it does (evaluate a formula that returns a cell reference, then get the value of that cell). When you figure out that you need it, you will love it.



    I think i've needed it once :)



  •  @locallunatic said:

     So what is this mess supposed to do?

     

    A fairly safe bet is "Something similar, but not equal, to what the guy who wrote it thinks it does."



  •  @Mason Wheeler said:

    @esoterik said:

    This is exactly why i say no, if someone asks a question starting with "i'm having a problem with excel ..."

    Now that i think about it, it does seem that excel is one of the last write only languages in use (that i can think of).

    What do you mean?  People are still using Perl and C++.

    Agreed about Perl.  C++ is pretty standard though.


  • @error_NoError said:

    By "NT" I'm going to assume we're talking Windows NT 4.0. Even when it was the current platform getting it to install out of the box on anything without blue screening was a real gamble. Most of NT4's installation issues were over storage controller drivers so I'm going to guess just like back then it should install ok on modern hardware as long as you can F6 a stable storage controller driver during setup.
    NT4 works just fine in VMWare - it's also fully supported there. (NT 3.51 also works just fine in VMWare, too, but you need a 3rd party VESA driver if you want anything beyond 640x480 in 16 colours).



  • @Scarlet Manuka said:

    You know work is boring when you find yourself trying to answer this question.

    Long description of what crazy function is doing

     

    Thanks, when I tried to parse that crap my brain kept freezing up.



  • @locallunatic said:

    Thanks, when I tried to parse that crap my brain kept freezing up.

    Fortunately it's summer here, so freezing isn't a problem. I did come dangerously close to meltdown though :)

    Actually it wasn't too bad after a few iterations of "replace all instances of this specific function call or expression with this identifier". If "a few" means about 15, anyway.

    But yes, TRWTF is cramming it all into a single cell formula. Would have been much better with a bunch of cells used for intermediate results, or as a VBA function where they could be stored in variables.

    I've just had a horrible image of the programmer being told to convert it into a VBA function, and making it into a one-liner that just replicates the formula with a whole bunch of Application.WorksheetFunction.* calls and a couple of pages of lines ending in " _".



  • I've come across some pretty messy Excel in my time. Generally, when I see an Excel spreadsheet, "refacor" is my reflex reaction.



  • @RogerWilco said:

     Best example and WTF I have seen, was when I learned that 40% of our Dutch national electricity grid was controlled by an Excel'95 spreadsheet running on Windows NT SP3, because it broke on anything newer and the "developer" had left over 10 years ago. This was as recent as 2007.

     My first (mis-)read of this post was that 40% of electricity in the Nederlands was consumed by people running Excel Spreadsheets....Need to get some more sleep....



  •  @esoterik said:

    @RogerWilco said:

    ...

    Best example and WTF I have seen, was when I learned that 40% of our Dutch national electricity grid was controlled by an Excel'95 spreadsheet running on Windows NT SP3, because it broke on anything newer and the "developer" had left over 10 years ago. This was as recent as 2007.

    That story is just crazy enough that it is probably more true than anyone wants to belive. They are going to have a real fun time when that PC finally gives out, i mean what are the chances of windows NT installing - at all - on modern hardware? I was building a test machine a year or so ago and i wanted to put all the operating systems that we supported on it. Turns out that the Win2k installer would crash and reset on the (new, preinstalled w/vista) PC i tried; i guess it didn't like something about the hardware. 

     

    I have a win95 and NT4 both installed on quite modern hardware.  Well, modern hardware abstracted by VMware a bit... Actually, if I started working there in an appropriate IT capacity, the first thing I'd do is likely P2V that thing.  *shudders* OMG, I wonder if VMware's converter agent still installs on NT4?  

     

     



  •  @dhromed said:

    Javascript doesn't have variable variables. It has a workaround for dynamic members, but PHP has an actual honest to god implementation of variable variables.

    The only difference I can see between PHP's "variable variables" and C's pointers is that you need to work a bit harder if you want to set a pointer based on user input (much as emulating INDIRECT() using OFFSET() takes some effort).

    Excel's main problem is the lack of local variables. Even if you made this formula drag around a few sub-expressions with it in hidden cells, it could only refer to them with a meaningless cell number anyway.



  • @dhromed said:

    Javascript doesn't have variable variables. It has a workaround for dynamic members, but PHP has an actual honest to god implementation of variable variables.

    ...but does it have variable variable variables?  How meta can it go? ;)



  • @Mason Wheeler said:

    @dhromed said:

    Javascript doesn't have variable variables. It has a workaround for dynamic members, but PHP has an actual honest to god implementation of variable variables.

    ...but does it have variable variable variables?  How meta can it go? ;)

    The rabbit hole is very deep indeed.



  • @Mason Wheeler said:

    @esoterik said:

    This is exactly why i say no, if someone asks a question starting with "i'm having a problem with excel ..."

    Now that i think about it, it does seem that excel is one of the last write only languages in use (that i can think of).

    What do you mean?  People are still using Perl and C++.

    I have written code in Perl and C++ that other people who are competent with Perl and C++ code have told me was quite legible.  I've even written a few things in C++, and a bunch of stuff in Perl, which caused people who are not competent in either of those languages to say, "Wow, I can actually read this."  NDA says I can't share, though.

    I could do a lot better than what Blakeyrat's coworker did up there.  However, I'd still be hard-pressed to write that as an Excel formula (or even a VBA function) in such a way that someone not familiar with VBA could understand it.

    I'll also admit that I do know a lot of people who write write-only code in perl5, and I'm not yet certain any legible perl6 code exists yet.  (OMFG, perl6.  I should write a series for the front page on perl6.  Unfortunately, I don't think I have the time.  But, having had the thought, I'll resolve to try.)

    That having been said, I have a coworker who still uses sed.  And I don't just mean for stream substitutions.  Fortunately, he's learned to not use it on anything that has to go through code review (that is, anything that'll ever reside on a production box), because nobody else will approve it.  His best luck for progress on his last 10 or so attempts was to have me be his reviewer: I'm nice; I gave him a (commented, legible) perl equivalent along with my veto.



  • @__moz said:

    Excel's main problem is the lack of local variables. Even if you made this formula drag around a few sub-expressions with it in hidden cells, it could only refer to them with a meaningless cell number anyway.
    Not true, actually. That's what named ranges are for.


Log in to reply