2

I have an excel sheet which I need to run on Android. Only problem, I used VBA for the Evaluate Function and VBA does not work on Android Apps. I was searching for an alternative and came a round the function "VALUE()", I saw that a Cell with:

=VALUE(10 + 5 * 3)

Would return the correct result (25). Now what I would like to do is:

=VALUE(A2)

Where A2 is a text containing "10 + 5 * 3". But this returns "#VALUE!"

Is there a trick how i could use VALUE() to evaluate simple formulas?

Thanks!

  • I'm afraid its not possible to evaluate text as formula without VBA. – Máté Juhász Jun 3 '15 at 19:56
  • is the function "VALUE()" not doing this? In my first example it takes a formula and prints the result, the only thing that is missing is how to use a cell instant of writing the formul in the VALUE(...) – Darkpara Jun 3 '15 at 22:10
2

There is a hack that has been around for years that does this in the full version of Excel. I doubt it will work on Android but it may be worth trying.

I'm using Excel 2013.

Go to the FORMULAS ribbon bar and choose Define Name.

Give any name, for example myResult

In the "Refers to:" part, put the formula =EVALUATE(A2)

Now, put the formula =myResult in your sheet where you want the answer to appear.

  • Already found this solution, and no dont work on Android :( Thanks anyway – Darkpara Jun 3 '15 at 22:08
  • Hmm, didn't think it would to be honest but worth a try anyway. I'm afraid you are out of luck then. Value() only evaluates a number written as text not a formula. – Julian Knight Jun 3 '15 at 23:39
1

Use INDIRECT().

Example (from Microsoft Office INDIRECT function):

Data
B2     1.333
B3     45
George 10
5      62
Formula            Description                                                  Result
'=INDIRECT(A2)     Value of the reference in cell A2. The reference is to       1.333
                   cell B2, which contains the value 1.333.                                   
'=INDIRECT(A3)     Value of the reference in cell A3. The reference is to          45
                   cell B3, which contains the value 45.
'=INDIRECT(A4)     Because cell B4 has the defined name "George," the reference to 10
                   that defined name is to cell B4, which contains the value 10
'=INDIRECT("B"&A5) Combines "B" with the value in A5, which is 5. This, in turn,   62
                   refers to cell B5, which contains the value 62.

Applies To: Excel 2016, Excel 2010, Excel Starter, Excel 2013, Excel Online, Excel 2016 for Mac, Excel for Mac 2011, Excel 2007

  • It's OK to use an example from an official source in an answer as long as you provide a link along with it to show where it came from. – karel Dec 1 '15 at 11:24
  • @Karel, thanks for the beautiful edit. How did you get on to me that I stole this from MS? :) – DraxDomax Dec 1 '15 at 11:38
  • Purely by accident, after reading your answer I searched for documentation about the Excel INDIRECT function and that link came out near the top of the Google search results. – karel Dec 1 '15 at 11:48
  • @karel You mean you actually searched in google to get the information you needed instead of just asking someone? LIFEHACK! :D – DraxDomax Dec 1 '15 at 11:52
  • It isn't clear how this accomplishes what was asked in the question. Can you add an example that shows how to convert a text formula, like 10 + 5 * 3, to a calculated result (25) using INDIRECT? – fixer1234 Dec 1 '15 at 18:39

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.