The Longest formula ever

[AciD]

Hi, here it is :

=IF(IF(INDEX(temp.xls!A:F;SUM(MATCH("EENU";temp.xls!A:A;0);MATCH("BF304";OFFSET(temp.xls!A1;MATCH("EENU";temp.xls!A:A;0)-1;0;MATCH("FCLI";temp.xls!A:A;0)-MATCH("EENU";temp.xls!A:A;0)+1;1);0))-1;5)<>0;5;6)=5;INDEX(temp.xls!A:F;SUM(MATCH("EENU";temp.xls!A:A;0);MATCH("BF304";OFFSET(temp.xls!A1;EQUIV("EENU";temp.xls!A:A;0)-1;0;MATCH("FCLI";temp.xls!A:A;0)-MATCH("EENU";temp.xls!A:A;0)+1;1);0))-1;IF(INDEX(temp.xls!A:F;SUM(MATCH("EENU";temp.xls!A:A;0);MATCH("BF304";OFFSET(temp.xls!A1;MATCH("EENU";temp.xls!A:A;0)-1;0;MATCH("FCLI";temp.xls!A:A;0)-MATCH("EENU";temp.xls!A:A;0)+1;1);0))-1;5)<>0;5;6));-INDEX(temp.xls!A:F;SUM(MATCH("EENU";temp.xls!A:A;0);MATCH("BF304";OFFSET(temp.xls!A1;MATCH("EENU";temp.xls!A:A;0)-1;0;MATCH("FCLI";temp.xls!A:A;0)-MATCH("EENU";temp.xls!A:A;0)+1;1);0))-1;IF(INDEX(temp.xls!A:F;SUM(MATCH("EENU";temp.xls!A:A;0);MATCH("BF304";OFFSET(temp.xls!A1;MATCH("EENU";temp.xls!A:A;0)-1;0;MATCH("FCLI";temp.xls!A:A;0)-MATCH("EENU";temp.xls!A:A;0)+1;1);0))-1;5)<>0;5;6)))

My pb is i'm searching the value "BF304" with this formula but when it can't find it, it print #N/A.. so i've found a formula to convert #N/A into "0" which is more better (sorry i haven't found the exact translation for type.erreur) :

=IF(TYPE.ERREUR(all_the_upper_formula)="7";"0";"all_the_upper_formula")

the pb is the overall formula is way to big for excel :/

any idea ?

thx.

 

[Haro6]

This are my two cents....

=IF(IF(IF(IF(WEEKDAY(H12,2)=6,H12+2,IF(WEEKDAY(H12,2)=7,H12+1,(H12+(M12/24))))-INT(IF(WEEKDAY(H12,2)=6,H12+2,IF(WEEKDAY(H12,2)=7,H12+1,(H12+(M12/24)))))=MEDIAN(IF(WEEKDAY(H12,2)=6,H12+2,IF(WEEKDAY(H12,2)=7,H12+1,(H12+(M12/24))))-INT(IF(WEEKDAY(H12,2)=6,H12+2,IF(WEEKDAY(H12,2)=7,H12+1,(H12+(M12/24))))),X7:Y7),(IF(WEEKDAY(H12,2)=6,H12+2,IF(WEEKDAY(H12,2)=7,H12+1,(H12+(M12/24))))),IF(IF(WEEKDAY(H12,2)=6,H12+2,IF(WEEKDAY(H12,2)=7,H12+1,(H12+(M12/24))))-INT(IF(WEEKDAY(H12,2)=6,H12+2,IF(WEEKDAY(H12,2)=7,H12+1,(H12+(M12/24)))))=MEDIAN(IF(WEEKDAY(H12,2)=6,H12+2,IF(WEEKDAY(H12,2)=7,H12+1,(H12+(M12/24))))-INT(IF(WEEKDAY(H12,2)=6,H12+2,IF(WEEKDAY(H12,2)=7,H12+1,(H12+(M12/24))))),X8:Y8),(IF(WEEKDAY(H12,2)=6,H12+2,IF(WEEKDAY(H12,2)=7,H12+1,(H12+(M12/24)))))+AA8)),IF(IF(WEEKDAY(H12,2)=6,H12+2,IF(WEEKDAY(H12,2)=7,H12+1,(H12+(M12/24))))-INT(IF(WEEKDAY(H12,2)=6,H12+2,IF(WEEKDAY(H12,2)=7,H12+1,(H12+(M12/24)))))=MEDIAN(IF(WEEKDAY(H12,2)=6,H12+2,IF(WEEKDAY(H12,2)=7,H12+1,(H12+(M12/24))))-INT(IF(WEEKDAY(H12,2)=6,H12+2,IF(WEEKDAY(H12,2)=7,H12+1,(H12+(M12/24))))),X7:Y7),(IF(WEEKDAY(H12,2)=6,H12+2,IF(WEEKDAY(H12,2)=7,H12+1,(H12+(M12/24))))),IF(IF(WEEKDAY(H12,2)=6,H12+2,IF(WEEKDAY(H12,2)=7,H12+1,(H12+(M12/24))))-INT(IF(WEEKDAY(H12,2)=6,H12+2,IF(WEEKDAY(H12,2)=7,H12+1,(H12+(M12/24)))))=MEDIAN(IF(WEEKDAY(H12,2)=6,H12+2,IF(WEEKDAY(H12,2)=7,H12+1,(H12+(M12/24))))-INT(IF(WEEKDAY(H12,2)=6,H12+2,IF(WEEKDAY(H12,2)=7,H12+1,(H12+(M12/24))))),X8:Y8),(IF(WEEKDAY(H12,2)=6,H12+2,IF(WEEKDAY(H12,2)=7,H12+1,(H12+(M12/24)))))+AA8)),IF(IF(WEEKDAY(H12,2)=6,H12+2,IF(WEEKDAY(H12,2)=7,H12+1,(H12+(M12/24))))-INT(IF(WEEKDAY(H12,2)=6,H12+2,IF(WEEKDAY(H12,2)=7,H12+1,(H12+(M12/24)))))=MEDIAN(IF(WEEKDAY(H12,2)=6,H12+2,IF(WEEKDAY(H12,2)=7,H12+1,(H12+(M12/24))))-INT(IF(WEEKDAY(H12,2)=6,H12+2,IF(WEEKDAY(H12,2)=7,H12+1,(H12+(M12/24))))),X9:Y9),(IF(WEEKDAY(H12,2)=6,H12+2,IF(WEEKDAY(H12,2)=7,H12+1,(H12+(M12/24)))))+AA8,IF(IF(WEEKDAY(H12,2)=6,H12+2,IF(WEEKDAY(H12,2)=7,H12+1,(H12+(M12/24))))-INT(IF(WEEKDAY(H12,2)=6,H12+2,IF(WEEKDAY(H12,2)=7,H12+1,(H12+(M12/24)))))=MEDIAN(IF(WEEKDAY(H12,2)=6,H12+2,IF(WEEKDAY(H12,2)=7,H12+1,(H12+(M12/24))))-INT(IF(WEEKDAY(H12,2)=6,H12+2,IF(WEEKDAY(H12,2)=7,H12+1,(H12+(M12/24))))),X10:Y10),(IF(WEEKDAY(H12,2)=6,H12+2,IF(WEEKDAY(H12,2)=7,H12+1,(H12+(M12/24)))))+AA8+AA10))),IF(IF(IF(WEEKDAY(H12,2)=6,H12+2,IF(WEEKDAY(H12,2)=7,H12+1,(H12+(M12/24))))-INT(IF(WEEKDAY(H12,2)=6,H12+2,IF(WEEKDAY(H12,2)=7,H12+1,(H12+(M12/24)))))=MEDIAN(IF(WEEKDAY(H12,2)=6,H12+2,IF(WEEKDAY(H12,2)=7,H12+1,(H12+(M12/24))))-INT(IF(WEEKDAY(H12,2)=6,H12+2,IF(WEEKDAY(H12,2)=7,H12+1,(H12+(M12/24))))),X7:Y7),(IF(WEEKDAY(H12,2)=6,H12+2,IF(WEEKDAY(H12,2)=7,H12+1,(H12+(M12/24))))),IF(IF(WEEKDAY(H12,2)=6,H12+2,IF(WEEKDAY(H12,2)=7,H12+1,(H12+(M12/24))))-INT(IF(WEEKDAY(H12,2)=6,H12+2,IF(WEEKDAY(H12,2)=7,H12+1,(H12+(M12/24)))))=MEDIAN(IF(WEEKDAY(H12,2)=6,H12+2,IF(WEEKDAY(H12,2)=7,H12+1,(H12+(M12/24))))-INT(IF(WEEKDAY(H12,2)=6,H12+2,IF(WEEKDAY(H12,2)=7,H12+1,(H12+(M12/24))))),X8:Y8),(IF(WEEKDAY(H12,2)=6,H12+2,IF(WEEKDAY(H12,2)=7,H12+1,(H12+(M12/24)))))+AA8)),IF(IF(WEEKDAY(H12,2)=6,H12+2,IF(WEEKDAY(H12,2)=7,H12+1,(H12+(M12/24))))-INT(IF(WEEKDAY(H12,2)=6,H12+2,IF(WEEKDAY(H12,2)=7,H12+1,(H12+(M12/24)))))=MEDIAN(IF(WEEKDAY(H12,2)=6,H12+2,IF(WEEKDAY(H12,2)=7,H12+1,(H12+(M12/24))))-INT(IF(WEEKDAY(H12,2)=6,H12+2,IF(WEEKDAY(H12,2)=7,H12+1,(H12+(M12/24))))),X7:Y7),(IF(WEEKDAY(H12,2)=6,H12+2,IF(WEEKDAY(H12,2)=7,H12+1,(H12+(M12/24))))),IF(IF(WEEKDAY(H12,2)=6,H12+2,IF(WEEKDAY(H12,2)=7,H12+1,(H12+(M12/24))))-INT(IF(WEEKDAY(H12,2)=6,H12+2,IF(WEEKDAY(H12,2)=7,H12+1,(H12+(M12/24)))))=MEDIAN(IF(WEEKDAY(H12,2)=6,H12+2,IF(WEEKDAY(H12,2)=7,H12+1,(H12+(M12/24))))-INT(IF(WEEKDAY(H12,2)=6,H12+2,IF(WEEKDAY(H12,2)=7,H12+1,(H12+(M12/24))))),X8:Y8),(IF(WEEKDAY(H12,2)=6,H12+2,IF(WEEKDAY(H12,2)=7,H12+1,(H12+(M12/24)))))+AA8)),IF(IF(WEEKDAY(H12,2)=6,H12+2,IF(WEEKDAY(H12,2)=7,H12+1,(H12+(M12/24))))-INT(IF(WEEKDAY(H12,2)=6,H12+2,IF(WEEKDAY(H12,2)=7,H12+1,(H12+(M12/24)))))=MEDIAN(IF(WEEKDAY(H12,2)=6,H12+2,IF(WEEKDAY(H12,2)=7,H12+1,(H12+(M12/24))))-INT(IF(WEEKDAY(H12,2)=6,H12+2,IF(WEEKDAY(H12,2)=7,H12+1,(H12+(M12/24))))),X9:Y9),(IF(WEEKDAY(H12,2)=6,H12+2,IF(WEEKDAY(H12,2)=7,H12+1,(H12+(M12/24)))))+AA8,IF(IF(WEEKDAY(H12,2)=6,H12+2,IF(WEEKDAY(H12,2)=7,H12+1,(H12+(M12/24))))-INT(IF(WEEKDAY(H12,2)=6,H12+2,IF(WEEKDAY(H12,2)=7,H12+1,(H12+(M12/24)))))=MEDIAN(IF(WEEKDAY(H12,2)=6,H12+2,IF(WEEKDAY(H12,2)=7,H12+1,(H12+(M12/24))))-INT(IF(WEEKDAY(H12,2)=6,H12+2,IF(WEEKDAY(H12,2)=7,H12+1,(H12+(M12/24))))),X10:Y10),(IF(WEEKDAY(H12,2)=6,H12+2,IF(WEEKDAY(H12,2)=7,H12+1,(H12+(M12/24)))))+AA8+AA10))),IF(IF(WEEKDAY(H12,2)=6,H12+2,IF(WEEKDAY(H12,2)=7,H12+1,(H12+(M12/24))))-INT(IF(WEEKDAY(H12,2)=6,H12+2,IF(WEEKDAY(H12,2)=7,H12+1,(H12+(M12/24)))))=MEDIAN(IF(WEEKDAY(H12,2)=6,H12+2,IF(WEEKDAY(H12,2)=7,H12+1,(H12+(M12/24))))-INT(IF(WEEKDAY(H12,2)=6,H12+2,IF(WEEKDAY(H12,2)=7,H12+1,(H12+(M12/24))))),X10:Y10),(IF(WEEKDAY(H12,2)=6,H12+2,IF(WEEKDAY(H12,2)=7,H12+1,(H12+(M12/24)))))+AA8+AA10,IF(IF(WEEKDAY(H12,2)=6,H12+2,IF(WEEKDAY(H12,2)=7,H12+1,(H12+(M12/24))))-INT(IF(WEEKDAY(H12,2)=6,H12+2,IF(WEEKDAY(H12,2)=7,H12+1,(H12+(M12/24)))))=MEDIAN(IF(WEEKDAY(H12,2)=6,H12+2,IF(WEEKDAY(H12,2)=7,H12+1,(H12+(M12/24))))-INT(IF(WEEKDAY(H12,2)=6,H12+2,IF(WEEKDAY(H12,2)=7,H12+1,(H12+(M12/24))))),X11:Y11),(IF(WEEKDAY(H12,2)=6,H12+2,IF(WEEKDAY(H12,2)=7,H12+1,(H12+(M12/24)))))+AA8+AA10)))