1

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).

enter image description here

emnha
  • 331
  • 2
  • 4
  • 15

2 Answers2

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
  • 11
  • 1
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

enter image description here

bosco_yip
  • 831
  • 1
  • 5
  • 5
  • The question is about evaluating expression in B81 and not about composing it from scratch. – Máté Juhász Feb 10 '20 at 13:43
  • @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 Feb 10 '20 at 14:10
  • @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? – emnha Feb 10 '20 at 17:35
  • @bosco_yip I got N/A error. https://ibb.co/ZMNRrLj – emnha Feb 10 '20 at 17:44
  • @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 Feb 11 '20 at 01:22
  • @bosco_yip you're right. It works now but how about the cases that there is only one variable like `-a` or `-b`? – emnha Feb 11 '20 at 07:45
  • 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 Feb 11 '20 at 11:36