The formula from hell... and audit

dkf

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.

boomzilla

Ugh. I've had to deal with these before (and have written my share). We used to do some craaaazy stuff with spreadsheets.

dhromed

Excel allows you to securely mediate.

Keith

It's a paragon of clarity compared to some of the VBA macros I've had to debug...

Nagesh

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.

skotl

@Keith said:

It's a paragon of clarity compared to some of the VBA macros I've had to debug...

This.

M_Adams

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!

anotherusername

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").