+ Reply to Thread
Results 1 to 41 of 41

Longest Formula Ever

  1. #1
    Forum Contributor
    Join Date
    07-18-2013
    Location
    United States
    MS-Off Ver
    Excel 2010
    Posts
    123

    Longest Formula Ever

    Just for giggles !

    =IF(E7=M78,SUM(I7:J12)/12,IF(E7=M79,SUM(I7:J12)/6,IF(E7=M80,SUM(I7:J12)/4,IF(E7=M81,SUM(I7:J12)/2,IF(E7=M82,SUM(I7:J12),IF(E7=M83,SUM(I7:J12)*2,IF(E7=M84,SUM(I7:J12)*26/12,IF(E7=M85,SUM(I7:J12)*4,IF(E8=M78,SUM(I7:J12)/12,IF(E8=M79,SUM(I7:J12)/6,IF(E8=M80,SUM(I7:J12)/4,IF(E8=M81,SUM(I7:J12)/2,IF(E8=M82,SUM(I7:J12),IF(E8=M83,SUM(I7:J12)*2,IF(E8=M84,SUM(I7:J12)*26/12,IF(E8=M85,SUM(I7:J12)*4,IF(E9=M78,SUM(I7:J12)/12,IF(E9=M79,SUM(I7:J12)/6,IF(E9=M80,SUM(I7:J12)/4,IF(E9=M81,SUM(I7:J12)/2,IF(E9=M82,SUM(I7:J12),IF(E9=M83,SUM(I7:J12)*2,IF(E9=M84,SUM(I7:J12)*26/12,IF(E9=M85,SUM(I7:J12)*4,IF(E10=M78,SUM(I7:J12)/12,IF(E10=M79,SUM(I7:J12)/6,IF(E10=M80,SUM(I7:J12)/4,IF(E10=M81,SUM(I7:J12)/2,IF(E10=M82,SUM(I7:J12),IF(E10=M83,SUM(I7:J12)*2,IF(E10=M84,SUM(I7:J12)*26/12,IF(E10=M85,SUM(I7:J12)*4,IF(E11=M78,SUM(I7:J12)/12,IF(E11=M79,SUM(I7:J12)/6,IF(E11=M80,SUM(I7:J12)/4,IF(E11=M81,SUM(I7:J12)/2,IF(E11=M82,SUM(I7:J12),IF(E11=M83,SUM(I7:J12)*2,IF(E11=M84,SUM(I7:J12)*26/12,IF(E11=M85,SUM(I7:J12)*4,IF(E12=M78,SUM(I7:J12)/12,IF(E12=M79,SUM(I7:J12)/6,IF(E12=M80,SUM(I7:J12)/4,IF(E12=M81,SUM(I7:J12)/2,IF(E12=M82,SUM(I7:J12),IF(E12=M83,SUM(I7:J12)*2,IF(E12=M84,SUM(I7:J12)*26/12,IF(E12=M85,SUM(I7:J12)*4
    ))))))))))))))))))))))))))))))))))))))))))))))))

    !!!!!!!!!!

  2. #2
    Forum Expert Mordred's Avatar
    Join Date
    07-06-2010
    Location
    Winnipeg, Canada
    MS-Off Ver
    2007, 2010
    Posts
    2,787

    Re: Longest Formula Ever

    I think I just threw up a little into my mouth!
    If you're happy with someone's help, click that little star at the bottom left of their post to give them Reps.

    ---Keep on Coding in the Free World---

  3. #3
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Longest Formula Ever

    for giggles??

    how would you ever edit that if you needed to add something, or is it wasnt giving what you expected?

    For starters, you have a constant in there that could be used once instead of about 48 times...
    SUM(I7:J12)/12,IF(E7=M79,
    SUM(I7:J12)/6,IF(E7=M80,
    SUM(I7:J12)/4,IF(E7=M81,
    SUM(I7:J12)/2,IF(E7=M82,
    SUM(I7:J12),IF(E7=M83,
    SUM(I7:J12)*2,IF(E7=M84,
    SUM(I7:J12)*26/12,IF(E7=M85,

    depending on the value in E7/8/9 etc and M78/85, Im sure you could devise a formula that would give you a value to apply to the sum, without resorting to that monstrosity (and that would save Mordred's breakfast lol)

    It's been my experience that despite how impressive and ""cool/wow" formulas like that look, they cause more problems than a series of simple helper columns...and in a LOT of cases, with a bit more thought, can be condensed by orders of magnitude to something far more manageable
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  4. #4
    Forum Expert Pepe Le Mokko's Avatar
    Join Date
    05-14-2009
    Location
    Belgium
    MS-Off Ver
    O365 v 2402
    Posts
    13,443

    Re: Longest Formula Ever

    First THINK, THEN compute

  5. #5
    Forum Expert JapanDave's Avatar
    Join Date
    06-10-2008
    Location
    The grid, I got in!
    MS-Off Ver
    Excel 2010/13
    Posts
    1,696

    Re: Longest Formula Ever

    I hate formulas and this just reinforces that.
    Be fore warned, I regularly post drunk. So don't take offence (too much) to what I say.
    I am the real 'Napster'
    The Grid. A digital frontier. I tried to picture clusters of information as they moved through the computer. What did they look like? Ships? motorcycles? Were the circuits like freeways? I kept dreaming of a world I thought I'd never see. And then, one day...

    If you receive help please give thanks. Click the * in the bottom left hand corner.

    snb's VBA Help Files

  6. #6
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Longest Formula Ever

    I've seen, and have probably written, longer formulas than that!
    Biff
    Microsoft MVP Excel
    Keep It Simple Stupid

    Let's Go Pens. We Want The Cup.

  7. #7
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Longest Formula Ever

    Likewise Tony, but you have to admit, there are almost always easier ways

  8. #8
    Forum Expert
    Join Date
    02-14-2009
    Location
    .
    MS-Off Ver
    ................
    Posts
    2,840

    Re: Longest Formula Ever

    Can't help this - even though it is off the board...

    =TRIM(CONCATENATE(IFERROR(LOOKUP(MID(TEXT(A12,0),LOOKUP(LEN(TEXT(A12,0)),{10},{1}),1),{"0","1","2","3","4","5","6","7","8","9"},{"","One Hundred","Two Hundred","Three Hundred","Four Hundred","Five Hundred","Six Hundred","Seven Hundred","Eight Hundred","Nine Hundred"}),"")," ",IFERROR(IF(MID(TEXT(A12,0),LOOKUP(LEN(TEXT(A12,0)),{9,10},{1,2}),1)="1",LOOKUP(MID(TEXT(A12,0),LOOKUP(LEN(TEXT(A12,0)),{9,10},{2,3}),1),{"0","1","2","3","4","5","6","7","8","9"},{"Ten","Eleven Crore","Twelve Crore","Thirteen Crore","Fourteen Crore","Fifteen Crore","Sixteen Crore","Seventeen Crore","Eighteen Crore","Nineteen Crore"}),LOOKUP(MID(TEXT(A12,0),LOOKUP(LEN(TEXT(A12,0)),{9,10},{1,2}),1),{"0","1","2","3","4","5","6","7","8","9"},{"","","Twenty","Thirty","Forty","Fifty","Sixty","Seventy","Eighty","Ninety"})),"")," ",IFERROR(IF(MID(TEXT(A12,0),LOOKUP(LEN(TEXT(A12,0)),{8,9,10},{11,1,2}),1)="1",LOOKUP(MID(TEXT(A12,0),LOOKUP(LEN(TEXT(A12,0)),{8,9,10,11},{11,1,2,3}),1),{"0","1","2","3","4","5","6","7","8","9"},{"","","","","","","","","",""}),LOOKUP(MID(TEXT(A12,0),LOOKUP(LEN(TEXT(A12,0)),{7,8,9,10},{12,1,2,3}),1),{"0","1","2","3","4","5","6","7","8","9"},{"Crore","One Crore","Two Crore","Three Crore","Four Crore","Five Crore","Six Crore","Seven Crore","Eight Crore","Nine Crore"})),"")," ",IFERROR(IF(MID(TEXT(A12,0),LOOKUP(LEN(TEXT(A12,0)),{7,8,9,10},{1,2,3,4}),1)="1",LOOKUP(MID(TEXT(A12,0),LOOKUP(LEN(TEXT(A12,0)),{7,8,9,10},{2,3,4,5}),1),{"0","1","2","3","4","5","6","7","8","9"},{"Ten","Eleven Lac","Twelve Lac","Thirteen Lac","Fourteen Lac","Fifteen Lac","Sixteen Lac","Seventeen Lac","Eighteen Lac","Nineteen Lac"}),LOOKUP(MID(TEXT(A12,0),LOOKUP(LEN(TEXT(A12,0)),{7,8,9,10},{1,2,3,4}),1),{"0","1","2","3","4","5","6","7","8","9"},{"","","Twenty","Thirty","Forty","Fifty","Sixty","Seventy","Eighty","Ninety"})),"")," ",IFERROR(IF(MID(TEXT(A12,0),LOOKUP(LEN(TEXT(A12,0)),{6,7,8,9,10},{11,1,2,3,4}),1)="1",LOOKUP(MID(TEXT(A12,0),LOOKUP(LEN(TEXT(A12,0)),{6,7,8,9,10},{11,2,3,4,5}),1),{"0","1","2","3","4","5","6","7","8","9"},{"","","","","","","","","",""}),LOOKUP(MID(TEXT(A12,0),LOOKUP(LEN(TEXT(A12,0)),{5,6,7,8,9,10},{11,1,2,3,4,5}),1),{"0","1","2","3","4","5","6","7","8","9"},{"Lac","One Lac","Two Lac","Three Lac","Four Lac","Five Lac","Six Lac","Seven Lac","Eight Lac","Nine Lac"})),"")," ",IFERROR(IF(MID(TEXT(A12,0),LOOKUP(LEN(TEXT(A12,0)),{5,6,7,8,9,10},{1,2,3,4,5,6}),1)="1",LOOKUP(MID(TEXT(A12,0),LOOKUP(LEN(TEXT(A12,0)),{4,5,6,7,8,9,10},{1,2,3,4,5,6,7}),1),{"0","1","2","3","4","5","6","7","8","9"},{"Ten Thousand","Eleven Thousand","Twelve Thousand","Thirteen Thousand","Forteen Thousand","Fifteen Thousand","Sixteen Thousand","Seventeen Thousand","Eighteen Thousand","Nineteen Thousand"}),LOOKUP(MID(TEXT(A12,0),LOOKUP(LEN(TEXT(A12,0)),{5,6,7,8,9,10},{1,2,3,4,5,6}),1),{"0","1","2","3","4","5","6","7","8","9"},{"","","Twenty","Thirty","Forty","Fifty","Sixty","Seventy","Eighty","Ninety"})),"")," ",IFERROR(IF(MID(TEXT(A12,0),LOOKUP(LEN(TEXT(A12,0)),{4,5,6,7,8,9,10},{12,1,2,3,4,5,6}),1)="0",LOOKUP(MID(TEXT(A12,0),LOOKUP(LEN(TEXT(A12,0)),{4,5,6,7,8,9,10},{1,2,3,4,5,6,7}),1),{"0","1","2","3","4","5","6","7","8","9"},{"","One Thousand","Two Thousand","Three Thousand","Four Thousand","Five Thousand","Six Thousand","Seven Thousand","Eight Thousand","Nine Thousand"}),IF(MID(TEXT(A12,0),LOOKUP(LEN(TEXT(A12,0)),{4,5,6,7,8,9,10},{12,1,2,3,4,5,6}),1)="1",LOOKUP(MID(TEXT(A12,0),3,1),{"0","1","2","3","4","5","6","7","8","9"},{"","","","","","","","","",""}),LOOKUP(MID(TEXT(A12,0),LOOKUP(LEN(TEXT(A12,0)),{4,5,6,7,8,9,10},{1,2,3,4,5,6,7}),1),{"0","1","2","3","4","5","6","7","8","9"},{"Thousand","One Thousand","Two Thousand","Three Thousand","Four Thousand","Five Thousand","Six Thousand","Seven Thousand","Eight Thousand","Nine Thousand"}))),"")," ",IFERROR(LOOKUP(MID(TEXT(A12,0),LOOKUP(LEN(TEXT(A12,0)),{3,4,5,6,7,8,9,10},{1,2,3,4,5,6,7,8,9}),1),{"0","1","2","3","4","5","6","7","8","9"},{"","One Hundred","Two Hundred","Three Hundred","Four Hundred","Five Hundred","Six Hundred","Seven Hundred","Eight Hundred","Nine Hundred"}),"")," ",IFERROR(IF(MID(TEXT(A12,0),LOOKUP(LEN(TEXT(A12,0)),{1,2,3,4,5,6,7,8,9,10},{11,1,2,3,4,5,6,7,8,9}),1)="1",LOOKUP(MID(TEXT(A12,0),LOOKUP(LEN(TEXT(A12,0)),{1,2,3,4,5,6,7,8,9,10},{1,2,3,4,5,6,7,8,9,10}),1),{"0","1","2","3","4","5","6","7","8","9"},{"Ten","Eleven","Twelve","Thirteen","Fourteen","Fifteen","Sixteen","Seventeen","Eighteen","Nineteen"}),LOOKUP(MID(TEXT(A12,0),LOOKUP(LEN(TEXT(A12,0)),{1,2,3,4,5,6,7,8,9,10},{11,1,2,3,4,5,6,7,8,9}),1),{"0","1","2","3","4","5","6","7","8","9"},{"","","Twenty","Thirty","Forty","Fifty","Sixty","Seventy","Eighty","Ninety"})),"")," ",IFERROR(IF(MID(TEXT(A12,0),LOOKUP(LEN(TEXT(A12,0)),{1,2,3,4,5,6,7,8,9,10},{11,1,2,3,4,5,6,7,8,9}),1)="1",LOOKUP(MID(TEXT(A12,0),LOOKUP(LEN(TEXT(A12,0)),{1,2,3,4,5,6,7,8,9,10},{1,2,3,4,5,6,7,8,9,10}),1),{"0","1","2","3","4","5","6","7","8","9"},{"","","","","","","","","",""}),LOOKUP(MID(TEXT(A12,0),LOOKUP(LEN(TEXT(A12,0)),{1,2,3,4,5,6,7,8,9,10},{1,2,3,4,5,6,7,8,9,10}),1),{"0","1","2","3","4","5","6","7","8","9"},{"","One","Two","Three","Four","Five","Six","Seven","Eight","Nine"})),"")," ","Rupee"," ",IFERROR(IF(LEN(FIND(".",A12))>0,"And",""),"")," ",IFERROR(IF(MID(A12,FIND(".",A12)+1,2)="1","Ten Paisa",""),"")," ",IFERROR(IF(MID(A12,FIND(".",A12)+1,1)="1",LOOKUP(MID(A12,FIND(".",A12)+2,1),{"0","1","2","3","4","5","6","7","8","9"},{"Ten","Eleven Paisa","Twelve Paisa","Thirteen Paisa","Fourteen Paisa","Fifteen Paisa","Sixteen Paisa","Seventeen Paisa","Eighteen Paisa","Nineteen Paisa"}),LOOKUP(MID(A12,FIND(".",A12)+1,1),{"0","1","2","3","4","5","6","7","8","9"},{"","","Twenty","Thirty","Forty","Fifty","Sixty","Seventy","Eighty","Ninety"})),"")," ",IFERROR(IF(MID(A12,FIND(".",A12)+1,1)="1",LOOKUP(MID(A12,FIND(".",A12)+2,1),{"0","1","2","3","4","5","6","7","8","9"},{"","","","","","","","","",""}),LOOKUP(MID(A12,FIND(".",A12)+2,1),{"0","1","2","3","4","5","6","7","8","9"},{"","One Paisa","Two Paisa","Three Paisa","Four Paisa","Five Paisa","Six Paisa","Seven Paisa","Eight Paisa","Nine Paisa"})),"")," ","Only."))


    http://www.ozgrid.com/forum/showthre...494#post681494

  9. #9
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Longest Formula Ever



    They had room for about 2000 more characters!

  10. #10
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Longest Formula Ever

    Tony, I dont even want to know how you figured that 1 out lol

  11. #11
    Forum Expert JapanDave's Avatar
    Join Date
    06-10-2008
    Location
    The grid, I got in!
    MS-Off Ver
    Excel 2010/13
    Posts
    1,696

    Re: Longest Formula Ever

    So that formula trims some spaces... So what.

  12. #12
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Longest Formula Ever

    I didn't goto the link (Ozgrid is one of the ugliest websites I've ever seen) but that formula looks like it's one of those "words to numbers/numbers to words" functions that are popular in Asia.
    Last edited by Tony Valko; 09-01-2013 at 05:19 PM. Reason: added a missing word!

  13. #13
    Forum Expert JapanDave's Avatar
    Join Date
    06-10-2008
    Location
    The grid, I got in!
    MS-Off Ver
    Excel 2010/13
    Posts
    1,696

    Re: Longest Formula Ever

    Quote Originally Posted by Tony Valko View Post
    I didn't goto the link (Ozgrid is one of the ugliest websites I've ever seen) but that formula looks like it's one of those "words to numbers/numbers to words" functions that are popular in Asia.
    Tony, I was just kidding. I started to get nauseated only 1/4 of the way through it, although it does work pretty good.

  14. #14
    Forum Contributor
    Join Date
    07-17-2012
    Location
    N/A
    MS-Off Ver
    Excel 2010
    Posts
    218

    Re: Longest Formula Ever

    Quote Originally Posted by Tony Valko View Post
    ...Ozgrid is one of the ugliest websites I've ever seen...
    Is that strange thing to say? OzGrid and ExcelForum run same software and the even the color scheme is same.

    Don't want start disagreement, just my comment

  15. #15
    Forum Guru romperstomper's Avatar
    Join Date
    11-04-2008
    Location
    A1
    MS-Off Ver
    Most
    Posts
    12,302

    Re: Longest Formula Ever

    Ozgrid used to look very different. (as did VBAX, only more so!)
    Remember what the dormouse said
    Feed your head

  16. #16
    Forum Contributor
    Join Date
    07-17-2012
    Location
    N/A
    MS-Off Ver
    Excel 2010
    Posts
    218

    Re: Longest Formula Ever

    Ahhh - see from WayBackMachine

    but that change 3 year ago! Link is last old style page on wayback,

  17. #17
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Longest Formula Ever

    Quote Originally Posted by lesoies View Post
    Ahhh - see from WayBackMachine

    but that change 3 year ago! Link is last old style page on wayback,
    It was even uglier than that, believe it or not.

    The design was terrible and the pages were crammed full of ads.

    That's why I never participated there.

  18. #18
    Forum Expert daffodil11's Avatar
    Join Date
    07-11-2013
    Location
    Phoenixville, PA
    MS-Off Ver
    MS Office 2016
    Posts
    4,465

    Re: Longest Formula Ever

    My personal best:

    =(LEFT(VLOOKUP('Process Measures Report Card'!N$3,TrainingProcessIndex!$C$3:$Q$200,$C26,0),FIND(":",VLOOKUP('Process Measures Report Card'!N$3,TrainingProcessIndex!$C$3:$Q$200,$C26,0))-1)/MID(VLOOKUP('Process Measures Report Card'!N$3,TrainingProcessIndex!$C$3:$Q$200,$C26,0),FIND(":",VLOOKUP('Process Measures Report Card'!N$3,TrainingProcessIndex!$C$3:$Q$200,$C26,0))+1,LEN(VLOOKUP('Process Measures Report Card'!N$3,TrainingProcessIndex!$C$3:$Q$200,$C26,0))-FIND(":",VLOOKUP('Process Measures Report Card'!N$3,TrainingProcessIndex!$C$3:$Q$200,$C26,0))))-(LEFT(VLOOKUP(DATE(YEAR('Process Measures Report Card'!N$3)-1,MONTH('Process Measures Report Card'!N$3),DAY('Process Measures Report Card'!N$3)),TrainingProcessIndex!$C$3:$Q$200,$C26,0),FIND(":",VLOOKUP(DATE(YEAR('Process Measures Report Card'!N$3)-1,MONTH('Process Measures Report Card'!N$3),DAY('Process Measures Report Card'!N$3)),TrainingProcessIndex!$C$3:$Q$200,$C26,0))-1)/MID(VLOOKUP(DATE(YEAR('Process Measures Report Card'!N$3)-1,MONTH('Process Measures Report Card'!N$3),DAY('Process Measures Report Card'!N$3)),TrainingProcessIndex!$C$3:$Q$200,$C26,0),FIND(":",VLOOKUP(DATE(YEAR('Process Measures Report Card'!N$3)-1,MONTH('Process Measures Report Card'!N$3),DAY('Process Measures Report Card'!N$3)),TrainingProcessIndex!$C$3:$Q$200,$C26,0))+1,LEN(VLOOKUP(DATE(YEAR('Process Measures Report Card'!N$3)-1,MONTH('Process Measures Report Card'!N$3),DAY('Process Measures Report Card'!N$3)),TrainingProcessIndex!$C$3:$Q$200,$C26,0))-FIND(":",VLOOKUP(DATE(YEAR('Process Measures Report Card'!N$3)-1,MONTH('Process Measures Report Card'!N$3),DAY('Process Measures Report Card'!N$3)),TrainingProcessIndex!$C$3:$Q$200,$C26,0))))

    It vlookups a current month's values stored as text such as "75:80" (read: 75 out of 80), converts it to a fraction, vlookups the previous month's data, converts that, then then compares them.


    I guess I'm still a rookie.
    Last edited by daffodil11; 09-03-2013 at 04:10 PM.

  19. #19
    Forum Guru romperstomper's Avatar
    Join Date
    11-04-2008
    Location
    A1
    MS-Off Ver
    Most
    Posts
    12,302

    Re: Longest Formula Ever

    The phrase "helper cells" does spring to mind.

  20. #20
    Forum Expert daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,675

    Re: Longest Formula Ever

    Rather than painfully extracting 75 and 80 from 75:80 you can use SUBSTITUTE to replace ":" with "/" and if you add "0 " to the front you get

    0 75/80

    which excel will recognise as a fraction if you co-erce it, so you can replace this part at the start

    =(LEFT(VLOOKUP('Process Measures Report Card'!N$3,TrainingProcessIndex!$C$3:$Q$200,$C26,0),FIND(":",VLOOKUP('Process Measures Report Card'!N$3,TrainingProcessIndex!$C$3:$Q$200,$C26,0))-1)/MID(VLOOKUP('Process Measures Report Card'!N$3,TrainingProcessIndex!$C$3:$Q$200,$C26,0),FIND(":",VLOOKUP('Process Measures Report Card'!N$3,TrainingProcessIndex!$C$3:$Q$200,$C26,0))+1,LEN(VLOOKUP('Process Measures Report Card'!N$3,TrainingProcessIndex!$C$3:$Q$200,$C26,0))-FIND(":",VLOOKUP('Process Measures Report Card'!N$3,TrainingProcessIndex!$C$3:$Q$200,$C26,0))))

    with this:

    =(0+("0 "&SUBSTITUTE(VLOOKUP('Process Measures Report Card'!N$3,TrainingProcessIndex!$C$3:$Q$200,$C26,0),":","/")))
    Audere est facere

  21. #21
    Forum Expert daffodil11's Avatar
    Join Date
    07-11-2013
    Location
    Phoenixville, PA
    MS-Off Ver
    MS Office 2016
    Posts
    4,465

    Re: Longest Formula Ever

    You slay me, DLL.

    Truncation has never been my strong suit. I will be sure to implement this in next week's report.

  22. #22
    Registered User
    Join Date
    08-21-2013
    Location
    UT
    MS-Off Ver
    Excel 2013
    Posts
    11

    Re: Longest Formula Ever

    seems legit, hah

  23. #23
    Forum Expert daffodil11's Avatar
    Join Date
    07-11-2013
    Location
    Phoenixville, PA
    MS-Off Ver
    MS Office 2016
    Posts
    4,465

    Re: Longest Formula Ever

    Someone needed a summary sheet of SUMIFs, pulling totals for each table from 52 worksheets.

    It was a little painful.

    =sumifs('Jan 6-8'!$M$5:$M$9,'Jan 6-8'!$L$5:$L$9,Summary!$C3)+sumifs('Jan 13-15'!$M$5:$M$9,'Jan 13-15'!$L$5:$L$9,Summary!$C3)+sumifs('Jan 20-22'!$M$5:$M$9,'Jan 20-22'!$L$5:$L$9,Summary!$C3)+sumifs('Jan 27-29'!$M$5:$M$9,'Jan 27-29'!$L$5:$L$9,Summary!$C3)+sumifs('Feb 3-5'!$M$5:$M$9,'Feb 3-5'!$L$5:$L$9,Summary!$C3)+sumifs('Feb 10-12'!$M$5:$M$9,'Feb 10-12'!$L$5:$L$9,Summary!$C3)+sumifs('Feb 17-19'!$M$5:$M$9,'Feb 17-19'!$L$5:$L$9,Summary!$C3)+sumifs('Feb 24-26'!$M$5:$M$9,'Feb 24-26'!$L$5:$L$9,Summary!$C3)+sumifs('Mar 2-4'!$M$5:$M$9,'Mar 2-4'!$L$5:$L$9,Summary!$C3)+sumifs('Mar 9-11'!$M$5:$M$9,'Mar 9-11'!$L$5:$L$9,Summary!$C3)+sumifs('Mar 16-18'!$M$5:$M$9,'Mar 16-18'!$L$5:$L$9,Summary!$C3)+sumifs('Mar 23-25'!$M$5:$M$9,'Mar 23-25'!$L$5:$L$9,Summary!$C3)+sumifs('Mar 30-32'!$M$5:$M$9,'Mar 30-32'!$L$5:$L$9,Summary!$C3)+sumifs('Apr 6-8'!$M$5:$M$9,'Apr 6-8'!$L$5:$L$9,Summary!$C3)+sumifs('Apr 13-15'!$M$5:$M$9,'Apr 13-15'!$L$5:$L$9,Summary!$C3)+sumifs('Apr 20-22'!$M$5:$M$9,'Apr 20-22'!$L$5:$L$9,Summary!$C3)+sumifs('Apr 27-29'!$M$5:$M$9,'Apr 27-29'!$L$5:$L$9,Summary!$C3)+sumifs('May 4-6'!$M$5:$M$9,'May 4-6'!$L$5:$L$9,Summary!$C3)+sumifs('May 11-13'!$M$5:$M$9,'May 11-13'!$L$5:$L$9,Summary!$C3)+sumifs('May 18-20'!$M$5:$M$9,'May 18-20'!$L$5:$L$9,Summary!$C3)+sumifs('May 25-27'!$M$5:$M$9,'May 25-27'!$L$5:$L$9,Summary!$C3)+sumifs('Jun 1-3'!$M$5:$M$9,'Jun 1-3'!$L$5:$L$9,Summary!$C3)+sumifs('Jun 8-10'!$M$5:$M$9,'Jun 8-10'!$L$5:$L$9,Summary!$C3)+sumifs('Jun 15-17'!$M$5:$M$9,'Jun 15-17'!$L$5:$L$9,Summary!$C3)+sumifs('Jun 22-24'!$M$5:$M$9,'Jun 22-24'!$L$5:$L$9,Summary!$C3)+sumifs('Jun 29-31'!$M$5:$M$9,'Jun 29-31'!$L$5:$L$9,Summary!$C3)+sumifs('Jul 6-8'!$M$5:$M$9,'Jul 6-8'!$L$5:$L$9,Summary!$C3)+sumifs('Jul 13-15'!$M$5:$M$9,'Jul 13-15'!$L$5:$L$9,Summary!$C3)+sumifs('Jul 20-22'!$M$5:$M$9,'Jul 20-22'!$L$5:$L$9,Summary!$C3)+sumifs('Jul 27-29'!$M$5:$M$9,'Jul 27-29'!$L$5:$L$9,Summary!$C3)+sumifs('Aug 3-5'!$M$5:$M$9,'Aug 3-5'!$L$5:$L$9,Summary!$C3)+sumifs('Aug 10-12'!$M$5:$M$9,'Aug 10-12'!$L$5:$L$9,Summary!$C3)+sumifs('Aug 17-19'!$M$5:$M$9,'Aug 17-19'!$L$5:$L$9,Summary!$C3)+sumifs('Aug 24-26'!$M$5:$M$9,'Aug 24-26'!$L$5:$L$9,Summary!$C3)+sumifs('Aug 31-33'!$M$5:$M$9,'Aug 31-33'!$L$5:$L$9,Summary!$C3)+sumifs('Sep 7-9'!$M$5:$M$9,'Sep 7-9'!$L$5:$L$9,Summary!$C3)+sumifs('Sep 14-16'!$M$5:$M$9,'Sep 14-16'!$L$5:$L$9,Summary!$C3)+sumifs('Sep 21-23'!$M$5:$M$9,'Sep 21-23'!$L$5:$L$9,Summary!$C3)+sumifs('Sep 28-30'!$M$5:$M$9,'Sep 28-30'!$L$5:$L$9,Summary!$C3)+sumifs('Oct 5-7'!$M$5:$M$9,'Oct 5-7'!$L$5:$L$9,Summary!$C3)+sumifs('Oct 12-14'!$M$5:$M$9,'Oct 12-14'!$L$5:$L$9,Summary!$C3)+sumifs('Oct 19-21'!$M$5:$M$9,'Oct 19-21'!$L$5:$L$9,Summary!$C3)+sumifs('Oct 26-28'!$M$5:$M$9,'Oct 26-28'!$L$5:$L$9,Summary!$C3)+sumifs('Nov 2-4'!$M$5:$M$9,'Nov 2-4'!$L$5:$L$9,Summary!$C3)+sumifs('Nov 9-11'!$M$5:$M$9,'Nov 9-11'!$L$5:$L$9,Summary!$C3)+sumifs('Nov 16-18'!$M$5:$M$9,'Nov 16-18'!$L$5:$L$9,Summary!$C3)+sumifs('Nov 23-25'!$M$5:$M$9,'Nov 23-25'!$L$5:$L$9,Summary!$C3)+sumifs('Nov 30-32'!$M$5:$M$9,'Nov 30-32'!$L$5:$L$9,Summary!$C3)+sumifs('Dec 7-9'!$M$5:$M$9,'Dec 7-9'!$L$5:$L$9,Summary!$C3)+sumifs('Dec 14-16'!$M$5:$M$9,'Dec 14-16'!$L$5:$L$9,Summary!$C3)+sumifs('Dec 21-23'!$M$5:$M$9,'Dec 21-23'!$L$5:$L$9,Summary!$C3)+sumifs('Dec 28-30'!$M$5:$M$9,'Dec 28-30'!$L$5:$L$9,Summary!$C3)


    What would have been a better way?

  24. #24
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: Longest Formula Ever

    Redesign the workbook so that all of the data is on a single sheet.
    Entia non sunt multiplicanda sine necessitate

  25. #25
    Forum Expert daffodil11's Avatar
    Join Date
    07-11-2013
    Location
    Phoenixville, PA
    MS-Off Ver
    MS Office 2016
    Posts
    4,465

    Re: Longest Formula Ever

    Amen to that. If only they had provided a sample workbook.

    Frownyface.

  26. #26
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Longest Formula Ever

    Just giving it a quick look over...

    You should be able to use a SUMPRODUCT(SUMIFS(INDIRECT formula.

    Or, put a SUMIFS on each sheet in the same cell then do a sum of that cell across all the sheets.

  27. #27
    Forum Guru :) Sixthsense :)'s Avatar
    Join Date
    01-01-2012
    Location
    India>Tamilnadu>Chennai
    MS-Off Ver
    2003 To 2010
    Posts
    12,767

    Re: Longest Formula Ever

    Why not sumif instead of sumifS?

    Or

    Go for VBA solution...


    If your problem is solved, then please mark the thread as SOLVED>>Above your first post>>Thread Tools>>
    Mark your thread as Solved


    If the suggestion helps you, then Click *below to Add Reputation

  28. #28
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Longest Formula Ever

    Quote Originally Posted by :) Sixthsense :) View Post
    Why not sumif instead of sumifS?
    Upon closer inspection...

    Yes, SUMIF.

  29. #29
    Forum Contributor Grimace's Avatar
    Join Date
    05-04-2009
    Location
    Gold Coast, Australia
    MS-Off Ver
    Excel 2013 and Office 365 Plus
    Posts
    380

    Re: Longest Formula Ever

    I copied Cytops formula into a sheet and it gave me the answer .. "You are kidding arent you"
    Handy things to keep in mind:

    Click *, if my suggestion has helped you
    If your problem is solved, then please mark the thread as SOLVED

    Sharing is Caring .... spread the knowledge

  30. #30
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Longest Formula Ever

    This one is quite long but the best alternative is a relatively easy SUMPRODUCT function:

    http://www.excelforum.com/excel-form...-too-long.html

  31. #31
    Registered User
    Join Date
    04-22-2012
    Location
    Syracuse, NY, USA
    MS-Off Ver
    Office 365
    Posts
    61

    Re: Longest Formula Ever

    It's from India, the giveaway is the word rupee about 2/3 of the way down. The Rupee is the currency of India (and Hyrule).
    Reality is stranger than fiction.

  32. #32
    Forum Expert
    Join Date
    09-01-2012
    Location
    Norway
    MS-Off Ver
    Office 365
    Posts
    2,841

    Re: Longest Formula Ever

    =IFERROR(IF(ISTEXT(RIGHT(MID(IF(LEN(IFERROR(INDEX(Data!$C$4:$C$395;SMALL(IF(Data!$A$4:$A$395<>"";ROW($A$4:$A$395)-ROW($A$4)+4);ROWS($A$1:A1)));""))<=4;INDEX(Data!$C$4:$C$395;SMALL(IF(Data!$A$4:$A$395<>"";ROW($A$4:$A$395)-ROW($A$4)+5);ROWS($A$1:A1)));INDEX(Data!$C$4:$C$395;SMALL(IF(Data!$A$4:$A$395<>"";ROW($A$4:$A$395)-ROW($A$4)+4);ROWS($A$1:A1))));FIND("@";SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(IF(LEN(IFERROR(INDEX(Data!$C$4:$C$395;SMALL(IF(Data!$A$4:$A$395<>"";ROW($A$4:$A$395)-ROW($A$4)+4);ROWS($A$1:A1)));""))<=4;INDEX(Data!$C$4:$C$395;SMALL(IF(Data!$A$4:$A$395<>"";ROW($A$4:$A$395)-ROW($A$4)+5);ROWS($A$1:A1)));INDEX(Data!$C$4:$C$395;SMALL(IF(Data!$A$4:$A$395<>"";ROW($A$4:$A$395)-ROW($A$4)+4);ROWS($A$1:A1))));"BILL NO.";"@");"PP NO.";"@");"RECEPT NO.";"@"));13);1));MID(IF(LEN(IFERROR(INDEX(Data!$C$4:$C$395;SMALL(IF(Data!$A$4:$A$395<>"";ROW($A$4:$A$395)-ROW($A$4)+4);ROWS($A$1:A1)));""))<=4;INDEX(Data!$C$4:$C$395;SMALL(IF(Data!$A$4:$A$395<>"";ROW($A$4:$A$395)-ROW($A$4)+5);ROWS($A$1:A1)));INDEX(Data!$C$4:$C$395;SMALL(IF(Data!$A$4:$A$395<>"";ROW($A$4:$A$395)-ROW($A$4)+4);ROWS($A$1:A1))));FIND("@";SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(IF(LEN(IFERROR(INDEX(Data!$C$4:$C$395;SMALL(IF(Data!$A$4:$A$395<>"";ROW($A$4:$A$395)-ROW($A$4)+4);ROWS($A$1:A1)));""))<=4;INDEX(Data!$C$4:$C$395;SMALL(IF(Data!$A$4:$A$395<>"";ROW($A$4:$A$395)-ROW($A$4)+5);ROWS($A$1:A1)));INDEX(Data!$C$4:$C$395;SMALL(IF(Data!$A$4:$A$395<>"";ROW($A$4:$A$395)-ROW($A$4)+4);ROWS($A$1:A1))));"BILL NO.";"@");"PP NO.";"@");"RECEPT NO.";"@"));12);MID(IF(LEN(IFERROR(INDEX(Data!$C$4:$C$395;SMALL(IF(Data!$A$4:$A$395<>"";ROW($A$4:$A$395)-ROW($A$4)+4);ROWS($A$1:A1)));""))<=4;INDEX(Data!$C$4:$C$395;SMALL(IF(Data!$A$4:$A$395<>"";ROW($A$4:$A$395)-ROW($A$4)+5);ROWS($A$1:A1)));INDEX(Data!$C$4:$C$395;SMALL(IF(Data!$A$4:$A$395<>"";ROW($A$4:$A$395)-ROW($A$4)+4);ROWS($A$1:A1))));FIND("@";SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(IF(LEN(IFERROR(INDEX(Data!$C$4:$C$395;SMALL(IF(Data!$A$4:$A$395<>"";ROW($A$4:$A$395)-ROW($A$4)+4);ROWS($A$1:A1)));""))<=4;INDEX(Data!$C$4:$C$395;SMALL(IF(Data!$A$4:$A$395<>"";ROW($A$4:$A$395)-ROW($A$4)+5);ROWS($A$1:A1)));INDEX(Data!$C$4:$C$395;SMALL(IF(Data!$A$4:$A$395<>"";ROW($A$4:$A$395)-ROW($A$4)+4);ROWS($A$1:A1))));"BILL NO.";"@");"PP NO.";"@");"RECEPT NO.";"@"));13));"")

    This one was posted recently. Alt + Enter before every INDEX reveals a pattern. I made named formulas of that and thought I done something smart until I realized the ROWS($A$1:A1). No dollars, no named formulas or? Anyway, I gave up.
    http://www.excelforum.com/excel-form...t-formula.html
    <----- If you were helped by my posts you can say "Thank you" by clicking the star symbol down to the left

    If the problem is solved, finish of the thread by clicking SOLVED under Thread Tools
    I don't wish to leave you with no answer, yet I sometimes miss posts. If you feel I forgot you, remind me with a PM or just bump the thread.

  33. #33
    Forum Expert
    Join Date
    09-01-2012
    Location
    Norway
    MS-Off Ver
    Office 365
    Posts
    2,841

  34. #34
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Longest Formula Ever

    This may be the longest formula I've ever seen.

    http://www.excelforum.com/excel-form...f-nesting.html

  35. #35
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Longest Formula Ever

    Yup, Im sure that must be some sort of record Tony...kudo's to the OP though for creating it

  36. #36
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,093

    Re: Longest Formula Ever

    Maybe this: http://www.excelforum.com/review/102...ml#post3781660

    Formula: copy to clipboard
    Please Login or Register  to view this content.


    Regards, TMS
    Trevor Shuttleworth - Retired Excel/VBA Consultant

    I dream of a better world where chickens can cross the road without having their motives questioned

    'Being unapologetic means never having to say you're sorry' John Cooper Clarke


  37. #37
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Longest Formula Ever

    Are you sure that's the correct link?

  38. #38
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Longest Formula Ever

    I have 1 here that excel says is too long - exceeds 8192 characters...
    http://www.excelforum.com/excel-form...reference.html

    =IF(R20="Wholesale", IF(AND(C20=B134),INDEX(OFFSET('Prices-W'!$B$5:$R$14,(0+(J20-1))*14,0),IF(L20<'Prices-W'!$A$5,0,MATCH(L20,'Prices-W'!$A$5:$A$14))+(COUNTIF('Prices-W'!$A$5:$A$14,L20)=0),IF(K20<'Prices-W'!$B$4,0,MATCH(K20,'Prices-W'!$B$4:$R$4))+(COUNTIF('Prices-W'!$B$4:$R$4,K20)=0)),IF(AND(C20=B135,J20<6),INDEX(OFFSET('Prices-W'!$B$105:$AK$119,(0+(J20-1))*19,0),IF(L20<'Prices-W'!$A$105,0,MATCH(L20,'Prices-W'!$A$105:$A$119))+(COUNTIF('Prices-W'!$A$105:$A$119,L20)=0),IF(K20<'Prices-W'!$B$104,0,MATCH(K20,'Prices-W'!$B$104:$AK$104))+(COUNTIF('Prices-W'!$B$104:$AK$104,K20)=0)),IF(AND(C20=B136,J20<6),INDEX(OFFSET('Prices-W'!$B$202:$AK$216,(0+(J20-1))*19,0),IF(L20<'Prices-W'!$A$202,0,MATCH(L20,'Prices-W'!$A$202:$A$216))+(COUNTIF('Prices-W'!$A$202:$A$216,L20)=0),IF(K20<'Prices-W'!$B$201,0,MATCH(K20,'Prices-W'!$B$201:$AK$201))+(COUNTIF('Prices-W'!$B$201:$AK$201,K20)=0)),IF(AND(C20=B137,J20<7),INDEX(OFFSET('Prices-W'!$B$299:$P$307,(0+(J20-1))*13,0),IF(L20<'Prices-W'!$A$299,0,MATCH(L20,'Prices-W'!$A$299:$A$307))+(COUNTIF('Prices-W'!$A$299:$A$307,L20)=0),IF(K20<'Prices-W'!$B$298,0,MATCH(K20,'Prices-W'!$B$298:$P$298))+(COUNTIF('Prices-W'!$B$298:$P$298,K20)=0)),IF(AND(OR(C20=B138,C20=B139),J20<7),1.3*(INDEX(OFFSET('Prices-W'!$B$299:$P$307,(0+(J20-1))*13,0),IF(L20<'Prices-W'!$A$299,0,MATCH(L20,'Prices-W'!$A$299:$A$307))+(COUNTIF('Prices-W'!$A$299:$A$307,L20)=0),IF(K20<'Prices-W'!$B$298,0,MATCH(K20,'Prices-W'!$B$298:$P$298))+(COUNTIF('Prices-W'!$B$298:$P$298,K20)=0))),IF(AND(C20=B141),INDEX(OFFSET('Prices-W'!$B$379:$P$391,(0+(J20-1))*17,0),IF(L20<'Prices-W'!$A$379,0,MATCH(L20,'Prices-W'!$A$379:$A$391))+(COUNTIF('Prices-W'!$A$379:$A$391,L20)=0),IF(K20<'Prices-W'!$B$378,0,MATCH(K20,'Prices-W'!$B$378:$P$378))+(COUNTIF('Prices-W'!$B$378:$P$378,K20)=0)),IF(AND(C20=B140),1.6*(INDEX(OFFSET('Prices-W'!$B$379:$P$391,(0+(J20-1))*17,0),IF(L20<'Prices-W'!$A$379,0,MATCH(L20,'Prices-W'!$A$379:$A$391))+(COUNTIF('Prices-W'!$A$379:$A$391,L20)=0),IF(K20<'Prices-W'!$B$378,0,MATCH(K20,'Prices-W'!$B$378:$P$378))+(COUNTIF('Prices-W'!$B$378:$P$378,K20)=0))),IF(C20=B143,IF(L20<'Prices-W'!$A$500,0,MATCH(L20,'Prices-W'!$A$500:$A$526))+(COUNTIF('Prices-W'!$A$500:$A$526,L20)=0),IF(K20<'Prices-W'!$B$499,0,MATCH(K20,'Prices-W'!$B$499:$W$499))+(COUNTIF('Prices-W'!$B$499:$W$499,K20)=0),IF(C20=B144,IF(L20<'Prices-W'!$A$532,0,MATCH(L20,'Prices-W'!$A$532:$A$558))+(COUNTIF('Prices-W'!$A$532:$A$558,L20)=0),IF(K20<'Prices-W'!$B$531,0,MATCH(K20,'Prices-W'!$B$531:$W$531))+(COUNTIF('Prices-W'!$B$531:$W$531,K20)=0),IF(C20=B145,IF(L20<'Prices-W'!$A$564,0,MATCH(L20,'Prices-W'!$A$564:$A$590))+(COUNTIF('Prices-W'!$A$564:$A$590,L20)=0),IF(K20<'Prices-W'!$B$563,0,MATCH(K20,'Prices-W'!$B$563:$Z$563))+(COUNTIF('Prices-W'!$B$563:$Z$563,K20)=0),IF(C20=B146,IF(L20<'Prices-W'!$A$596,0,MATCH(L20,'Prices-W'!$A$596:$A$620))+(COUNTIF('Prices-W'!$A$596:$A$620,L20)=0),IF(K20<'Prices-W'!$B$595,0,MATCH(K20,'Prices-W'!$B$595:$U$595))+(COUNTIF('Prices-W'!$B$595:$U$595,K20)=0),IF(C20=B147,IF(L20<'Prices-W'!$A$626,0,MATCH(L20,'Prices-W'!$A$626:$A$652))+(COUNTIF('Prices-W'!$A$626:$A$652,L20)=0),IF(K20<'Prices-W'!$B$625,0,MATCH(K20,'Prices-W'!$B$625:$W$625))+(COUNTIF('Prices-W'!$B$625:$W$625,K20)=0),IF(C20=B148,IF(L20<'Prices-W'!$A$658,0,MATCH(L20,'Prices-W'!$A$658:$A$684))+(COUNTIF('Prices-W'!$A$658:$A$684,L20)=0),IF(K20<'Prices-W'!$B$657,0,MATCH(K20,'Prices-W'!$B$657:$W$657))+(COUNTIF('Prices-W'!$B$657:$W$657,K20)=0),IF(C20=B142,(((K20*L20)/1000000)*290)+(G20*'Prices-W'!$D$688)+(H20*'Prices-W'!$C$689),IF(C20=B149,IF(L20<'Prices-W'!$A$695,0,MATCH(L20,'Prices-W'!$A$695:$A$696))+(COUNTIF('Prices-W'!$A$695:$A$696,L20)=0),IF(K20<'Prices-W'!$B$694,0,MATCH(K20,'Prices-W'!$B$694:$O$694))+(COUNTIF('Prices-W'!$B$694:$O$694,K20)=0),IF(C20=B150,IF(L20<'Prices-W'!$A$702,0,MATCH(L20,'Prices-W'!$A$702:$A$703))+(COUNTIF('Prices-W'!$A$702:$A$703,L20)=0),IF(K20<'Prices-W'!$B$701,0,MATCH(K20,'Prices-W'!$B$701:$O$701))+(COUNTIF('Prices-W'!$B$701:$O$701,K20)=0),IF(C20=B151,IF(L20<'Prices-W'!$A$709,0,MATCH(L20,'Prices-W'!$A$709:$A$710))+(COUNTIF('Prices-W'!$A$709:$A$710,L20)=0),IF(K20<'Prices-W'!$B$708,0,MATCH(K20,'Prices-W'!$B$708:$O$708))+(COUNTIF('Prices-W'!$B$708:$O$708,K20)=0)," ")))))))))))))))))),IF(R20="Retail", IF(AND(C20=B134),INDEX(OFFSET('Prices-R'!$B$5:$R$14,(0+(J20-1))*14,0),IF(L20<'Prices-R'!$A$5,0,MATCH(L20,'Prices-R'!$A$5:$A$14))+(COUNTIF('Prices-R'!$A$5:$A$14,L20)=0),IF(K20<'Prices-R'!$B$4,0,MATCH(K20,'Prices-R'!$B$4:$R$4))+(COUNTIF('Prices-R'!$B$4:$R$4,K20)=0)),IF(AND(C20=B135,J20<6),INDEX(OFFSET('Prices-R'!$B$105:$AK$119,(0+(J20-1))*19,0),IF(L20<'Prices-R'!$A$105,0,MATCH(L20,'Prices-R'!$A$105:$A$119))+(COUNTIF('Prices-R'!$A$105:$A$119,L20)=0),IF(K20<'Prices-R'!$B$104,0,MATCH(K20,'Prices-R'!$B$104:$AK$104))+(COUNTIF('Prices-R'!$B$104:$AK$104,K20)=0)),IF(AND(C20=B136,J20<6),INDEX(OFFSET('Prices-R'!$B$202:$AK$216,(0+(J20-1))*19,0),IF(L20<'Prices-R'!$A$202,0,MATCH(L20,'Prices-R'!$A$202:$A$216))+(COUNTIF('Prices-R'!$A$202:$A$216,L20)=0),IF(K20<'Prices-R'!$B$201,0,MATCH(K20,'Prices-R'!$B$201:$AK$201))+(COUNTIF('Prices-R'!$B$201:$AK$201,K20)=0)),IF(AND(C20=B137,J20<7),INDEX(OFFSET('Prices-R'!$B$299:$P$307,(0+(J20-1))*13,0),IF(L20<'Prices-R'!$A$299,0,MATCH(L20,'Prices-R'!$A$299:$A$307))+(COUNTIF('Prices-R'!$A$299:$A$307,L20)=0),IF(K20<'Prices-R'!$B$298,0,MATCH(K20,'Prices-R'!$B$298:$P$298))+(COUNTIF('Prices-R'!$B$298:$P$298,K20)=0)),IF(AND(OR(C20=B138,C20=B139),J20<7),1.3*(INDEX(OFFSET('Prices-R'!$B$299:$P$307,(0+(J20-1))*13,0),IF(L20<'Prices-R'!$A$299,0,MATCH(L20,'Prices-R'!$A$299:$A$307))+(COUNTIF('Prices-R'!$A$299:$A$307,L20)=0),IF(K20<'Prices-R'!$B$298,0,MATCH(K20,'Prices-R'!$B$298:$P$298))+(COUNTIF('Prices-R'!$B$298:$P$298,K20)=0))),IF(AND(C20=B141),INDEX(OFFSET('Prices-R'!$B$379:$P$391,(0+(J20-1))*17,0),IF(L20<'Prices-R'!$A$379,0,MATCH(L20,'Prices-R'!$A$379:$A$391))+(COUNTIF('Prices-R'!$A$379:$A$391,L20)=0),IF(K20<'Prices-R'!$B$378,0,MATCH(K20,'Prices-R'!$B$378:$P$378))+(COUNTIF('Prices-R'!$B$378:$P$378,K20)=0)),IF(AND(C20=B140),1.6*(INDEX(OFFSET('Prices-R'!$B$379:$P$391,(0+(J20-1))*17,0),IF(L20<'Prices-R'!$A$379,0,MATCH(L20,'Prices-R'!$A$379:$A$391))+(COUNTIF('Prices-R'!$A$379:$A$391,L20)=0),IF(K20<'Prices-R'!$B$378,0,MATCH(K20,'Prices-R'!$B$378:$P$378))+(COUNTIF('Prices-R'!$B$378:$P$378,K20)=0))),IF(C20=B143,IF(L20<'Prices-R'!$A$500,0,MATCH(L20,'Prices-R'!$A$500:$A$526))+(COUNTIF('Prices-R'!$A$500:$A$526,L20)=0),IF(K20<'Prices-R'!$B$499,0,MATCH(K20,'Prices-R'!$B$499:$W$499))+(COUNTIF('Prices-R'!$B$499:$W$499,K20)=0),IF(C20=B144,IF(L20<'Prices-R'!$A$532,0,MATCH(L20,'Prices-R'!$A$532:$A$558))+(COUNTIF('Prices-R'!$A$532:$A$558,L20)=0),IF(K20<'Prices-R'!$B$531,0,MATCH(K20,'Prices-R'!$B$531:$W$531))+(COUNTIF('Prices-R'!$B$531:$W$531,K20)=0),IF(C20=B145,IF(L20<'Prices-R'!$A$564,0,MATCH(L20,'Prices-R'!$A$564:$A$590))+(COUNTIF('Prices-R'!$A$564:$A$590,L20)=0),IF(K20<'Prices-R'!$B$563,0,MATCH(K20,'Prices-R'!$B$563:$Z$563))+(COUNTIF('Prices-R'!$B$563:$Z$563,K20)=0),IF(C20=B146,IF(L20<'Prices-R'!$A$596,0,MATCH(L20,'Prices-R'!$A$596:$A$620))+(COUNTIF('Prices-R'!$A$596:$A$620,L20)=0),IF(K20<'Prices-R'!$B$595,0,MATCH(K20,'Prices-R'!$B$595:$U$595))+(COUNTIF('Prices-R'!$B$595:$U$595,K20)=0),IF(C20=B147,IF(L20<'Prices-R'!$A$626,0,MATCH(L20,'Prices-R'!$A$626:$A$652))+(COUNTIF('Prices-R'!$A$626:$A$652,L20)=0),IF(K20<'Prices-R'!$B$625,0,MATCH(K20,'Prices-R'!$B$625:$W$625))+(COUNTIF('Prices-R'!$B$625:$W$625,K20)=0),IF(C20=B148,IF(L20<'Prices-R'!$A$658,0,MATCH(L20,'Prices-R'!$A$658:$A$684))+(COUNTIF('Prices-R'!$A$658:$A$684,L20)=0),IF(K20<'Prices-R'!$B$657,0,MATCH(K20,'Prices-R'!$B$657:$W$657))+(COUNTIF('Prices-R'!$B$657:$W$657,K20)=0),IF(C20=B142,(((K20*L20)/1000000)*290)+(G20*'Prices-R'!$D$688)+(H20*'Prices-R'!$C$689),IF(C20=B149,IF(L20<'Prices-R'!$A$695,0,MATCH(L20,'Prices-R'!$A$695:$A$696))+(COUNTIF('Prices-R'!$A$695:$A$696,L20)=0),IF(K20<'Prices-R'!$B$694,0,MATCH(K20,'Prices-R'!$B$694:$O$694))+(COUNTIF('Prices-R'!$B$694:$O$694,K20)=0),IF(C20=B150,IF(L20<'Prices-R'!$A$702,0,MATCH(L20,'Prices-R'!$A$702:$A$703))+(COUNTIF('Prices-R'!$A$702:$A$703,L20)=0),IF(K20<'Prices-R'!$B$701,0,MATCH(K20,'Prices-R'!$B$701:$O$701))+(COUNTIF('Prices-R'!$B$701:$O$701,K20)=0),IF(C20=B151,IF(L20<'Prices-R'!$A$709,0,MATCH(L20,'Prices-R'!$A$709:$A$710))+(COUNTIF('Prices-R'!$A$709:$A$710,L20)=0),IF(K20<'Prices-R'!$B$708,0,MATCH(K20,'Prices-R'!$B$708:$O$708))+(COUNTIF('Prices-R'!$B$708:$O$708,K20)=0)," "))))))))))))))))),"N/A"))

  39. #39
    Forum Contributor
    Join Date
    04-02-2012
    Location
    Ohio
    MS-Off Ver
    Excel 2010
    Posts
    452

    Re: Longest Formula Ever

    =IF(VLOOKUP($A74,'ADMIN HIDDEN'!$B$2:$D$68,2,FALSE)=1,SUM(INDEX((INDEX(Ex_Data,0,MATCH(VLOOKUP($A74,Time_Sheet_Lookup_Table,3,FALSE),Ex_Data[#Headers],FALSE)))*(Ex_Data[[Macrocycle]:[Macrocycle]]=C$65)*(IF(LEN(C$66),(Ex_Data[[Mesocycle]:[Mesocycle]]=C$66),1)*(IF(LEN(C$67),(Ex_Data[[Week]:[Week]]=C$67),1)*(IF(LEN(C$68),(Ex_Data[[Day]:[Day]]=C$68),1)))),)),IF(VLOOKUP($A74,'ADMIN HIDDEN'!$B$2:$D$68,2,FALSE)=11,SUM(INDEX((INDEX(Day_Data,0,MATCH(VLOOKUP($A74,Time_Sheet_Lookup_Table,3,FALSE),Day_Data[#Headers],FALSE)))*(Day_Data[[Macrocycle]:[Macrocycle]]=C$65)*(IF(LEN(C$66),(Day_Data[[Mesocycle]:[Mesocycle]]=C$66),1)*(IF(LEN(C$67),(Day_Data[[Week]:[Week]]=C$67),1)*(IF(LEN(C$68),(Day_Data[[Day]:[Day]]=C$68),1)))),)),IF(VLOOKUP($A74,'ADMIN HIDDEN'!$B$2:$D$68,2,FALSE)=2,MAX(INDEX((INDEX(Ex_Data,0,MATCH(VLOOKUP($A74,Time_Sheet_Lookup_Table,3,FALSE),Ex_Data[#Headers],FALSE)))*(Ex_Data[[Macrocycle]:[Macrocycle]]=C$65)*(IF(LEN(C$66),(Ex_Data[[Mesocycle]:[Mesocycle]]=C$66),1)*(IF(LEN(C$67),(Ex_Data[[Week]:[Week]]=C$67),1)*(IF(LEN(C$68),(Ex_Data[[Day]:[Day]]=C$68),1)))),)),IF(VLOOKUP($A74,'ADMIN HIDDEN'!$B$2:$D$68,2,FALSE)=3,MIN(INDEX((INDEX(Ex_Data,0,MATCH(VLOOKUP($A74,Time_Sheet_Lookup_Table,3,FALSE),Ex_Data[#Headers],FALSE)))*(Ex_Data[[Macrocycle]:[Macrocycle]]=C$65)*(IF(LEN(C$66),(Ex_Data[[Mesocycle]:[Mesocycle]]=C$66),1)*(IF(LEN(C$67),(Ex_Data[[Week]:[Week]]=C$67),1)*(IF(LEN(C$68),(Ex_Data[[Day]:[Day]]=C$68),1)))),)),IF(VLOOKUP($A74,'ADMIN HIDDEN'!$B$2:$D$68,2,FALSE)=4,SUM(IF(C$65="",1,Ex_Data[[Macrocycle]:[Macrocycle]]=C$65)*IF(C$66="",1,Ex_Data[[Mesocycle]:[Mesocycle]]=C$66)*IF(C$67="",1,Ex_Data[[Week]:[Week]]=C$67)*IF(C$68="",1,Ex_Data[[Day]:[Day]]=C$68)),IF(VLOOKUP($A74,'ADMIN HIDDEN'!$B$2:$D$68,2,FALSE)=5,MROUND(SUMPRODUCT(Protocol_Rep,C_Col_Protocol_Rep)/SUM(C_Col_Protocol_Rep),0.1),IF(VLOOKUP($A74,'ADMIN HIDDEN'!$B$2:$D$68,2,FALSE)=51,MROUND(AVERAGE(INDEX((INDEX(Day_Data,0,MATCH(VLOOKUP($A74,Time_Sheet_Lookup_Table,3,FALSE),Day_Data[#Headers],FALSE)))*(Day_Data[[Macrocycle]:[Macrocycle]]=C$65)*(IF(LEN(C$66),(Day_Data[[Mesocycle]:[Mesocycle]]=C$66),1)*(IF(LEN(C$67),(Day_Data[[Week]:[Week]]=C$67),1)*(IF(LEN(C$68),(Day_Data[[Day]:[Day]]=C$68),1)))),)),0.1),IF(VLOOKUP($A74,'ADMIN HIDDEN'!$B$2:$D$68,2,FALSE)=511,MROUND(SUMPRODUCT(Scale_1_To_5,C_Col_Session_RPE)/SUM(C_Col_Session_RPE),0.1),IF(VLOOKUP($A74,'ADMIN HIDDEN'!$B$2:$D$68,2,FALSE)=512,MROUND(SUMPRODUCT(Scale_1_To_5,C_Col_Readiness_Rating)/SUM(C_Col_Readiness_Rating),0.1),IF(VLOOKUP($A74,'ADMIN HIDDEN'!$B$2:$D$68,2,FALSE)=513,MROUND(SUMPRODUCT(Scale_1_To_5,C_Col_Quality_of_Session)/SUM(C_Col_Quality_of_Session),0.1),IF(VLOOKUP($A74,'ADMIN HIDDEN'!$B$2:$D$68,2,FALSE)=52,MROUND(SUMPRODUCT(Protocol_RI,C_Col_Protocol_RI)/SUM(C_Col_Protocol_RI),0.1),IF(VLOOKUP($A74,'ADMIN HIDDEN'!$B$2:$D$68,2,FALSE)=53,MROUND(SUMPRODUCT(Protocol_RPE,C_Col_Protocol_RPE)/SUM(C_Col_Protocol_RPE),0.1),IF(VLOOKUP($A74,'ADMIN HIDDEN'!$B$2:$D$68,2,FALSE)=6,MROUND(SUMPRODUCT(RT_Values,C_Col_Avg_RT)/SUM(C_Col_Avg_RT),0.1),IF(VLOOKUP($A74,'ADMIN HIDDEN'!$B$2:$D$68,2,FALSE)=7,MROUND(SUMPRODUCT(Intensity_Values,C_Col_Avg_Int)/SUM(C_Col_Avg_Int),0.01),IF(VLOOKUP($A74,'ADMIN HIDDEN'!$B$2:$D$68,2,FALSE)=8,MROUND(SUMPRODUCT(RPE_Values,C_Col_Avg_RPE)/SUM(C_Col_Avg_RPE),0.1),IF(VLOOKUP($A74,'ADMIN HIDDEN'!$B$2:$D$68,2,FALSE)=9,MROUND(SUM(INDEX((INDEX(Ex_Data,0,27))*(Ex_Data[[Macrocycle]:[Macrocycle]]=C$65)*(IF(LEN(C$66),(Ex_Data[[Mesocycle]:[Mesocycle]]=C$66),1)*(IF(LEN(C$67),(Ex_Data[[Week]:[Week]]=C$67),1)*(IF(LEN(C$68),(Ex_Data[[Day]:[Day]]=C$68),1)))),))*SUMPRODUCT(Intensity_Values,C_Col_Avg_Int)/SUM(C_Col_Avg_Int),1),IF(VLOOKUP($A74,'ADMIN HIDDEN'!$B$2:$D$68,2,FALSE)=10,SUMPRODUCT(((Ex_Data[[Macrocycle]:[Macrocycle]]=C$65)+(C$65=""))*((Ex_Data[[Mesocycle]:[Mesocycle]]=C$66)+(C$66=""))*((Ex_Data[[Week]:[Week]]=C$67)+(C$67=""))*((Ex_Data[[Day]:[Day]]=C$68)+(C$68=""))*ISNUMBER(SEARCH(VLOOKUP($A74,PR_Lookup_Table,3,FALSE),IF(OR($A74="All Time PRs, est.",$A74="All Time PRs, missed",$A74="All Time PRs, new"),Ex_Data[[All Time PR]:[All Time PR]],Ex_Data[[Protocol PR]:[Protocol PR]])))))))))))))))))))))

    4086 characters not counting the curly brackets. Basically it looks up a row label and returns the data that corresponds with the appropriate time unit column labels.

  40. #40
    Valued Forum Contributor
    Join Date
    01-19-2012
    Location
    Barrington, IL
    MS-Off Ver
    Excel 2007/2010
    Posts
    1,211

    Re: Longest Formula Ever

    My personal longest to date, only 3060, and I never got a chance to use it. I lost the thread I was responding to

    I made it months ago and have no idea what it does anymore :D

    =IFERROR(SUM(CHOOSE(MATCH(N$14,$C$1:$N$1,0),,CHOOSE(ROUNDDOWN(VLOOKUP($A15,'Tab2'!$A$5:$M$7,MATCH(N$14,'Tab2'!$B$4:$M$4,0)+1,FALSE),0)
    ,SUM('Tab1'!$B5)),CHOOSE(ROUNDDOWN(VLOOKUP($A15,'Tab2'!$A$5:$M$7,MATCH(N$14,'Tab2'!$B$4:$M$4,0)+1,FALSE),0),SUM('Tab1'!$C5),SUM('Tab1'!
    $B5:$C5)),CHOOSE(ROUNDDOWN(VLOOKUP($A15,'Tab2'!$A$5:$M$7,MATCH(N$14,'Tab2'!$B$4:$M$4,0)+1,FALSE),0),SUM('Tab1'!$D5),SUM('Tab1'!$C5:$D5)
    ,SUM('Tab1'!$B5:$D5)),CHOOSE(ROUNDDOWN(VLOOKUP($A15,'Tab2'!$A$5:$M$7,MATCH(N$14,'Tab2'!$B$4:$M$4,0)+1,FALSE),0),SUM('Tab1'!$E5),SUM(
    'Tab1'!$D5:$E5),SUM('Tab1'!$C5:$E5),SUM('Tab1'!$B5:$E5)),CHOOSE(ROUNDDOWN(VLOOKUP($A15,'Tab2'!$A$5:$M$7,MATCH(N$14,'Tab2'!$B$4:$M$4,0)
    +1,FALSE),0),SUM('Tab1'!$F5),SUM('Tab1'!$E5:$F5),SUM('Tab1'!$D5:$F5),SUM('Tab1'!$C5:$F5),SUM('Tab1'!$B5:$F5)),CHOOSE(ROUNDDOWN(VLOOKUP
    ($A15,'Tab2'!$A$5:$M$7,MATCH(N$14,'Tab2'!$B$4:$M$4,0)+1,FALSE),0),SUM('Tab1'!$G5),SUM('Tab1'!$F5:$G5),SUM('Tab1'!$E5:$G5),SUM(
    'Tab1'!$D5:$G5),SUM('Tab1'!$C5:$G5),SUM('Tab1'!$B5:$G5)),CHOOSE(ROUNDDOWN(VLOOKUP($A15,'Tab2'!$A$5:$M$7,MATCH(N$14,'Tab2'!$B$4:$M$4,0)
    +1,FALSE),0),SUM('Tab1'!$H5),SUM('Tab1'!$G5:$H5),SUM('Tab1'!$F5:$H5),SUM('Tab1'!$E5:$H5),SUM('Tab1'!$D5:$H5),SUM('Tab1'!$C5:$H5),SUM
    ('Tab1'!$B5:$H5)),CHOOSE(ROUNDDOWN(VLOOKUP($A15,'Tab2'!$A$5:$M$7,MATCH(N$14,'Tab2'!$B$4:$M$4,0)+1,FALSE),0),SUM('Tab1'!$I5),SUM(
    'Tab1'!$H5:$I5),SUM('Tab1'!$G5:$I5),SUM('Tab1'!$F5:$I5),SUM('Tab1'!$E5:$I5),SUM('Tab1'!$D5:$I5),SUM('Tab1'!$C5:$I5),SUM('Tab1'!$B5:$I5)
    )*CHOOSE(ROUNDDOWN(VLOOKUP($A15,'Tab2'!$A$5:$M$7,MATCH(N$14,'Tab2'!$B$4:$M$4,0)+1,FALSE),0),SUM('Tab1'!$J5),SUM('Tab1'!$I5:$J5),SUM
    ('Tab1'!$H5:$J5),SUM('Tab1'!$G5:$J5),SUM('Tab1'!$F5:$J5),SUM('Tab1'!$E5:$J5),SUM('Tab1'!$D5:$J5),SUM('Tab1'!$C5:$J5),SUM('Tab1'!$B5:$J5)
    ),CHOOSE(ROUNDDOWN(VLOOKUP($A15,'Tab2'!$A$5:$M$7,MATCH(N$14,'Tab2'!$B$4:$M$4,0)+1,FALSE),0),SUM('Tab1'!$K5),SUM('Tab1'!$J5:$K5),SUM
    ('Tab1'!$J5:$K5),SUM('Tab1'!$I5:$K5),SUM('Tab1'!$H5:$K5),SUM('Tab1'!$G5:$K5),SUM('Tab1'!$F5:$K5),SUM('Tab1'!$E5:$K5),SUM('Tab1'!$D5:$K5)
    ,SUM('Tab1'!$C5:$K5)),CHOOSE(ROUNDDOWN(VLOOKUP($A15,'Tab2'!$A$5:$M$7,MATCH(N$14,'Tab2'!$B$4:$M$4,0)+1,FALSE),0),SUM('Tab1'!$L5),SUM
    ('Tab1'!$K5:$L5),SUM('Tab1'!$J5:$L5),SUM('Tab1'!$I5:$L5),SUM('Tab1'!$H5:$L5),SUM('Tab1'!$G5:$L5),SUM('Tab1'!$F5:$L5),SUM('Tab1'!$E5:$L5)
    ,SUM('Tab1'!$D5:$L5),SUM('Tab1'!$C5:$L5),SUM('Tab1'!$B5:$L5)),CHOOSE(ROUNDDOWN(VLOOKUP($A15,'Tab2'!$A$5:$M$7,MATCH(N$14,'Tab2'!$B$4:$M$4,0)
    +1,FALSE),0),SUM('Tab1'!$M5),SUM('Tab1'!$L5:$M5),SUM('Tab1'!$K5:$M5),SUM('Tab1'!$J5:$M5),SUM('Tab1'!$I5:$M5),SUM('Tab1'!$H5:$M5),SUM
    ('Tab1'!$G5:$M5),SUM('Tab1'!$F5:$M5),SUM('Tab1'!$E5:$M5),SUM('Tab1'!$D5:$M5),SUM('Tab1'!$C5:$M5),SUM('Tab1'!$B5:$M5))),CHOOSE(ROUNDDOWN
    (ROUNDDOWN(VLOOKUP($A15,'Tab2'!$A$5:$M$7,MATCH(N$14,'Tab2'!$B$4:$M$4,0)+1,FALSE),0),0)+COLUMNS(N:$N),'Tab1'!$M5,'Tab1'!$L5,'Tab1'!$K5,
    'Tab1'!$J5,'Tab1'!$I5,'Tab1'!$H5,'Tab1'!$G5,'Tab1'!$F5,'Tab1'!$E5,'Tab1'!$D5,'Tab1'!$C5,'Tab1'!$B5)*(VLOOKUP($A15,'Tab2'!$A$5:$M$7,MATCH(N$14,'Tab2'!$B$4:$M$4,0)+1,FALSE)-ROUNDDOWN(VLOOKUP($A15,'Tab2'!$A$5:$M$7,MATCH(N$14,'Tab2'!$B$4:$M$4,0)+1,FALSE),0))),"0")
    You should hit F5, because chances are I've edited this post at least 5 times.
    Example of Array Formulas
    Quote Originally Posted by Jacc View Post
    Sorry, your description makes no sense. I just made some formula that looks interesting cause I had nothing else to do.
    Click the * below on any post that helped you.

  41. #41
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Longest Formula Ever

    This one might take the top prize...

    https://www.excelforum.com/showthread.php?p=4320170

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Longest active member
    By pike in forum The Water Cooler
    Replies: 2
    Last Post: 11-10-2011, 03:44 AM
  2. Replies: 4
    Last Post: 05-19-2011, 08:37 AM
  3. Formula for avg skip, max skip, longest out
    By Jordans121 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 12-12-2010, 04:56 PM
  4. Find the longest run of 0's in a column
    By Ben_z_ in forum Excel General
    Replies: 7
    Last Post: 11-10-2008, 11:59 PM
  5. Longest Time
    By Brento in forum Excel General
    Replies: 1
    Last Post: 10-03-2006, 10:00 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1