Is there a way to evaluate a formula that is stored in a cell?

Question

In a Google Docs spreadsheet, I'm looking for something like =EVAL(A1) where A1 is set to "=1+2".

I found out that in MS Excel there is an EVALUATE() function (which seems a bit tricky to use properly). But I could not find anything similar in Google Docs.

I also searched through the function list, but could not find anything helpful...

Answer 1

No, there's no equivalent to Excel's EVALUATE() in Google Sheets.

There's long history behind this one, see this old post for instance.

If you're just interested in simple math (as shown in your question), that can be done easily with a custom function.

function doMath( formula ) {   // Strip leading "=" if there   if (formula.charAt(0) === '=') formula = formula.substring(1);   return eval(formula) } 

For example, with your A1, put =doMath(A1) in another cell, and it will be 3.

Answer 2

Short answer

As was mentioned previously, Google Sheets doesn't have a built-in EVALUATE function, but Google Sheets could be extended to add this function. Fortunately some SocialCalc files could be used to make this easier.

Script

On Google spreadsheet I'm sharing my progress. At this time I added the SocialCalc files that I think that are required and a couple of functions, and several test cases.

NOTES:

1. Google Sheets specific functions like FILTER, UNIQUE, among others are not available in SocialCalc as well as other functions like SIGN.
2. I think that the SocialCalc file should be replaced by those on https://github.com/marcelklehr/socialcalc as it looks to be updated recently. H/T to eddyparkinson (see https://stackoverflow.com/a/16329364/1595451)

Uses

The EVALUATE function on the linked file could be used as a custom function.

Example 1

A1: '=1+2 (please note the use of an apostrophe to make the formula be treated by Google Sheets as a string.

B1 formula:

=EVALUATE(A1) 

B1 display value:

3 

Example 2

To "EVALUATE" a formula like =VLOOKUP(2,A1:B3,2), at this time we need to use the "advanced" parameters. See the following example:

B1: '=VLOOKUP(2,A1:B3,2)

C1 formula:

=EVALUATE(B1,"data","A1:B3") 

C1 display value:

B 

Code.gs

/**  *  * Evaluates a string formula  *  * @param {"=1+1"}  formula   Formula string   * @param {"Tests"} sheetName Target sheet.   * @param {"A1"}    coord     Target cell.   *   * @customfunction  *  */ function EVALUATE(formula,sheetName,coord){   // SocialCalc Sheet object   var scSheet = new SocialCalc.Sheet();   if(sheetName && coord){     // Pass values from a Google sheet to a SocialCalc sheet     GS_TO_SC(scSheet,coord,sheetName);   }   var parseinfo = SocialCalc.Formula.ParseFormulaIntoTokens(formula.substring(1));   var value = SocialCalc.Formula.evaluate_parsed_formula(parseinfo,scSheet,1); // parse formula, allowing range return   if(value.type != 'e'){     return value.value;   } else {     return value.error;   } } /**  *  * Pass the Google spreadsheet values of the specified range   * to a SocialCalc sheet  *  * See Cell Class on socialcalc-3 for details  *  */ function GS_TO_SC(scSheet,coord,sheetName){   var ss = SpreadsheetApp.getActiveSpreadsheet();   if(sheetName){     var sheet = ss.getSheetByName(sheetName);     var range = sheet.getRange(coord);     } else {       var range = ss.getRange(coord);   }   var rows = range.getNumRows();   var columns = range.getNumColumns();   var cell,A1Notation,dtype,value,vtype;   // Double loop to pass cells in range to SocialCalc sheet   for(var row = 1; row <= rows; row++){     for(var column = 1; column <= columns; column++){       cell = range.getCell(row,column);       A1Notation = cell.getA1Notation();       value = cell.getValue();       if(cell.isBlank()){         dtype = 'b';         vtype = 'b';       } else {         switch(typeof value){           case 'string':             dtype = 't';             vtype = 't';             break;           case 'date':           case 'number':             dtype = 'v'             vtype = 'n';             break;         }       }       scSheet.cells[A1Notation] = {         datavalue: value,         datatype: dtype,         valuetype: vtype       }     }   } } 

formula1.gs

https://github.com/DanBricklin/socialcalc/blob/master/formula1.js

socialcalcconstants.gs

https://github.com/DanBricklin/socialcalc/blob/master/socialcalcconstants.js

socialcalc-3.gs

https://github.com/DanBricklin/socialcalc/blob/master/socialcalc-3.js