0

Suppose I have an Excel formula that outputs a string of the form:

"X*10 + Y*30 + Y*40 + X*20"

How do I get Excel to collect terms and output, verbatim, their sum? I.e.:

"X*30 + Y*70"

Ideally I'd like to do this with formulas instead of a VBA script.

I'm using Excel for Mac in Office 365 Business (latest version).

I examined the accepted answer at: Excel function that evaluates a string as if it were a formula?

To try implementing it, I first tried Evaluate as if it were a "hidden" built-in function ["=EVALAUTE(....)"] and that didn't work. So I saved the VBA script in the accepted answer as a module, and tried it as a worksheet function ["=ev(....)], and that didn't work either. I suspect these Evaluate functions are designed to give numerical outputs rather than symbolic ones

theorist
  • 111
  • 3
  • (1) I find this question hard to understand. Part of the problem is that you’re asking for a format / notation that I’m not familiar with. But another part of the problem is that you define some cells in Row 1, and then *use* some cells in Rows 13 and 14 without defining them. (2) You seem to be saying that you want cells `A1`-`D1` to display the strings you want. That’s probably going to be just about impossible. What we typically do in cases like this is put the display strings in cells `A2`-`D2`, for example. … (Cont’d) – Scott - Слава Україні Jul 31 '20 at 04:41
  • (Cont’d) …  (3) Also, it becomes a lot more manageable if you put the coefficients in a matrix, rather than actually typing the formulas. (4) You have GV and LH standing for themselves, and KZ standing for the combination of KA, KB, KD, and KT.  Will that scheme last forever? (Surely there’s a possibility that you’ll hire a new employee someday.) Are you at least willing to have the grouping rules dynamic? … (Cont’d) – Scott - Слава Україні Jul 31 '20 at 04:41
  • (Cont’d) …  (5) At the risk of being snarky, it works better if you figure out what your question is before you start to post it. You initially posted this question 3 hours ago, and already you’ve edited it nine times — two since I started reading it. And that also makes it hard to understand. – Scott - Слава Україні Jul 31 '20 at 04:41
  • @Scott Sorry about that. I've completely rewritten the question in a way that I hope makes it clearer. – theorist Aug 01 '20 at 19:33
  • Does this answer your question? [Excel function that evaluates a string as if it were a formula?](https://superuser.com/questions/253353/excel-function-that-evaluates-a-string-as-if-it-were-a-formula) – Máté Juhász Aug 04 '20 at 04:28
  • @MátéJuhász Thanks, gave it a try, didn't work. Please see the end of my post, above. – theorist Aug 04 '20 at 05:34

1 Answers1

0

It appears the same question was asked on the Microsoft support forums: https://answers.microsoft.com/en-us/msoffice/forum/all/can-excel-do-simple-symbolic-algebra/bfb67bbc-5d9f-4dbf-868b-110237c58d89?auth=1

The answer there by a Microsoft agent was that no, Excel does not natively support this. They suggest that VBA may be leveraged to perform these more advanced computations, but that, in its default form, Excel does not perform symbolic algebraic computation.

music2myear
  • 40,472
  • 44
  • 86
  • 127