My New longest excel formula ever

apj101

VIP Member
=IF(OR(IF(OR(IF(ISERROR(VLOOKUP($H36,Holidays,1,0))=TRUE,1,0)),0,OR(IF($Q36="All",IF(AND(BJ$29>=VLOOKUP($H36,Holidays,2,0),BJ$29<=VLOOKUP($H36,Holidays,3,0)),1,0),0),IF($Q36="All",IF(AND(BJ$29>=VLOOKUP($H36,Holidays,4,0),BJ$29<=VLOOKUP($H36,Holidays,5,0)),1,0),0),IF($Q36="All",IF(AND(BJ$29>=VLOOKUP($H36,Holidays,6,0),BJ$29<=VLOOKUP($H36,Holidays,7,0)),1,0),0), IF($Q36="All",IF(AND(BJ$29>=VLOOKUP($H36,Holidays,8,0),BJ$29<=VLOOKUP($H36,Holidays,9,0)),1,0),0),IF($Q36="All",IF(AND(BJ$29>=VLOOKUP($H36,Holidays,10,0),BJ$29<=VLOOKUP($H36,Holidays,11,0)),1,0),0))),IF(OR(IF(ISERROR(VLOOKUP($I36,Holidays,1,0))=TRUE,1,0),LEN($I36)=0),0,OR(IF($Q36="All",IF(AND(BJ$29>=VLOOKUP($I36,Holidays,2,0),BJ$29<=VLOOKUP($I36,Holidays,3,0)),1,0),0),IF($Q36="All",IF(AND(BJ$29>=VLOOKUP($I36,Holidays,4,0),BJ$29<=VLOOKUP($I36,Holidays,5,0)),1,0),0),IF($Q36="All",IF(AND(BJ$29>=VLOOKUP($I36,Holidays,6,0),BJ$29<=VLOOKUP($I36,Holidays,7,0)),1,0),0), IF($Q36="All",IF(AND(BJ$29>=VLOOKUP($I36,Holidays,8,0),BJ$29<=VLOOKUP($I36,Holidays,9,0)),1,0),0),IF($Q36="All",IF(AND(BJ$29>=VLOOKUP($I36,Holidays,10,0),BJ$29<=VLOOKUP($I36,Holidays,11,0)),1,0),0))),IF(OR(IF(ISERROR(VLOOKUP($J36,Holidays,1,0))=TRUE,1,0),LEN($J36)=0),0,OR(IF($Q36="All",IF(AND(BJ$29>=VLOOKUP($J36,Holidays,2,0),BJ$29<=VLOOKUP($J36,Holidays,3,0)),1,0),0),IF($Q36="All",IF(AND(BJ$29>=VLOOKUP($J36,Holidays,4,0),BJ$29<=VLOOKUP($J36,Holidays,5,0)),1,0),0),IF($Q36="All",IF(AND(BJ$29>=VLOOKUP($J36,Holidays,6,0),BJ$29<=VLOOKUP($J36,Holidays,7,0)),1,0),0), IF($Q36="All",IF(AND(BJ$29>=VLOOKUP($J36,Holidays,8,0),BJ$29<=VLOOKUP($J36,Holidays,9,0)),1,0),0),IF($Q36="All",IF(AND(BJ$29>=VLOOKUP($J36,Holidays,10,0),BJ$29<=VLOOKUP($J36,Holidays,11,0)),1,0),0))),IF(OR(IF(ISERROR(VLOOKUP($K36,Holidays,1,0))=TRUE,1,0),LEN($K36)=0),0,OR(IF($Q36="All",IF(AND(BJ$29>=VLOOKUP($K36,Holidays,2,0),BJ$29<=VLOOKUP($K36,Holidays,3,0)),1,0),0),IF($Q36="All",IF(AND(BJ$29>=VLOOKUP($K36,Holidays,4,0),BJ$29<=VLOOKUP($K36,Holidays,5,0)),1,0),0),IF($Q36="All",IF(AND(BJ$29>=VLOOKUP($K36,Holidays,6,0),BJ$29<=VLOOKUP($K36,Holidays,7,0)),1,0),0), IF($Q36="All",IF(AND(BJ$29>=VLOOKUP($K36,Holidays,8,0),BJ$29<=VLOOKUP($K36,Holidays,9,0)),1,0),0),IF($Q36="All",IF(AND(BJ$29>=VLOOKUP($K36,Holidays,10,0),BJ$29<=VLOOKUP($K36,Holidays,11,0)),1,0),0))),IF(OR(IF(ISERROR(VLOOKUP($L36,Holidays,1,0))=TRUE,1,0),LEN($L36)=0),0,OR(IF($Q36="All",IF(AND(BJ$29>=VLOOKUP($L36,Holidays,2,0),BJ$29<=VLOOKUP($L36,Holidays,3,0)),1,0),0),IF($Q36="All",IF(AND(BJ$29>=VLOOKUP($L36,Holidays,4,0),BJ$29<=VLOOKUP($L36,Holidays,5,0)),1,0),0),IF($Q36="All",IF(AND(BJ$29>=VLOOKUP($L36,Holidays,6,0),BJ$29<=VLOOKUP($L36,Holidays,7,0)),1,0),0), IF($Q36="All",IF(AND(BJ$29>=VLOOKUP($L36,Holidays,8,0),BJ$29<=VLOOKUP($L36,Holidays,9,0)),1,0),0),IF($Q36="All",IF(AND(BJ$29>=VLOOKUP($L36,Holidays,10,0),BJ$29<=VLOOKUP($L36,Holidays,11,0)),1,0),0))),IF(OR(IF(ISERROR(VLOOKUP($M36,Holidays,1,0))=TRUE,1,0),LEN($M36)=0),0,OR(IF($Q36="All",IF(AND(BJ$29>=VLOOKUP($M36,Holidays,2,0),BJ$29<=VLOOKUP($M36,Holidays,3,0)),1,0),0),IF($Q36="All",IF(AND(BJ$29>=VLOOKUP($M36,Holidays,4,0),BJ$29<=VLOOKUP($M36,Holidays,5,0)),1,0),0),IF($Q36="All",IF(AND(BJ$29>=VLOOKUP($M36,Holidays,6,0),BJ$29<=VLOOKUP($M36,Holidays,7,0)),1,0),0), IF($Q36="All",IF(AND(BJ$29>=VLOOKUP($M36,Holidays,8,0),BJ$29<=VLOOKUP($M36,Holidays,9,0)),1,0),0),IF($Q36="All",IF(AND(BJ$29>=VLOOKUP($M36,Holidays,10,0),BJ$29<=VLOOKUP($M36,Holidays,11,0)),1,0),0)))),1,0)


word!
 

tremmor

Well-Known Member
Never was that advanced. Im curious. What does it do? I can't run it because im using a reader only from others. Your a player what ever. Do ya have a certification with Microsoft? looks like it.
cheers.........
 

tremmor

Well-Known Member
Access Works well. 37 yrs ago i tracked thousands of jobs for maybe 30 people. For maybe 7 yrs. Did it in excel and tracked every job, what was done, what time and shift and how long it took. Took hrs to sort. Someone converted and imported and took a second. Is a time and place for it. Whats a better database now.

Never liked Microsoft project either. Want to ramrod 2000 people for a year?
Primavara. Or Primavera . been retired to long.
 

strollin

Well-Known Member
Personally, I would never consider writing an Excel formula like that as it is virtually impossible to maintain. Not exactly sure why you need to do all those lookups but I probably would have done a vba macro instead.
 

apj101

VIP Member
Personally, I would never consider writing an Excel formula like that as it is virtually impossible to maintain. Not exactly sure why you need to do all those lookups but I probably would have done a vba macro instead.

No, im highly..HIGHLY..proficient in vba, and in this case it could not be used (not though an inability to code it but for the reason below)
the formula is going into a conditional formatting formula, you could technically still use VBA but it hugely clunky
Id also avoid using so many lookups if i could as they are one of the highest time overhead functions, especially when im not defining a specific range (such as A:A rather that A1:A99).
The alternative is to use a multiarray formula, it would be simpler, but given they are even slower that vlookups and this particular formula is used on several thousand cell as part of the conditional formula (plus the trouble using arrays in conditional formula) would have made the sheet so slow it is unworkable.
As for maintaining it, yes its crap. By tomorrow i will have forgotten how its laid out. But this sheet will be heavily locked down and i don't anticipate changes frequently.
And before you say it, no i couldn't split the logic into several rows...as its need to do that for every cell under which the conditional formula applies (i.e. add several thousand rows).
There is no way to accomplish this task given the constraints without something like that. Id be happy to share the final product with anyone who wants it.
Its a excel gantt chart
 
Top