Solved

Super Long Formula

Posted on 2013-02-06
25
170 Views
Last Modified: 2013-02-11
Hello Experts,

I have been working on a worksheet, which has seen many, many revisions.  When I first started on this project, I had many "helper" cells.  But as I learned more and more about Excel, I knew I could get rid of the helper cells - and have my answer be returned in just one cell.

Being that my worksheet is doing 100% of it's calculations in only one cell, my concern is the length of the formula.

Now I know it's all being forced into one cell, so of course my formula is going to be a monster - but are there any glaring issues with having a formula which is > 1000 characters long?

I would love to hear your opinion....

Thank you in advance!

~ Geekamo
0
Comment
Question by:Geekamo
  • 11
  • 7
  • 5
  • +2
25 Comments
 
LVL 26

Accepted Solution

by:
redmondb earned 250 total points
ID: 38858953
Geekamo,

You're living dangerously...
(1) If the formula has to be changed or simply checked then it will be a nightmare for any one else, and in a very short space of time, yourself.
(2) There are limits to the number of characters in a formula (e.g. 2007's is 1024). What version are you on?

Cells are cheap - put back the help cells. Hide the columns or put the cells on another sheet, if you don't want to see them.

Regards,
brian.
0
 
LVL 33

Expert Comment

by:Norie
ID: 38858958
Geeakamo

Can you post the formula?

By the way, why do you want to lose the helper columns?

They can be quite handy, for example when trying to deal with a problem.

Also, you can't be running out of space.
0
 
LVL 1

Author Comment

by:Geekamo
ID: 38859049
@ Brian,

(1) Well the good news, is that the worst is over.  So updating it going forward won't be needed. Throughout writing the formula I kept testing every step of the way, and once it was finally finished I re-tested everything again - and it all appears to be working correctly.

(2)  I am using 2013, and I think that supports way more than 1024.  As for my co-workers I think most are running 2010.  If there are 2007 users, then I should still be good - cause the formula is 1005 characters.

@ imnorie,

Here is the formula,...

=IF(Object="Square / Rectangle","Bag Size: "&IF(BagType="Gusseted",Width+1&"""W x "&Depth+1&"""D x ",Width+Depth+2&"""W x ")&IF(Objective="Liner with Tie Off",Depth+Height+IF(Default="",6,Default),IF(Objective="Liner with Overhang",(Depth/2)+Height+IF(Default="",8,Default),IF(Objective="Full Cover",(Depth/2)+Height,IF(Objective="Cap with Overhang",(Depth/2)+IF(Default="",10,Default),))))&"""L ","Bag Size: "&IF(BagType="Gusseted",ROUNDUP(IF(Width="",Depth/PI(),Width),0)&"""W x "&ROUNDUP(IF(Width="",(Depth/2)-(Depth/PI()),(Width*PI())/2)-Width,0)+1&"""D x ",ROUNDUP(IF(Width="",Depth/2,Width*PI()/2),0)+1&"""W x ")&ROUNDUP(IF(Objective="Liner with Tie Off",IF(Width="",Depth/PI(),Width)+Height+IF(Default="",6,Default),IF(Objective="Liner with Overhang",IF(Width="",(Depth/PI())/2,Width/2)+Height+IF(Default="",8,Default),IF(Objective="Full Cover",IF(Width="",(Depth/PI())/2,Width/2)+Height,IF(Objective="Cap with Overhang",IF(Width="",(Depth/PI())/2,Width/2)+IF(Default="",10,Default),)))),0)&"""L ")

Open in new window


If there is any good that came out of this beast of a formula, is it forced me to learn alot more about Excel.  Only a couple weeks ago, I wouldn't of had a chance at writing a formula like that.  (My Excel skill level is very much a beginner)

If either of you notice a problem, or maybe something redundant in my formula, please let me know.  Idk, be kind...  I'm sure all the experts will be laughing at my formula.  Such a newb here.  :)

~ Geekamo
0
 
LVL 24

Expert Comment

by:Steve
ID: 38859055
Geekamo, please post the formula in an example workbook...

We love to have a game of "shortest formula" (although Barry is hard to beat)
0
 
LVL 1

Author Comment

by:Geekamo
ID: 38859068
0
 
LVL 26

Expert Comment

by:redmondb
ID: 38859072
Geekamo,

Well the good news, is that the worst is over.  So updating it going forward won't be needed.
Unless the file won't be used again then that may be optimistic thinking! Almost certainly, this formula will make your future self very cross with your current one!

(2010 has gone to 8,192 characters.)

Regards,
Brian.
0
 
LVL 1

Author Comment

by:Geekamo
ID: 38859077
@ All,

A little less then half of the formula, specifically points to "Square / Rectangle" being selected.  It wasn't until I started on the Cylinder part of my formula, inwhich I am giving the user the option of including either the Diameter or Circumference.  Not the mention allowing the user to adjust the default values.  That's when the formula really started to grow.

Idk, at the very least - this was a good learning exercise - that's for sure.
0
 
LVL 1

Author Comment

by:Geekamo
ID: 38859086
@ Brian,

lol - I'm hoping I won't have to deal with it again.  I mean if down the road I need to adjust for other things, then I know I'll be starting this formula again from scratch.  But, for what I need it to do - it appears to be doing exactly what I want.
0
 
LVL 26

Expert Comment

by:redmondb
ID: 38859113
Geekamo,

But, for what I need it to do - it appears to be doing exactly what I want.
It's always hard to argue with success! The work is done now, so by all means keep this formula - but if you still have a version of the file with the helper cells, then include the relevant sheet (hidden) in the live file.

Regards,
Brian.
0
 
LVL 24

Expert Comment

by:Steve
ID: 38859116
Have you any objections to a UDF?

A VBA function would act like an in-built function and be very easy to read / use / update.

I am happy to convert you existing formula to a UDF to get you started.
(but do not want to take the time if this does not suit you)

ATB
Steve.
0
 
LVL 1

Author Comment

by:Geekamo
ID: 38859127
@ Brian,

Ya, I know I could technically hide any helper cells - but idk, I guess I forced myself to see if I was even capiable of writing something more then a basic SUM formula.
0
 
LVL 1

Author Comment

by:Geekamo
ID: 38859136
@ Steve,

Hmm, Idk what a UDF is.  :)

As for macro code, I've used it before.  I basically know how to copy and paste the code in, and I can do some VERY VERY VERY VERY basic editing of the code to reuse for other projects I work on.

But I would hate to have you invest any time into it - when the chances are very good, it'll be like reading hieroglyphics.  But then again, you could probably make the same argument with my forula.  :)

~ Geekamo
0
Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

 
LVL 26

Expert Comment

by:redmondb
ID: 38859156
Geekamo,

Ya, I know I could technically hide any helper cells
Yes, but not the point I was making. I was suggesting that you include an old Helper version of the sheet as a backup. So the live sheet would still use your Super Formula, but you'd have the comfort of knowing that you would still have the Helper version if necessary.

Regards,
Brian.
0
 
LVL 1

Author Comment

by:Geekamo
ID: 38859170
@ Brian,

Oh ok, ya I'm not so much as worried about that.  I mean I do have all the previous versions, so I'll never loose anything before this. Just with this project alone, I have 7 excel workbooks.  All variations of the one previous to it.
0
 
LVL 26

Expert Comment

by:redmondb
ID: 38859197
Geekamo,

One hidden sheet v. 6 files?!

Regards,
Brian.
0
 
LVL 1

Author Comment

by:Geekamo
ID: 38859202
@ Brian,

Hmm, you make a good point.  :)

~ Geekamo
0
 
LVL 26

Expert Comment

by:redmondb
ID: 38859223
Geekamo,

Hard, bitter experience!

Regards,
Brian.
0
 
LVL 24

Expert Comment

by:Steve
ID: 38859263
Geekamo...

here is a starting UDF formula:

Const pi = 3.14159265358979

Function Recommended(Obj As String, Dia As Integer, Circ As Integer, Hgt As Integer, BagT As String, Objve As String, Optional Def As Double) As String


If Obj = "Square / Rectangle" Then

    If BagType = "Gusseted" Then
        
        If Len(Def) <> 0 Then
            
            Recommended = "Bag Size: " & Dia + 1 & "W x " & Depth + 1 & "D x "
        
        Else
            
            Recommended = "Bag Size: " & Dia + 1 & "W x " & Depth + 1 & "D x "
        
        End If
    
    End If

End If





If Obj = "Cylinder" Then

    If BagT = "Gusseted" Then
        
        If Len(Def) <> 0 Then
            
            Recommended = "Bag Size: " & Dia + 1 & "W x " & Depth + 1 & "D x "
        
        Else
            
            Recommended = "Bag Size: " & Dia + 1 & "W x " & Depth + 1 & "D x "
        
        End If
    
    End If

End If

Open in new window


This is in the attached workbook and is to demonstrate a simple UDF.
It allows you to build the formula logic but without the constraints of the cell.

This way you could create the formula and using it becomes easy (see yellow cell)
To get to the code in the workbook press [ctrl]+[f11]

Can keep working on this if it looks like a good way to proceed.

ATB
Steve.
Measuring-Up---Stripped-Down-Ver.xlsm
0
 
LVL 1

Author Comment

by:Geekamo
ID: 38859375
@ Steve,

I feel bad, but I think I might have to pass on that.  Some parts of it, just reading through it - makes sense. But there are other parts, that I just don't get.  And I think even if you explained it, I'd still be up against a wall at updating it down the road if need be.

Granted my formula is super involved and confusing - but I at least know how I got to my answer by taking baby steps when I was writing the formula.

~ Geekamo
0
 
LVL 24

Expert Comment

by:Steve
ID: 38859387
That is fine, you are the user so you have to have something that works for you.

Just thought I would offer an alternative to the mega-formula.

:)
0
 
LVL 13

Expert Comment

by:MrBullwinkle
ID: 38859478
As was stated, to think you'll never have to update the formula is setting you up for failure both in this workbook, and in any endeavors of programming. One of the most critical aspects of programming is writing code thats easily maintained.

Although this equation may not need updated, if other equations get added, and then something seems wrong, it'll be near impossible to verify that this one is correct, and not another.

I very seldom write UDFs, because I think it makes it hard to follow the logic of spreadsheet, unless its something that's used over and over. Excel has A LOT of built in functions that do just about anything.

Performance wise, I'm pretty sure that if your equation uses some sub equation repeatidly, then having that in a seperate cell will allow it to be cached and not recalculated over and over.   Heres an example:
=(A+B)^2 + A^2/(A+B)^2 + B^2/(A+B)^2
Notice (A+B)^2 is used 3 times.  IF that were in a separate cell, it only gets calculated once, rather than 3x.   Not a huge deal with 1 cell, but if you were to copy this down 1000 rows, you'll feel the pain.
0
 
LVL 24

Assisted Solution

by:Steve
Steve earned 250 total points
ID: 38861611
Erm, I got a bit into the UDF and so as I have written it now I have added it to the file and posted it here... (sorry, I get bored when there is nothing good on telly)

:)

Also when reverse engineering the formula I had used [alt]+[enter] to "next line" the formula.
This can help with long formulas as you can lay the formula out to fit nicer into the formula bar. It can help in some situations.
Measuring-Up---Stripped-Down-Ver.xlsm
0
 
LVL 1

Author Comment

by:Geekamo
ID: 38866824
@ All,

Sorry for the delay in getting back to my post.  I will be back over the weekend.  Thank you in advance for your patience!

~ Geekamo
0
 
LVL 1

Author Closing Comment

by:Geekamo
ID: 38878273
@ All,

Thank you all for your input.  Since I've posted this question, and while I was away for a bit - I kept thinking about all your comments in how it's just bad practice to use a formula that is this long.

Granted, it was a good experience for myself.  And I'm happy I actually completed it, but while I've been away - I started back at the beginning...

And this time, I'm utilizing some helper cells (located on a spreadsheet that is hidden).  And I'm pretty happy with the outcome.  Reading through my formula now, it's much easier to read & comprehend.

Thank you all for your help!

~ Geekamo
0
 
LVL 26

Expert Comment

by:redmondb
ID: 38878369
Thanks, Geekamo. Sounds like a double win for you!
0

Featured Post

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

What is a Form List Box? (skip if you know this) The forms List Box is the alternative to the ActiveX list box. If you are using excel 2007, you first make sure you have a developer tab (click the Orb)->"Excel Options"->Popular->"Show Developer tab…
A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.

747 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now