converting formulas to "atomic" formulas

[RobZahra]

The task is, find a function to convert any formula in excel into an
"atomic formula". Formally defined below, but first, an example:

Cell A1 is "=A2"
Cell A2 is "=A3+A4"
Cell A3 is "5" (atomic)
Cell A4 is "=A5+A6"
Cell A5 is "=7" (atomic)
Cell A6 is "=8" (atomic)

Then the function, given A1's formula "=A2' as input, would return
"=A3+A5+A6" as the equivalent atomic formula.


Define an "atomic cell" to be any cell containing no references to
other cells. An "atomic formula" contains only references to atomic
cells, not any other type of references. Every non-circular formula
can be reduced to an atomic formula by continually replacing the cell
references which are non-atomic with a statement of the formula itself,
as in the example

Does such a method exist, either built-in to excel or does someone have
code?

thanks

 

[Scott]

You might be able to derive something from one of these two
formula-parsing sites I've seen. It will take some manipulation by
you. (ie. you'll probably have
to parse each formula, and then recursively parse those formula, etc.)
It also depends on how complicated your formulas are going to end up
being... For example, if you're only going to be dealing with a limited
subset of functions (ie. +, -, *, /) where the parameters are only
single cells and not ranges, your task will be a lot simpler. If you
have to deal with ranges, ie. =MEDIAN(A1:A500) where each has its' own
values and dependencies, your life will be miserable, particularly if
you want working functions for your result.

http://ewbi.blogs.com/develops/2004/12/excel_formula_p.html

http://www.dicks-blog.com/archives/2005/04/01/excel-formula-formatter/

Some of the experts around here will probably have more insights.

Scott

 

[Tim Williams]

Is this a homework assignment?

You could try looking at each cells Precedents. Parsing out cell references manually might be a bit complex...

 

[RobZahra]

this is not a homework assignment, but instead the desire for a general
tool to convert excel logic into a form suitable for scripting, VB or
otherwise.

Assume "simple" functions only, like +, -, *, / .

Shouldn't this tool exist already?

 

[Scott]

It probably does... i've even written some before. Unfortunately, the
ones I've written were generally in C/C++/Perl. For what you're
specifically trying to do -- namely the substitutions so you only have
atomic values -- is fairly specialized, from what I'm used to. It's not
what I'd call difficult, merely an adaptation from basic parsers to
include substitution.

In other words, you're probably going to have to write your own code to
do it, but you can probably search for formula parsing to get
pseudocode if you need it.

Scott

 

[Tim Williams]

Should the code be able to handle references to ranges outside of the
current sheet (the one with the formula being converted) ?
What about named ranges ?

Tim

 

[Charles Williams]

If you want to make a general tool to do this it will be a non-trivial task,
particularly handling functions (user-defined functions, INDIRECT, OFFSET,
LOOKUPs, IF, CHOOSE ...), array formulae, ranges, defined names etc, let
alone conditional formatting.

For substitution of simple cell references to single cells it would be
fairly easy to write a selective parser/replacer: start by reading the
formulae in R1C1 form and then look for single cell references excluding
ranges, functions, names etc and do a recursive substitution.

Handling the general case will require a parser and an equivalent function
library in the scripting language of your choice, and I would have thought
handling arbitrary user-defined functions and event-driven calculations
would probably be put in the "too difficult" class.

There are a number of commercial products available that attempt to convert
Excel spreadsheets into locked run-time only solutions. AFAIK they all have
limitations in the degree of complexity they can handle, but I expect they
work well for simple cases.

Charles
______________________
Decision Models
FastExcel 2.3 now available
Name Manager 4.0 now available
www.DecisionModels.com