excel-combining several long formulas

[msucog]

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

 

[prost]

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

 

[electricpete]

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.

 

[GregLocock]

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

SIGlease see FAQ731-376 for tips on how to make the best use of Eng-Tips.

 

[IDS]

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

http://www.interactiveds.com.au

 

[dik]

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

 

[msucog]

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.

 

[IDS]

msucog - you may find the link below useful.

http://newtonexcelbach.wordpress.com/2008/02/21/hello-world-in-a-udf/

Doug Jenkins
Interactive Design Services

http://newtonexcelbach.wordpress.com/

 

[SteveGregory]

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.