Super Long Formula
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
@ 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 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
(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
Geekamo, please post the formula in an example workbook...
We love to have a game of "shortest formula" (although Barry is hard to beat)
We love to have a game of "shortest formula" (although Barry is hard to beat)
ASKER
@ The_Barman,
Measuring-Up---Stripped-Down-Ver.xlsx
Measuring-Up---Stripped-Down-Ver.xlsx
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.
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.
ASKER
@ 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.
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.
ASKER
@ 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.
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.
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.
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.
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.
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.
ASKER
@ 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.
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.
ASKER
@ 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
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
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.
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.
ASKER
@ 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.
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.
Geekamo,
One hidden sheet v. 6 files?!
Regards,
Brian.
One hidden sheet v. 6 files?!
Regards,
Brian.
ASKER
@ Brian,
Hmm, you make a good point. :)
~ Geekamo
Hmm, you make a good point. :)
~ Geekamo
Geekamo,
Hard, bitter experience!
Regards,
Brian.
Hard, bitter experience!
Regards,
Brian.
Geekamo...
here is a starting UDF formula:
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
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 IfThis 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
ASKER
@ 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
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
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.
:)
Just thought I would offer an alternative to the mega-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.
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.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
@ 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
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
ASKER
@ 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
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
Thanks, Geekamo. Sounds like a double win for you!
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.