×
INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

excel-combining several long formulas

excel-combining several long formulas

excel-combining several long formulas

(OP)
anyone have a way to combine several long formulas in to one formula? the formulas are long and when i start adding them together in to one formula (one cell), excel eventually tells me the formula is too long. or is there a way to "optimize" a formula or set of formula to remove/combine common variables (sort of like eq1=x+2x+y+5x+2y-->eq1new=8x+3y) except that the equations i am dealing with are much more involved than the simple one i used as an example.

essentially, i've got to combine several long formulas in to one cell for the sake of applying it to many rows of data. i would like to set it up as a drag and drop equation since there's a few thousand cells of data.

i would prefer to stay away from using macros or solver functions. i've already got the equations broken apart in multiple cells...now i've just got the problem of combining them in to one cell and the program limits the formula length.

thanks in advance

RE: excel-combining several long formulas

write a macro, that would allow you to write and combine as many equations if you like.

 

RE: excel-combining several long formulas

I definitely agree with prost Very long formula's in a cell are tough to compose and even tougher for someone else to figure out or troublshoot (or maybe tough for you if you come back in 18 months and try to understand what you did).  The programming environment allows you to assign meaningful variable names and modularize to break a complex task into simpler chunks.

As far as execution speed, I think excel formula's are often quicker. Don't know if that is a big issue in your situation.

=====================================
Eng-tips forums: The best place on the web for engineering discussions.

RE: excel-combining several long formulas

One possibility is to cut and paste all the formulas into Mathcad, assemble them into one equation, and hit "Simplify"

You may be lucky.

Personally I often build long equations up across several columns, then just copy the whole lot down for the whle lot of data.




 

Cheers

Greg Locock

SIG:Please see FAQ731-376: Eng-Tips.com Forum Policies for tips on how to make the best use of Eng-Tips.

RE: excel-combining several long formulas

Another vote here for keeping the formulas simple over several colums, and copying down.

That or write a UDF, which will be much easier to check, and give you much more flexibility.  If recalculation speed is an issue you can read all the data into an array, write the results into an array inside VBA, then paste the function as an array function, so that the data transfer between the spreadsheet and VBA only occurs once per calculation, not once for every cell.  Performance should then be as good or better than a complex worksheet formula.

Doug Jenkins
Interactive Design Services
www.interactiveds.com.au
 

RE: excel-combining several long formulas

When I have a long or complicated formula, I ofter code it into separate cells and then cut and past them into the intended one.

Dik

RE: excel-combining several long formulas

(OP)
i think a udf may work...never used it before but i'll give it a whirl. since i'd be combining several functions in to one, it may reduce the formula enough for it to fit.

RE: excel-combining several long formulas

You could keep the formulas short and spread them over several columns. Then hide those columns from view. Then put in as many rows of data that you want. It might make it easier to review, edit and understand later.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members!


Resources