0
I have a text expression written with single quote in front '-a+b. Then it's substituted as in the image. I want to evaluate the expression in yellow (evaluate to 4) instead of displaying like that. How can I do that? I tried Evaluate function but the function doesn't exist in my version (Excel 2019).
anhnha
Posted 2020-02-10T07:47:01.637
Reputation: 199
1
You could use the following and adapt as required for choosing the lookup values. (For example, you might have 10 variables, not just "a" and "b".) You might also need to adapt for operators and the negative sign if other expressions need evaluated.
=-HLOOKUP("a",$A$77:$D$78,2,FALSE)+HLOOKUP("b",$A$77:$D$78,2,FALSE)
The above is the simple version assuming that exact set of variables and that exact expression.
A note of interest for you since you indicate you sought a solution with `EVALUATE()~. You DO have access to that formula. Where you are running into trouble is that it does NOT work "cell-side" (in the open spreadsheet. It is only available in the Named Range functionality.
The idea would be for you to make a Named Range, perhaps "Answer", and use this formula in the "Refers to" box:
=EVALUATE(-a+b)
You would need to EITHER replace the "a" and "b" in that with the corresponding portions of the formula from above, of define them as Named Ranges themselves. I prefer the latter personally, though some single letters, "c" for example, are not available (it conflicts with the "C" in the R1C1 addressing style; so does "r"). You could give the Named Ranges names like "AA" and so on, using SUBSTITUTE() for the raw input from the cell. Or just the ones Excel won't let you use directly.
The important element of this, since my wager is that you found an EVALUATE() solution elsewhere and just need to make it work, is:
EVALUATE() MUST BE USED IN NAMED RANGES OR IT WILL NOT WORK. And EVERYONE can use it, not just some, so long as they use it inside the Named Range functionality.
All the old Excel 4 macros are available that way: in Named Ranges, never, ever, in the cells of the spreadsheet itself.
Jeorje
Posted 2020-02-10T07:47:01.637
Reputation: 11
0
Try......
Assume data housed in A2:D3
In B6, formula copied down :
=IMREAL(IMDIV(SUBSTITUTE(SUBSTITUTE(A6,MID(A6,2,1),HLOOKUP(MID(A6,2,1),A$2:D$3,2,0)),MID(A6,4,1),HLOOKUP(MID(A6,4,1),A$2:D$3,2,0))&"i","1+i"))*2
bosco_yip
Posted 2020-02-10T07:47:01.637
Reputation: 269
The question is about evaluating expression in B81 and not about composing it from scratch. – Máté Juhász – 2020-02-10T13:43:18.587
@Máté Juhász, Please read the OP's request : "....I want to evaluate the expression in yellow (evaluate to 4) instead of displaying like that. How can I do that?", I understand the OP wanted the calculation result (4) in his example and doesn't cell displaying of (-2+6) – bosco_yip – 2020-02-10T14:10:37.310
@bosco_yip that looks good but I'm not sure why it doesn't work on my computer. Could you explain a bit about the function and how it works? – anhnha – 2020-02-10T17:35:28.063
@anhnha, your posted formula appear typo in: ...HLOOKUP(MID(...),A$3:D$3),2,0)... it should read as : ...HLOOKUP(MID(...),A$2:D$3),2,0)... – bosco_yip – 2020-02-11T01:22:11.103
@bosco_yip you're right. It works now but how about the cases that there is only one variable like -a or -b? – anhnha – 2020-02-11T07:45:42.220
For 1 variable, wrap with a IF function, something like : IF(LEN(A6)=2,SUBSTITUTE(A6,MID(A6,2,1),HLOOKUP(MID(A6,2,1),A$2:D$3,2,0)),TheAboveFormula) – bosco_yip – 2020-02-11T11:36:11.303
2
Does this answer your question? Excel function that evaluates a string as if it were a formula?
– Akina – 2020-02-10T08:09:48.260If your problem is this much only then better assign NAME to cell A78 & C78 then use both to Add!! – Rajesh S – 2020-02-10T08:48:01.417
@Akina no, I tried that before asking and I did mention that evaluate not exist. – anhnha – 2020-02-10T09:17:40.090
@RajeshS I have a lot of expression and need to use substitute – anhnha – 2020-02-10T09:19:00.767
evaluateExists in VBA in all versions of Excel – Máté Juhász – 2020-02-10T15:26:06.977