Add unknown variables to Excel

Question

Here is a screenshot from my TI CAS:

I would like to do the same into an Excel file. I want to put "x" in a cell and make other cells compute with it.

Is there a way to add "unknown variables" to Excel? I don't want it to solve anything, but I need to have values like: 0.06*x+66000

EDIT

For example, if D2 = x and E3 = 2, I want to have "x-2" if I enter "=D2-E3" in a cell.

EDIT

The "x" must be evaluate, not only concatenate. It must simplify the next equation like shown in the screenshot. For example, if you have: A1: x-2 and A2: 3, if you do: A3: =A1*A2 you should have: 3x-6.

Do you mean you want to enter an equation as literal text? If so, just prepend with `'`. Otherwise, please explain a bit more about what you are trying to do, — Ken Y-N, Apr 17 '16 at 23:43
For example, if D2 = x and E3 = 2, I want to have "x-2" if I enter "=D2-E3" in a cell. Is it clearer? Prepending ```'``` doesn't work. — Annie Caron, Apr 17 '16 at 23:51
I wonder if [this is close to what you are looking for](http://superuser.com/a/776730/347185)? — Ken Y-N, Apr 18 '16 at 00:05
Thank you for the link, but it is not what I'm looking for! It needs to stay textual and not a number. — Annie Caron, Apr 18 '16 at 00:12
you can do this by custom built formula using VBA. Depending on how complex ur function gets, this can be achieved. For function like 0.06x + 66000, this if fairly straight forward. The UDF just need to return the string of that. — Rosetta, Apr 18 '16 at 01:48
your 2nd EDIT is a bit too much to ask for... sry to say that ... — Rosetta, Apr 18 '16 at 15:22

OldUgly · Answer 1 · 2016-04-18T03:07:23.767

2

Here it is with a user defined function.

And the code ...

Public Function MakeEqn(inputStr As String) As String
Dim tempStr As String
Dim rngStr As String

Dim myRng As Range
Dim iLoop As Long, jLoop As Long

    Application.Volatile

    tempStr = ""
    Set myRng = Nothing
    iLoop = 1
    Do While iLoop + 1 <= Len(inputStr)
        On Error Resume Next
        For jLoop = 2 To 4
            rngStr = Mid(inputStr, iLoop, jLoop)
            Set myRng = Range(rngStr)
            If Not myRng Is Nothing Then Exit For
        Next jLoop
        On Error GoTo 0
        If myRng Is Nothing Then
            tempStr = tempStr & Mid(inputStr, iLoop, 1)
        Else
            tempStr = tempStr & myRng.Value
            Set myRng = Nothing
            iLoop = iLoop + jLoop - 1
        End If
        iLoop = iLoop + 1
    Loop

    MakeEqn = tempStr
    Set myRng = Nothing
End Function

edited Apr 18 '16 at 03:07

answered Apr 18 '16 at 03:01

OldUgly

2,129
3
13
21

it would be better if you can do `MakeEqn(inputStr as Range) as String` then convert inputStr to String programmably so that the equation get updated concurrently as cells A1:A3 are changed – Rosetta Apr 18 '16 at 12:58
If I do ```=MakeEqn("2*A3+A2")``` in B2 and ```=MakeEqn("A1*(B2)")``` in B3, I get: 4*2*x+2. I would like to have 8x+2 or 8x+4 if it was 4*(2*x+2). My TI can do that without having to do anything in particular, but can't get Excel to do it... – Annie Caron Apr 18 '16 at 14:56
@KSSheon - 'Application.Volatile' forces the recalc when something on the worksheet is changed. I agree, it would be better if the input was not enclosed in quotes. – OldUgly Apr 18 '16 at 16:56
@AnnieCaron - at this point, it is reasonably straight forward to add more processing. e.g. - whenever two "numbers" appear on both sides of an operator, perform the operation. I'd be interested in what you come up with. – OldUgly Apr 18 '16 at 17:00
@OldUgly Well, I think if there's no solution out of the box, we can add a lot of things to process every possible configuration... I thought Excel was able to handle algebraic formulas and do some calculation on them like a TI calculator can do. It seems like numbers are mandatory! I'm not a VBA dev so I think I'll stick with my TI for now since I do not have much time to learn and develop something. Thanks for the help. – Annie Caron Apr 18 '16 at 18:44

Forward Ed · Answer 2 · 2016-04-18T01:56:29.857

1

for you edit case, in say E4 place this formula

=D2&"-"E3

Write out your formula and concatenate (&) it together with the parts that you will have changing with cell values. Cell values can be numbers or text (ie. X) to suite your needs.

Below is an example for the basic equation of a line done two ways.

In the first method I just re-wrote the equation keeping the math operators and variable x as text, and replacing the constants M and B with their cell values.

=D4&"*x+"&D3

The other approach is to substitute cell value for text which is what I did over in the F to H columns. I used the following formula in G6:

=SUBSTITUTE(SUBSTITUTE(G2,G3,H3),G4,H4)

using the substitute method is not without some potential pit falls. You need to substitute the longer variables before the shorter variables. Ie. you need to replace Ay before you replace A. If you replace A with 9 first in the formulas Ay+A, you would wind up with 9y+9 which is not what you want to happen.

If you need to calculate some value first as shown in the third example in the F9 area, note how the equation for the cell G14 is mixed with some math calculations occurring as well as some string values staying put.

=(B11-B10)/(C11-C10)&"*x+"&C10-(B11-B10)/(C11-C10)*B10

UPDATE

In G14 we now have the formula with just x in it. Let say we know what X is at this stage and we want to solve for Y. Let put X in G15 and its value in H15 and do the following in G16:

=SUBSTITUTE(G14,G15,H15)

This will get up the formula with the X value replaced and seeing just numbers. Select cell G17 so it is the active cell, then go to your Formulas Ribbon and select Define name.

Place the name of your formula in the top, I used "answer", and in the bottom enter the following

=evaluate(G16)

Then in cell G17 use your new defined name, so in my case I used:

=answer

What answer does is evaluates the cell immediately above where I entered it. I can use "answer" anywhere on this sheet to evaluate the cell immediately above it.

edited Apr 18 '16 at 01:56

answered Apr 18 '16 at 00:46

Forward Ed

9,484
3
22
52

It does indeed concatenate the string, but it doesn't calculate with it. If you change the "x" value to "8", it will only do: "8*45000" instead of "x*45000". If you look at my screenshot, it must simplify values along the way... Is there any other way then just concatenation? – Annie Caron Apr 18 '16 at 01:36
funny you should mention this I wound up developing a User Defined Function to do something very similar. You stated in your question that you did not want to solve anything, just wanted to keep X in the formula. If you want to go the next step and replace your X with a value, you can do like you already have and use a built in function in excel called EVALUATE. EVALUATE will take a string that is numbers and math operators layed out in a format excel would normally read and returns the value. The trick to evaluate is you need to use it in named range if I recall correctly. I will update. – Forward Ed Apr 18 '16 at 01:42
This is NOT the same as what you posted but its how I am dealing with general formulas, substituting in values and solving them. that way anyone checking on paper can follow what numbers were use. It uses the substitution technique showing above, evaluates intermediate variables and evaluates the equation using the evaluation method show in the answer above. [Example](http://i.imgur.com/Zf8Fu0n.png?1), let me know if this is helpful – Forward Ed Apr 18 '16 at 02:05
In order to simplify between steps you either have to do it through concatenation, or use another program like Mathcad...There are websites out there that will simply you equations for you. but that would involve some cutting and pasting. I think I use [wolfram alpha](http://www.wolframalpha.com/) and have had it do some integrals for me since I am a little rusty with my calculus. – Forward Ed Apr 18 '16 at 02:09
That's why I said: ```I want to put "x" in a cell and make other cells compute with it.```. I added that I don't want anything to be solved not to receive any answer like: use Excel Solver or What-if. You can ask question if it's not clear, because it's hard to explain. I edited my answer and I will read and try your solution tonight, thanks for your time. – Annie Caron Apr 18 '16 at 14:39

Add unknown variables to Excel

2 Answers2

UPDATE