Calculate a mathematical formula in a field?

[rumplestiltskin]

There is either a simple command I'm unable to find or I'm trying to find a simple solution that doesn't exist.

I have a field which will contain a mathematical formula, something like this:

5*5/2

Let's call the field "dataEntered". So how do I calculate the results of the field? Do I need to parse through it or is there a simple command like:
do field "dataEntered" (that didn't work)
the result of field "dataEntered" (that didn't work either)

Thanks,
Barry

[Klaus]

Hi Barry,

VALUE is your friend:
...
put value(fld "the one with the math in it")
...



Best

Klaus

[rumplestiltskin]

Thank you, Klaus. Value may be my friend but you are, as well.

Barry

[dunbarx]

What Klaus said.

Another friend is that the value function obeys PEMDAS pretty well, but I would be careful anyway and use parentheses everywhere.

Craig Newman

[rumplestiltskin]

...Another friend is that the value function obeys PEMDAS pretty well, but I would be careful anyway and use parentheses everywhere.

Exactly what I tell my Excel students at the community college; helps make the formula more understandable when you go back to it a month later.

[Klaus]

Please enlight the unknowing, what is PEMDAS?

[sturgis]

Parenthesis
Exponents
Multiply
Divide
Add
Subtract

Order of operations mnemonic.

Please enlight the unknowing, what is PEMDAS?

[Klaus]

Ah, thanks, never heard before

[rumplestiltskin]

We learned (in 2nd grade, IIRC) PEMDAS as "Please Excuse My Dear Aunt Sally" so it would be easy to remember. I find many of my freshman college students either don't remember it or never learned it so, when I teach them Excel, it's all new to them.

[wilstrand]

Hi Barry!

I have just released rGrid which is a spread sheet inspired grid control. One of its features is that it handles formulas written into the cells (fields).
rGrid is open source so you can use the code as you wish for non commercial projects. Here below is the code that parses the formulas with cell referencing. Hope it can be of use to you!

Code: Select all

   put sGridDataA[tActiveSheet][tRowID][tColumnID]["formula"] into tCellRefs
   if tCellRefs is not empty then
      if char 1 of tCellRefs is not "=" then put "=" before sGridDataA[tActiveSheet][tRowID][tColumnID]["formula"]
      repeat for each item tItem in "=,+,-,/,*,^,<,>,sqrt,(,),if,then,put,sum,exp"
         replace tItem with cr in tCellRefs
      end repeat
      replace "$" with empty in tCellRefs
      put sGridDataA[tActiveSheet][tRowID][tColumnID]["formula"] into tValueFormula
      put empty into tTempCellRef
      repeat for each line tCellRef in tCellRefs
         if tCellRef is empty then next repeat
         switch
            case ":" is in tCellRef
               replace space with empty in tCellRef
               replace cr with empty in tCellRef
               put rangeToCells(tCellRef) into tRange
               replace ";" with comma in tRange
               replace tCellRef with tRange in tValueFormula
               put cr & rangeToCells(tCellRefs) after tTempCellRef
               break
            default
               put cr & tCellRef after tTempCellRef
               break
         end switch
      end repeat
      if char 1 of tTempCellRef is cr then delete char 1 of tTempCellRef
      replace ";" with cr in tTempCellRef
      repeat for each line tCellRef in tTempCellRef
         if tCellRef is empty then next repeat
         if tCellRef is a number then next repeat # Let numbers be calculated in formula
         put sGridDataA[tActiveSheet][item 1 of cellRefToID(tCellRef)][item 2 of cellRefToID(tCellRef)]["value"] into tCellValue
         if tCellValue is empty then put 0 into tCellValue
         if tCellValue is not a number then put false into tCanCalculate
         replace tCellRef with tCellValue in tValueFormula
      end repeat
      if char 1 of tValueFormula is "=" then delete char 1 of tValueFormula
      if tCanCalculate is not false
      then
         set the htmltext of control "CellField" of control id tCellContainerID of grp id tRowContainerID of me to value(tValueFormula)
         put value(tValueFormula) into sGridDataA[tActiveSheet][tRowID][tColumnID]["value"]
      else
         set the htmltext of control "CellField" of control id tCellContainerID of grp id tRowContainerID of me to "#VALUE!"
         put "#VALUE!" into sGridDataA[tActiveSheet][tRowID][tColumnID]["value"]
      end if
   end if

You can download the latest version of rGrid from http://www.tapirsoft.com

With my best regards

Mats Wilstrand

[rumplestiltskin]

Mats,

rGrid is v-e-r-y interesting. More than I need for the small project I am building right now but I have bookmarked the page so I can check it out later.

Thanks,
Barry