Be seen. Boost your question’s priority for more expert views and faster solutions

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

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

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.

(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

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

@ The_Barman,

Measuring-Up---Stripped-Down-Ver.xlsx

Measuring-Up---Stripped-Down-Ver.xlsx

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.

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.

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.

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.

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.

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

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.

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.

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
```

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

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

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

:)

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.

:)

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

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

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

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.

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.