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?

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.

(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 ")

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

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

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!

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.

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.

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.

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.

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. :)

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.

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.

Const pi = 3.14159265358979Function Recommended(Obj As String, Dia As Integer, Circ As Integer, Hgt As Integer, BagT As String, Objve As String, Optional Def As Double) As StringIf 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 IfEnd IfIf 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 IfEnd If

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.

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.

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

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.

Thanks, Geekamo. Sounds like a double win for you!

0

Featured Post

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

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.