How Reduce Formula

  • Hi
    Anyone know how to reduce this formula.
    I could not post the file it has 149 KB


    =SE($A20=TOROS!$A$177;PROC($A20;TOROS!$A$177;TOROS!A$232);SE($A20=TOROS!$A$234;PROC($A20;TOROS!$A$234;TOROS!A$289);SE($A20=TOROS!$A$291;PROC($A20;TOROS!$A$291;TOROS!A$346);SE($A20=TOROS!$A$348;PROC($A20;TOROS!$A$348;TOROS!A$403);SE($A20=TOROS!$A$405;PROC($A20;TOROS!$A$405;TOROS!A$460);SE($A20=TOROS!$A$465;PROC($A20;TOROS!$A$465;TOROS!A$521);SE($A20=TOROS!$A$526;PROC($A20;TOROS!$A$526;TOROS!A$582);SE($A20=TOROS!$A$587;PROC($A20;TOROS!$A$587;TOROS!A$643);SE($A20=TOROS!$A$647;PROC($A20;TOROS!$A$647;TOROS!A$703);SE($A20=TOROS!$A$708;PROC($A20;TOROS!$A$708;TOROS!A$764);SE($A20=TOROS!$A$769;PROC($A20;TOROS!$A$769;TOROS!A$825);SE($A20=TOROS!$A$830;PROC($A20;TOROS!$A$830;TOROS!A$886);SE($A20=TOROS!$A$891;PROC($A20;TOROS!$A$891;TOROS!A$947);SE($A20=TOROS!$A$952;PROC($A20;TOROS!$A$952;TOROS!A$1008);SE($A20=TOROS!$A$1013;PROC($A20;TOROS!$A$1013;TOROS!A$1069);SE($A20=TOROS!$A$1074;PROC($A20;TOROS!$A$1074;TOROS!A$1130);SE($A20=TOROS!$A$1135;PROC($A20;TOROS!$A$1135;TOROS!A$1191);SE($A20=TOROS!$A$1196;PROC($A20;TOROS!$A$1196;TOROS!A$1252);SE($A20=TOROS!$A$1257;PROC($A20;TOROS!$A$1257;TOROS!A$1313);SE($A20=TOROS!$A$1318;PROC($A20;TOROS!$A$1318;TOROS!A$1374);SE($A20=TOROS!$A$1379;PROC($A20;TOROS!$A$1379;TOROS!A$1435);SE($A20=TOROS!$A$1440;PROC($A20;TOROS!$A$1440;TOROS!A$1496);SE($A20=TOROS!$A$1501;PROC($A20;TOROS!$A$1501;TOROS!A$1557);SE($A20=TOROS!$A$1562;PROC($A20;TOROS!$A$1562;TOROS!A$1618);SE($A20=TOROS!$A$1623;PROC($A20;TOROS!$A$1623;TOROS!A$1679);SE($A20=TOROS!$A$1684;PROC($A20;TOROS!$A$1684;TOROS!A$1740);SE($A20=TOROS!$A$1745;PROC($A20;TOROS!$A$1745;TOROS!A$1801);SE($A20=TOROS!$A$1806;PROC($A20;TOROS!$A$1806;TOROS!A$1862);SE($A20=TOROS!$A$1867;PROC($A20;TOROS!$A$1867;TOROS!A$1923);SE($A20=TOROS!$A$1928;PROC($A20;TOROS!$A$1928;TOROS!A$1984);SE($A20=TOROS!$A$1989;PROC($A20;TOROS!$A$1989;TOROS!A$2045);SE($A20=TOROS!$A$2050;PROC($A20;TOROS!$A$2050;TOROS!A$2106);SE($A20=TOROS!$A$2111;PROC($A20;TOROS!$A$2111;TOROS!A$2167);SE($A20=TOROS!$A$2172;PROC($A20;TOROS!$A$2172;TOROS!A$2228);SE($A20=TOROS!$C$2233;PROC($A20;TOROS!$C$2233;TOROS!A$2289);SE($A20=TOROS!$A$2294;PROC($A20;TOROS!$A$2294;TOROS!A$2350);SE($A20=TOROS!$A$2355;PROC($A20;TOROS!$A$2355;TOROS!A$2411);SE($A20=TOROS!$A$2416;PROC($A20;TOROS!$A$2416;TOROS!A$2472);SE($A20=TOROS!$A$2477;PROC($A20;TOROS!$A$2477;TOROS!A$2533);SE($A20=TOROS!$A$2538;PROC($A20;TOROS!$A$2538;TOROS!A$2594);SE($A20=TOROS!$A$2599;PROC($A20;TOROS!$A$2599;TOROS!A$2655);SE($A20=TOROS!$A$2660;PROC($A20;TOROS!$A$2660;TOROS!A$2716);SE($A20=TOROS!$A$2722;PROC($A20;TOROS!$A$2722;TOROS!A$2778);SE($A20=TOROS!$A$2784;PROC($A20;TOROS!$A$2784;TOROS!A$2840);SE($A20=TOROS!$A$2845;PROC($A20;TOROS!$A$2845;TOROS!A$2901);SE($A20=TOROS!$A$2906;PROC($A20;TOROS!$A$2906;TOROS!A$2962);SE($A20=TOROS!$A$2967;PROC($A20;TOROS!$A$2967;TOROS!A$3023);SE($A20=TOROS!$A$3028;PROC($A20;TOROS!$A$3028;TOROS!A$3084)))))))))))))))))))))))))))))))))))))))))))))))))

  • Re: How Reduce Formula


    Hi Marreco


    This is quite possibly the worst formula I have seen in all my time. Have you heard of a little program called win zip? Have you made an attempt to make your file smaller to squeeze in on by itself. I sincerly encourage you to endevour to put a file with the above formula cause in my opinion no one is going to take the time to decipher this one. Also you might want to point out what you want the above to do. The file should do that though.


    Take care


    Smallman

  • Re: How Reduce Formula


    marreco,


    Please do not use quote tags (or code tags) with formulas. Just post the formula without any tags.


    As for uploading the file: you only need to post a sample with enough data to illustrate the problem - not the complete file.


    You will help yourself by providing more explicit explanations about your requirements, rather than making those helping you make guesses about the formula or your needs.


    IF the functions SE and PROC are custom functions then post the code. They are not standard Excel functions, at least as far as I can tell.

  • Re: How Reduce Formula


    I assume this is a Brazilian version of Excel, or Portuguese so SE = IF and PROC = LOOKUP


    That doesn't really explain the formula, though, because it's a series of IF functions like


    =IF($A20=TOROS!$A$177;LOOKUP($A20;TOROS!$A$177;TOROS!A$232)


    That uses LOOKUP with a lookup range of a single cell (!) so can be reduced to this


    =SE($A20=TOROS!$A$177;TOROS!A$232)


    and you can do the same throughout the formula.


    You could reduce considerably if there was some pattern to the arrangements....but there appear to be slight variations, e.g. the above tests A20 against A177 and if equal returns the value from 55 cells further down the column......but later in the formula this changes to 56...and the cells being tested are also variable distances apart. If you make the pattern consistent then you could use a simple one line formula........

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!