• JustinCasey (unregistered) in reply to James M
    James M:
    Though surely it would be cell Z24, not 24Z?
    Exactly! That irked me all through the story; get the cell address correct, at least!
  • Anonymous (unregistered) in reply to ounos

    WT?

  • K (unregistered)

    TRWTF is that they used CHOOSE(MATCH(...),...) instead of VLOOKUP(...). Friggin' financiers.

  • Buffled (unregistered) in reply to Auction_God

    [quote user="Auction_God"]Here's a formula I recently had to build. It is easily as long/complex as the WTF cell, but because of the formating it is easier to read and understand. I used CTRL-Enter to add lines to the actual formula.

    =IF(AND($D$1="F1",ISNUMBER($K5)),IF($K5<Pass_Retained_Capacity,"Retain Capacity,",""),"") & IF(AND($D$1="F1",ISNUMBER(Dis_2_min),ISNUMBER(Dis_2_max),ISNUMBER($J5)),IF(OR($J5<Dis_2_min,$J5>Dis_2_max),"2nd Discharge Cap,",""),"") & ... snip ... [/quote] Dude... that is not more readable, no matter how you delude yourself. Take a step back, and use your brain. I promise you, there is a better way.

    Bonus points if you can identify the industy...[/quote]

  • Elizabeth Greene (unregistered) in reply to Matt

    As best as I can tell, it determines the next date for a periodic event. 5Y is 5 years, Y is 1 year, 26W is 26 weeks, etc.

    The Designer embedded a bunch of Logic into the formula to add the correct number of days based on the code.

    If the application permitted, this would have been simpler.

    =DATE(YEAR(H$1),MONTH(H$1),Day(h$1) + Vlookup(f$1,'Interval Lookup!$A$1:$B$50',1,False)

    and created another tab with two columns. Interval Code, Number of Days 5y,=3655 Y,365 26W,=267 90D,90 4W,=4*7

    The problem with this approach is that it would have to make assumptions about the lengths of Months and years. I.e. assuming that 2 months = 61 days is wrong 1/3 of the time.

    Another approach would have used 3 Vlookups, one for years, one for months, one for days. That is probably the most legible correct solution.

    Date and time math in excel is icky.

    Here is another fun one. It Converts "Integer8" times into excel timestamps. These are 64 bit numbers representing (the number of nanoseconds since 1/1/1601)/100. Curiously this is how active directory stores timestamps.. =IF(C2>0,C2/(8.64*10^11) - 109205,"")

    -ellie

    captcha: ideo .. what I feel like for learning too much excel. Thats what I get for hanging out with accountants.

  • Marc B (unregistered) in reply to Auction_God

    It's from the airline industry, isn't it?

    Pass_Retained_Capacity = # of passengers that were on plane for entire flight Dis_2_max = Flight distance if plane makes it to destination. DCR_max = Maximum runway distance needed. ACR_max = Average runway distance needed. OCV_max = Runway distance needed if you want to crash. Shoulder_concession_height = Height (to shoulder) of flight attendants. Concession_height_min = Height of that little wheeled cart that nails me in the knee.

    Simple!

  • (cs) in reply to justsomedude
    justsomedude:
    Who says "24Z"? Ranges are referenced with the columns letter(s) before the row numbers. It's "Z24", not "24Z".

    Maybe he's a Nissan guy.

  • (cs)

    I've been using the Format Painter icon pretty much since I've been using Office.

    It always amazed me that people use these products for years and never click on anything new. I see my coworkers struggling to build Word docs and spreadsheets, doing everything the long way, and when I try to show them a faster way, they always say, "Well, I need to take a class."

    I didn't take a class. Clicking "Help" pretty much taught me everything I need to know about Word and Excel.

  • Mike (unregistered)

    "the other women who never leaned how to file a trouble ticket where baffled about how to get their problems fixed."

    Interesting how many women say they're tired of the usual stereotypes and want equality, yet they still resort to the old 'bat eyelashes' and 'sweet talk' to get men to do stuff for them. Also interesting how many men get sucked into that.

  • Zapp Brannigan (unregistered) in reply to Elizabeth Greene

    Most of the accountants I know can barely use SUM and COUNT. A few can use VLOOKUP if they have an example from from another spreadsheet and several hours to spare. A formula that complex had to be done by a college intern or an IT busybody. They will, however, do everything in a spreadsheet including database tables and word processing documents. A word to the newbies, don't spend more than 30 seconds formatting a report for accounting, they are only going to re-key it into Excel and reformat it themselves anyway.

  • DGM (unregistered) in reply to dpm
    dpm:
    MMaI:
    dpm:
    if she's drop-dead gorgeous and willing to perform sexual favours, I'd fix a sendmail.cf raw (without m4) and call it even.
    you're a bit too desperate aren't you ;)
    No, "a bit too desperate" is when you're willing to fix sendmail.cf for just money.

    WIN

  • JdFalcon04 (unregistered) in reply to bjolling
    bjolling:
    Real-modo:
    My parents had a problem with a spreadsheet my sister set up for them, and they asked me for help.

    I _____________________ and now they ___________.

    A møøse once bit my sister ... No realli!

    Mind you, møøse bites kan be pretty nasti...

  • (cs) in reply to Mike
    Mike:
    "the other women who never leaned how to file a trouble ticket where baffled about how to get their problems fixed."

    Interesting how many women say they're tired of the usual stereotypes and want equality, yet they still resort to the old 'bat eyelashes' and 'sweet talk' to get men to do stuff for them. Also interesting how many men get sucked into that.

    And if the "bat eyelashes" trick doesn't work, advance to physical contact, i.e., the casual hand on his forearm while looking him in the eye and talking animatedly, as if touching him is so comfortable and natural in her mind that she doesn't even realize she's doing it.

  • WC (unregistered)

    The Real WTF is all the hard-coded stuff in there. I've written a similar spreadsheet myself and I used the actual cells in the spreadsheet instead, so that if anyone adds a column it doesn't break the world's ugliest spreadsheet formula.

  • (cs)

    The cell formula resolves to her phone number.

  • Coward (unregistered) in reply to Auction_God
    Auction_God:
    Bonus points if you can identify the industy...

    Obvious. It's porn.

  • IHazYourCheezburger (unregistered)

    Fixing this kind of horrid Excel formulae is officially my day job. For Real. And I don't even get sexual favors.

    Captcha: tristique Aint that a fact!

  • (cs) in reply to JustinCasey
    JustinCasey:
    James M:
    Though surely it would be cell Z24, not 24Z?
    Exactly! That irked me all through the story; get the cell address correct, at least!
    Obviously, it's part of the obfuscation.
  • Franz Kafka (unregistered) in reply to Real-modo
    Real-modo:
    My parents had a problem with a spreadsheet my sister set up for them, and they asked me for help.

    I laughed and stomped all over their compooter_ and now they see me on weekends during visiting hours.

  • (cs)

    No, the suspense is killing me... won't someone please tell us what the !&" error is? I don't want to have to fight the spiders in the attic just to dig out my "Excel 1.0 for Dummies" book.

  • can't think of anything funny (unregistered) in reply to Anon

    I'm just suprised no one has mentioned yet that you can double click on the little paint-brush to continue applying the same format until you click it again.

    Saves so much time (and yes, I also use styles, and define my own, and use the Alt + Shift + left or right shortcuts to switch to header 1,2,3 etc (and up or down can re-order bullet points or word tables).

    That may have sounded a little geeky

  • (cs) in reply to can't think of anything funny
    can't think of anything funny:
    I'm just suprised no one has mentioned yet that you can double click on the little paint-brush to continue applying the same format until you click it again.

    Saves so much time (and yes, I also use styles, and define my own, and use the Alt + Shift + left or right shortcuts to switch to header 1,2,3 etc (and up or down can re-order bullet points or word tables).

    That may have sounded a little geeky

    I don't think knowing how to use Office qualifies as geeky. Knowing how to program Office, now...

  • (cs)

    Friends don't let friends do stuff in Excel.

    For the cute accountant, use a database.

    For the fancy-schmancy data analysis formula fellow, use R or maybe MatLab.

    OK, so I'm not funny. Neither is Excel.

  • Wyrd (unregistered) in reply to Anon
    tfa:
    Take, for example, that little paintbrush-looking button in Excel. Did you know it's called the “Format Painter” and can magically copy/paste cell formatting? Did you even know it was there? I didn't until recently, and holy crap is it awesome.

    Actually, I do know about it, but only because I attended a three day course on Microsoft Access. (Instructor happened to mention it while she was comparing/contrasting MS Excel and MS Access).

    Yeah, the format brush thingie is pretty sweet. Note that, that course I attended is supposed to be the sort of thing that geeks like me wouldn't need. Well, in fact, it was pretty darn helpful. (Not just for the format brush--I learned a lot of useful things about MS Access that I otherwise would not have discovered for quite some time.)

    -- Furry cows moo and decompress.

  • DiverKas (unregistered)

    TRWTF is that he edited out the part that the cubie was really Irish Girl.

    Now I ask, who wouldnt have tried to fix it?

  • A. Nonny Mouse (unregistered)

    "OK, Miss Brunette, I see what the problem is. Cell Y32 needs your phone number, so if you'd please just fill that in, we'll try it again."

  • Harrow (unregistered) in reply to Kermos
    Kermos:
    dpm:
    Kermos:
    The girl better have been drop dead gorgeous and at least performed sexual favours afterwards.
    "At least"??? What more do you want, money and a parade? Hell, if she's drop-dead gorgeous and willing to perform sexual favours, I'd fix a sendmail.cf raw (without m4) and call it even.

    Well, she could also become my permanent sexual slave. :)

    But then you would have to become her permanent EXcel formula repair slave. A few months of that and you will be ready to castrate yourself with a dull butterknife to escape the stupid.

    -Harrow.

  • Edo (unregistered) in reply to Paul
    Paul:
    Ye gods, that is a horrendous formula! The worst part is that my Inner Geek is busy trying to find an easier way to write it....

    My thoughts exactly. Ugh, there goes my break.

  • Jay (unregistered) in reply to Code Dependent
    JustinCasey:
    James M:
    Though surely it would be cell Z24, not 24Z?
    Exactly! That irked me all through the story; get the cell address correct, at least!

    Oh! I thought Cell 24Z was the room in the psych ward that he occupied after trying to debug an Excel formula.

  • Someone told me I was over there (unregistered) in reply to Wyrd
    Wyrd:
    I learned a lot of useful things about MS Access that I otherwise would not have discovered for quite some time

    Most useful thing to know about Access: close button is in top right

  • Beavis (unregistered)

    Silly nerds. You don't solve problems for fame or fortune, you solve problems because they exist.

  • Dr. Evil (unregistered) in reply to JdFalcon04
    JdFalcon04:
    bjolling:
    Real-modo:
    My parents had a problem with a spreadsheet my sister set up for them, and they asked me for help.

    I _____________________ and now they ___________.

    A møøse once bit my sister ... No realli!

    Mind you, møøse bites kan be pretty nasti...

    No realli! She was Karving her initials on the møøse with the sharpened end of an interspace tøøthbrush given her by Svenge - her brother-in-law - an Oslo dentist and star of many Norwegian møvies: "The Høt Hands of an Oslo Dentist", "Fillings of Passion", "The Huge Mølars of Horst Nordfink"...

  • Duke of New York (unregistered) in reply to justsomedude
    justsomedude:
    Who says "24Z"? Ranges are referenced with the columns letter(s) before the row numbers. It's "Z24", not "24Z".
    No, 24Z is correct for the story's purposes. "Cell Z24" doesn't have that same foreboding "Room 101" sound.
  • Lego (unregistered) in reply to Auction_God
    Auction_God:
    Here's a formula I recently had to build. It is easily as long/complex as the WTF cell, but because of the formating it is easier to read and understand. I used CTRL-Enter to add lines to the actual formula.

    =IF(AND($D$1="F1",ISNUMBER($K5)),IF($K5<Pass_Retained_Capacity,"Retain Capacity,",""),"") & IF(AND($D$1="F1",ISNUMBER(Dis_2_min),ISNUMBER(Dis_2_max),ISNUMBER($J5)),IF(OR($J5<Dis_2_min,$J5>Dis_2_max),"2nd Discharge Cap,",""),"") & IF(AND($D$1="F1",ISNUMBER(DCR_min),ISNUMBER(DCR_max),ISNUMBER($O5)),IF(OR($O5<DCR_min,$O5>DCR_max),"DCR Fail,",""),"") & IF(AND($D$1="F1",ISNUMBER(ACR_min),ISNUMBER(ACR_max),ISNUMBER($S5)),IF(OR($S5<ACR_min,$S5>ACR_max),"ACR Fail,",""),"") & IF(AND($D$1="F1",ISNUMBER(OCV_Min),ISNUMBER(OCV_Max),ISNUMBER($T5)),IF(OR($T5<OCV_Min,$T5>OCV_Max),"OCV Fail,",""),"") & IF(AND($D$1="F1",$U5<>0,OR($U5 < Shoulder_Concession_Height, $U5>Shoulder_Height_Max)),"Shoulder Height,","") & IF(AND($D$1="F1",$V5<>0,OR($V5 < Concession_Height_Min, $V5>Overall_Height_Max)),"Overall Height,","") & IF(AND($D$1="F1",ISNUMBER(Weight_Min),ISNUMBER(Weight_Max),ISNUMBER($W5)),IF(OR($W5<Weight_Min,$W5>Weight_Max),"Weight Fail,",""),"") & IF(AND($D$1="F1",LEN($C5)>0,OR(ISBLANK($K5),ISBLANK($J5),ISBLANK($O5),ISBLANK($S5),ISBLANK($T5),ISBLANK($U5),ISBLANK($V5))),"Missing Data,","")

    Bonus points if you can identify the industy...

    The industry is undoubtedly theme park rides.

    I can not tell you, however, who is in dusty.

    --Lego

    captcha: sino. Perhaps dusty is Chinese...

  • woah! (unregistered) in reply to IT Girl
    IT Girl:
    And no, before you ask, cute guys don't get preferential treatment either. ;)

    Sure they do. Its just that the definition of "cute guy" has nothing to do with looks.

    "Cute guys" are the ones with money hanging out their pockets and a office assistant under their desks.

    They don't get get any preferential treatment from you because they will never need it.

  • BigG (unregistered) in reply to AMerrickanGirl
    AMerrickanGirl:
    I've been using the Format Painter icon pretty much since I've been using Office.

    It always amazed me that people use these products for years and never click on anything new. I see my coworkers struggling to build Word docs and spreadsheets, doing everything the long way, and when I try to show them a faster way, they always say, "Well, I need to take a class."

    I didn't take a class. Clicking "Help" pretty much taught me everything I need to know about Word and Excel.

    Amen Sister (I'm assuming sister?)!!

    Apparently we're the small mintority that can teach themselves. I'm usually getting yelled at for using styles and (gasp!) autonumber in Word. Heck, I learned a lot just by playing around, and if that didn't work I'd resort to "Help".

    It's a tool, use it. Don't treat it like a neat typwriter for $Diety's Sake!

  • (cs)
    As software developers, we're seen as by the world as the Experts of All Things Computer. Want more RAM installed? Of course we can help, we know C++! Blue screen of death? Our day job calls for .NET, so we know exactly what to do! Need a monitor plugged in? That's basically the same thing as programming!

    Yes absolutely and it sucks! I've had people assume that just because I program computers that I also routinely assemble the cellphones that they use. Sigh

  • (cs) in reply to AMerrickanGirl
    AMerrickanGirl:
    It always amazed me that people use these products for years and never click on anything new. I see my coworkers struggling to build Word docs and spreadsheets, doing everything the long way, and when I try to show them a faster way, they always say, "Well, I need to take a class."

    I didn't take a class. Clicking "Help" pretty much taught me everything I need to know about Word and Excel.

    Exclamations of "there's got to be a better way" are what set the bright apart from the dull.

  • bored (unregistered) in reply to JustinCasey

    It's often industy within my cpu fan.

  • Teh Irish Gril Riot (unregistered) in reply to BigG
    BigG:
    AMerrickanGirl:
    I've been using the Format Painter icon pretty much since I've been using Office.

    It always amazed me that people use these products for years and never click on anything new. I see my coworkers struggling to build Word docs and spreadsheets, doing everything the long way, and when I try to show them a faster way, they always say, "Well, I need to take a class."

    I didn't take a class. Clicking "Help" pretty much taught me everything I need to know about Word and Excel.

    Amen Sister (I'm assuming sister?)!!

    Apparently we're the small mintority that can teach themselves. I'm usually getting yelled at for using styles and (gasp!) autonumber in Word. Heck, I learned a lot just by playing around, and if that didn't work I'd resort to "Help".

    It's a tool, use it. Don't treat it like a neat typwriter for $Diety's Sake!

    Good grief. Get a room you two.

  • (cs) in reply to Auction_God
    Auction_God:
    We made cells for one of the race teams in the Formula One race...

    Hopefully not Ferrari... :)

  • (cs) in reply to Protector one
    Protector one:
    Curious how it handles feb 29s though, with that "...*365.25+1900" in there...

    "...*365.25+1900" is going to fail when you pass 2100, which is not a leap year. 2000 was an exception to an exception to an exception to the rule (that years have 365 days).

    There MIGHT be a much more straightforward way to accomplish all this with date manipulation functions... except that no one with any experience trusts Microsoft's date handling. http://support.microsoft.com/kb/214330 http://office.microsoft.com/en-us/excel/HA102201961033.aspx etc...

  • SQL Dave (unregistered)

    ... and did you know that if you double-click the Format Painter button, you can apply the selected cells' format to multiple other cells? (You'll be in "apply format mode", where every cell or range you click on will receive the previously selected format, until you press Esc).

    Excel truly does excel... insert clever mandatory Microsoft slam here ___________________

  • (cs) in reply to cod3_complete
    cod3_complete:

    I've had people assume that just because I program computers that I also routinely assemble the cellphones that they use. Sigh

    Same with me. Some people see me as "someone which knows all about electronics and computers", and if I tell them I don't know they usually come with excuses of the type "c'mon, don't be unhelpful". I so damn hate that.

    To make it worse, I study electronics engineering. This makes people think I can repair pretty much anything which uses batteries or goes plugged into an outlet.

  • (cs) in reply to Renan_S2
    Renan_S2:
    cod3_complete:

    I've had people assume that just because I program computers that I also routinely assemble the cellphones that they use. Sigh

    Same with me. Some people see me as "someone which knows all about electronics and computers", and if I tell them I don't know they usually come with excuses of the type "c'mon, don't be unhelpful". I so damn hate that.

    To make it worse, I study electronics engineering. This makes people think I can repair pretty much anything which uses batteries or goes plugged into an outlet.

    Maybe they want to give you some free hands-on experience. Maybe you aren't busy enough as it is.

  • tunafish (unregistered) in reply to Teh Irish Gril Riot
    Teh Irish Gril Riot:
    BigG:
    AMerrickanGirl:
    I've been using the Format Painter icon pretty much since I've been using Office.

    It always amazed me that people use these products for years and never click on anything new. I see my coworkers struggling to build Word docs and spreadsheets, doing everything the long way, and when I try to show them a faster way, they always say, "Well, I need to take a class."

    I didn't take a class. Clicking "Help" pretty much taught me everything I need to know about Word and Excel.

    Amen Sister (I'm assuming sister?)!!

    Apparently we're the small mintority that can teach themselves. I'm usually getting yelled at for using styles and (gasp!) autonumber in Word. Heck, I learned a lot just by playing around, and if that didn't work I'd resort to "Help".

    It's a tool, use it. Don't treat it like a neat typwriter for $Diety's Sake!

    Good grief. Get a room you two.

    and a camera

  • Jeff (unregistered) in reply to Auction_God
    Auction_God:
    Bonus points if you can identify the industy...

    Looks like insurance - possibly some sort of rating engine?

  • Justin (unregistered) in reply to Renan_S2

    'Cause you can't? I'd say it's more about want-to. I guess I figure if I'm on the clock to help people it doesn't really matter what I'm helping them with.

    captcha: decet -- That's like when you have on just a bathrobe right?

  • titter.com (unregistered) in reply to Jasper
    Jasper:
    And...? Where's the rest of the story? Did he ask her out, they had a good time, they got married and happy, two kids and a dog? ;)
    married and happy
    happy, two kids and a dog
    ERROR: DOES_NOT_COMPUTE
  • Lumberjack (unregistered)
    “I did make a small change to 24Z a while back,” the accountant added, “but for the life of me, I can't figure out why it's not matching up billing terms.”

    Ok, what I'm dying to know is what did she change and what was going on in her head at that very moment.

Leave a comment on “Cell 24Z”

Log In or post as a guest

Replying to comment #:

Log Out

« Return to Article