The formula from hell... and audit
-
Because we love formulæ in Excel spreadsheets, I present… the nastiest formula I've seen in quite a long time.
It's just missing some cross-workbook references to be a total screaming horror.
-
Ugh. I've had to deal with these before (and have written my share). We used to do some craaaazy stuff with spreadsheets.
-
Excel allows you to securely mediate.
-
It's a paragon of clarity compared to some of the VBA macros I've had to debug...
-
Well that is nothing. We have one formula in a Lotus 123 spreadsheet that is messier than this and we have to support that because if we didn't entire financial system will crumble down to mere dust.
Fiddled Under: Now channelizing topper.
-
-
Fixing those sort of spreadsheets is one of my major functions
There are a couple of spreadsheets that have nearly made me slide under my desk whimpering. They were originally provided by the company responsible for our ERMS.
Filed Under: Microsoft Query must die!
-
The main problem is that it's extremely repetitive. The same exact thing is repeated over and over, with very minor differences.
I can reduce it down to this:
=IF(F6="050",tiers!$D$21+tiers!$D$29-tiers!$D$26,0)+IF(OR(E6="W",E6="X"),VLOOKUP("N"&IF(F6="050",100,F6*1)&VLOOKUP(L6,tiers!$B$38:$C$49,2,0)&"N/A"&M6/12&"/"&N6&"0-"&R6/1000&C6*1,tiers!$L$2:$W$20969,12,0),IF(E6="P",VLOOKUP("E"&IF(F6="050",100,F6*1)&VLOOKUP(L6,tiers!$B$52:$C$55,2,0)&"N/A"&IF(AND(M6=36,N6=60),M6,M6/12)&"/"&N6&VLOOKUP(R6,tiers!$B$57:$C$59,2,0)&C6*1,tiers!$L$2:$W$20969,12,0),VLOOKUP(B6&IF(F6="050",100,F6*1)&VLOOKUP(L6,tiers!$B$38:$C$49,2,0)&IF(AND(E6="*",M6=6),IF(AND(F6<>"050",OR(L6="PPH08",L6="PTH08")),"0-9",IF(Q6=48,"0-4","6-10")&M6&"/"&N6&IF(AND(F6="050",R6=125000),"100-125","0-100")&C6*1,tiers!$L$2:$W$20969,12,0),IF(E6="*",IF(OR(L6="PPH08",L6="PTH08"),"0-9",IF(Q6=48,"0-4","6-10"))&M6&"/"&N6&IF(AND(OR(L6="PPH08",L6="PTH08"),R6=100000),"0-100",VLOOKUP(R6,tiers!$B$64:$C$70,2,0))&C6*1,tiers!$L$2:$W$20969,12,0),"ERROR"))))
That's about 78% shorter. Can anyone do better (or find a mistake I've made)?
Note that in some cases, you'll get #VALUE! when the original code would have resulted in "ERROR". As far as I'm concerned, that's not any different since they're both indicating the same error (and the original code would result in #VALUE! instead of "ERROR" if F6="050" anyway; you'd never see "ERROR" unless F6<>"050").
-
Can anyone do better (or find a mistake I've made)?
First, give me a reason to care a lot. Like, a lot.
-
First, give me a reason to care a lot. Like, a lot.
-
Fiendish.
-
Because... well, I might have been WRONG... and, your duty, yaknow...
-
Suggested Discourse extension: capturing all XKCD images and replacing them with goatse.
Filed under: it's about as outdated, meta, meta meta
-
Suggested Discourse extension: capturing all xkcd images and replacing them with
goatseRosie O'Donnell.FTFY
-
Post it on codegolf.stackexchange.com