S&U&M to be SUM .. is it possible?

Posted by Somkiat on June 24, 2001 9:56 PM

If I put "S" "U" "M" each in a cell, is it possible to build SUM function from them. (I've already used subtotal with 11 functions available. However I am trying a new approach.)

A1 ="S"
A2 = "U"
A3 = "M"

A4 = A1&A2&A3&"(DataRange)" to be =SUM(DataRange)
or A4 = A1&A2&A3&INDIRECT(datarange)

But this formula does not work!

I am trying to make very dynamic formula, so this question comes to my mind.

Thanks for any help,
Somkiat

Posted by Aladin Akyurek on June 24, 2001 10:50 PM

A bold NO.

Hi Somkiat,

The parser built in Excel, judging from its behavior, is nothing like the callable EVAL of LISP/SCHEME. Moreover, it's not available to the users like EVAL is. SUBTOTAL behaves interestingly in a way similar to MAPC, APPLY, and the like in LISP/SCHEME.

Hope this helps.

Aladin

You could build a spreadsheet prog in LISP (Excel-like) and add to it or make available the EVAL of LISP in it, so that things such as the one you suggest can easily be done.

Posted by AB on June 26, 2001 6:46 AM

Not so fast!

It is possible.

You do have access to an EVALUATE command function that can be setup to do exactly what you're asking for. Not entirely sure why you would want to do that but it is possible.

Suppose the following function exists in cell [A4]: = "="&A1&A2&A3&"(DataRange)"

Create a defined workbook name (lets call it "MySum") and use the following for the refers to piece: =EVALUATE(Sheet1!$A$4)

You'll then notice that if you input "=MySum" in any cell on your worksheet it will evaluate the formula in cell [A4].

Obviously, you could modify the named range to assemble the function itself and skip a step.

Regards,
AaronThe Excel Logic Page

Posted by Aladin Akyurek on June 27, 2001 8:12 AM

EVAL Vs EVALUATE

I really thought that Somkiat was aware of EVALUATE and that he really wanted something more. His example made me think of LISP's EVAL.

Following up the example: In a spreadsheet in LISP, with EVAL one of its function we would have

=(EVAL ("&A1&B1&C1&"(DataRange)")) [ Don't take the syntax form too seriously]

with as result the computed sum/total of DataRange in the cell where this EVAL-formula is.

It was my belief Somkiat was after such a possibility. Whence my "A bold No".

Aladin

=====================

Posted by Somkiat on June 28, 2001 12:29 AM

Re: EVAL Vs EVALUATE

Thank very much to both of you. I was aware of EVALUATE from www.geocities.com/davemcritchie/excel/formula.htm#UseFormula

I am very lucky that both of you helped me though it is not possible.

Thanks again,
Somkiat

: Hi Somkiat,