Can Excel do simple symbolic algebra?

Hi

You can do this either in VBA or using cells.

Your approach is good, but remember that a cell can be either a value or text, but not both. It seems to me you need to add columns that a labels for your values and incorporate the labels in your calculations.

I'm not following how that would work. Could you show me some sample code using cell formulas?  

I'll add that, using Define Name, I already have labels for my values.   The problem isn't the labeling, the problem is doing symbolic calculations on the labels.  That seems to be the sticking point.

Hi

You can do this either in VBA or using cells.

Your approach is good, but remember that a cell can be either a value or text, but not both. It seems to me you need to add columns that a labels for your values and incorporate the labels in your calculations.

= = = =

As a side note on your project:

> ..  result = Total@{a+c+d} 

You already have the total with "a+c+d". 

Perhaps you meant:

result = Total@{a, c, d}

result = {a, c, d} // Tr 

I would recommend making it a little "easier" to read:

result /. Thread[{KA, KB, KD, KT} -> KZ]

= = = =

Hi Dana.  You're of course correct that the Total command in my Mathematica example is redundant; I've updated the screenshot to eliminate that.  Thanks.

Hi.  My thoughts are this would be hard to do if you change the basic input equations.

As is, you might be able to go the vba route.

For example, we could extract the following from 1 equation:

Sub Demo()

    Dim s, v

    s = Mid([A1].Formula, 2) ' Remove =

    v = Split(s, "+")

    Debug.Print v(0)

    Debug.Print v(1)

    Debug.Print v(2)

End Sub

returns:

KA*40% 

KD*10% 

LH*45%

You could then keep track of the values as they are added. 

Then join the string parts.   

However, this would only apply if the equations follow a specific pattern.

The equations will change, but they will always follow the same pattern:

XX*##% + XX*##% + XX*##% +....

where X is any letter, and # is any digit.

I've done programming (C++, Mathematica, Perl), but never used VBA.  Nevertheless, I can see from your example how VBA could be programmed to do text replacement.  But could it then collect terms and sum them?

I.e.,  once it extracts, say, AB*45%, AB*55%, CD*80%, CD*15%, how would it know to add them to give AB*100%, CD*95%? That last part is the heart of what I need to do.  

If I only needed to display all the individual terms, I can do that already, by converting each equation to a string, using FORMULATEXT.  For instance, the following (using what I've indicated is in cell B3, above):

=IF(B3<>0,SUBSTITUTE(FORMULATEXT(B3),"=",""),"")

gives:

KA*40% + KD*10% + LH*45%

While this:

=IF(B3<>0,SUBSTITUTE(FORMULATEXT(B3),"+",", "),"")

gives:

=KA*40%, KD*10%, LH*45%

I can then combine the string versions for all the cells (B3 through E3) using CONCATENATE.

Finally, using SUBSTITUTE, I could clean up these strings to give either:

KZ*40% + KZ*10% + LH*45%+ KZ*5% + KZ*40% + GV*25% + LH*55%

or:

KZ*40%, KZ*10%, LH*45%, KZ*5%, KZ*40%, GV*25%, LH*55%

But how do I convert these strings back to formulas and get Excel to do algebra on them?  I.e., how do I get it to transform the above to either:

GV*25% + KZ*95%+ LH*100% 

or:

GV*25%, KZ*95%, LH*100% 

Can VBA do simple symbolic algebra or, like Excel, is it limited to numerical calculations?

I looked through a list of all VBA functions here (https://vbaf1.com/functions/#\_Toc5), and it seems the answer is no: that VBA cannot do simple symbolic algebra, it can only do numerical calculations.

Hi,

First, I would like to appreciate for your patience to provide the detail description of your requirement.

Coming to your requirement, I totally understand you want to get the sum of Grant 1,2,3 and 4 with the name ranges instead of displaying the value. As far as I know the requirement you are looking for is not possible in the Excel application and I also tried my best to search for any related reference but could not find anything, apologies for it.

I assume this should be possible by some programming like VBA, if you are willing to look into VBA suggestions, I will help you to move this thread to programming category so that members from that category will share their views.

Moreover, we will keep this thread open as the Excel experts in this community will share their valuable ideas or workarounds to meet your requirement.

Appreciate your patience and understanding. Have a good day and stay safe 😊

Best Regards,

Chitrahaas