Expanding Formulas

[tralst]

I know that I can show the work in a formula by simply using a &. Example,
with A1 =2 and A2 =3, I type =A1+A2 in a cell This can be converted to the
cells values by entering =A1&"+"&A2. This will return 2+3 in the cell. My
question. Is there a shortcut to accomplish this?

 

[joeu2004]

I know that I can show the work in a formula by simply using a &. Example,
with A1 =2 and A2 =3, I type =A1+A2 in a cell This can be converted to the
cells values by entering =A1&"+"&A2. This will return 2+3 in the cell. My
question. Is there a shortcut to accomplish this?

I think you might be interested in the Evaluate Formula feature.
Typically, you select the cell with the interesting formula, then
click on Tools -> Formula Auditing -> Evaluate Formula. Even though
the feature has some limitations, it is often helpful in debugging or
understanding many complex formulas.

 

[tralst]

I don't need it for evaluation. My customer wants me to show all work in the
equations that I am using. In other words I would have 3 cells:

="Number1+Number2"
=A1&"+"&A2
=A1+A2

The actual equations are much more complicated and it takes a lot of time to
format them with the &'s included.

 

[Gord Dibben]

Would this help at all?

You could employ a user defined function and go the other way.

Function EvalCell(RefCell As String)
Application.Volatile
EvalCell = Evaluate(RefCell)
End Function

Enter 2+3 in A1 then in an adjacent cell enter =EvalCell(A1) to return 5

Probably not too practical if the 2 and 3 in A1 and A2 are formula-generated.


Gord Dibben MS Excel MVP

 

[tralst]

Thanks, unfortunately that didn't help. I have to be able to modify the input
through additional formulas and have the equations change. I thought there
would be a ctrl function or something.

 

[joeu2004]

I don't need it for evaluation. My customer wants me to show all work in the
equations that I am using.
[...]
The actual equations are much more complicated and it takes a lot of time
to format them with the &'s included.

If I understand what you mean (and I think I do), I think you -- that
is, your customer -- is asking for the "impossible", although I
certainly understand why he might want it.

Consider the following hypothetical "simple" example:

=roundup(nper(A1/12, roundup(pmt(A1/12, 12*A2, -A3), 0), -A3), 0)

I think you are saying your customer would like to see:

=roundup(nper(6%/12, roundup(pmt(6%/12, 12*30, -100000), 0), -100000),
0)

Right?

That requires interpreting each element of a formula, recognizing cell
references (of all forms), and replacing them with the evaluated value
of their formulas. It also requires that the parser recognize strings
and (usually) avoid interpreting their contents.

Even in the "simple" case above, that would require a powerful parser
and expression evaluator. Things get more interesting when a formula
involves named formulas, INDIRECT() and perhaps OFFSET(), depending on
your customer's expectations. And what would your customer expect of
formulas that include a look-up function? And what about array
formulas or array parameters?

I am as service-oriented as a person can be. But there is a point at
which I know I must tell a customer "it is cannot be reasonably done"
-- at least, not at a reasonable cost in dollars and time.

(Unless someone can find a ready-made application that perhaps that
kind of interpretation for a reasonable cost.)

There might be some reasonable alternatives that you can present to
your customer.

For example, it is not difficult to print spreadsheets that show each
formula, not their partial evaluation.

If you -- that is, your customer -- is amenable to that and you need
help doing that, that would be easy to assist you with.

On the other hand, if I misunderstood and unduly over-complicated your
requirements, forgive me -- and please clarify what you need.

For example, perhaps your formulas are not all that complicated after
all. If you can characterize your formulas in simpler terms, it might
be possible to write a macro with a very simple parser and evaluator.
("Possible", but perhaps stilll not likley, IMHO.)

 

[tralst]

I haven't used anything like the roundup function you show. My customer wants
all the work shown, just like your math teacher in high school. Equation
first, followed by the equation filled in with the numbers used, followed by
the answer. I do it all the time using a "& in front of the variable and &"
following the variable. I was hoping that since Excel allows the use of &
that there would be a easy method to do it as opposed to typing in each
equation separately. Here is a simple example:

Aradius = ((2*R3)2-Ï€*R32)/4
= ((2*0.25)^2-Ï€*0.25^2)/4
= 0.013 in2

The second cell actually contains the formulation

="((2*"&R_3&")^2-Ï€*"&R_3&"^2)/4"

This plugs in the values for R3.

I don't need it for evaluation. My customer wants me to show all work in the
equations that I am using.
[...]
The actual equations are much more complicated and it takes a lot of time
to format them with the &'s included.

If I understand what you mean (and I think I do), I think you -- that
is, your customer -- is asking for the "impossible", although I
certainly understand why he might want it.

Consider the following hypothetical "simple" example:

=roundup(nper(A1/12, roundup(pmt(A1/12, 12*A2, -A3), 0), -A3), 0)

I think you are saying your customer would like to see:

=roundup(nper(6%/12, roundup(pmt(6%/12, 12*30, -100000), 0), -100000),
0)

Right?

That requires interpreting each element of a formula, recognizing cell
references (of all forms), and replacing them with the evaluated value
of their formulas. It also requires that the parser recognize strings
and (usually) avoid interpreting their contents.

Even in the "simple" case above, that would require a powerful parser
and expression evaluator. Things get more interesting when a formula
involves named formulas, INDIRECT() and perhaps OFFSET(), depending on
your customer's expectations. And what would your customer expect of
formulas that include a look-up function? And what about array
formulas or array parameters?

I am as service-oriented as a person can be. But there is a point at
which I know I must tell a customer "it is cannot be reasonably done"
-- at least, not at a reasonable cost in dollars and time.

(Unless someone can find a ready-made application that perhaps that
kind of interpretation for a reasonable cost.)

There might be some reasonable alternatives that you can present to
your customer.

For example, it is not difficult to print spreadsheets that show each
formula, not their partial evaluation.

If you -- that is, your customer -- is amenable to that and you need
help doing that, that would be easy to assist you with.

On the other hand, if I misunderstood and unduly over-complicated your
requirements, forgive me -- and please clarify what you need.

For example, perhaps your formulas are not all that complicated after
all. If you can characterize your formulas in simpler terms, it might
be possible to write a macro with a very simple parser and evaluator.
("Possible", but perhaps stilll not likley, IMHO.)

 

[joeu2004]

I haven't used anything like the roundup function you show.

I think you missed the point I was making. It does not matter what
specific functions you use. What matters is the difficulty of writing
a parser that can handle all kinds of formulas that Excel permits.
The difficulty increases with the use of functions, especially nested
functions.

My customer wants
all the work shown, just like your math teacher in high school.

I think I get that.

I do it all the time using a "& in front of the variable and &"
following the variable.

There are many things that humans can do easily, but they are
difficult to "teach" computers to do. Driving a car is one example.
It is only recently that AI experts have developed algorithms that
permit autonomous computers to drive a vehicle through an obstacle
course.

Parsing and evaluating expressions are much easier than such AI
feats. But they are still a challenge for most Excel programmer's, I
suspect.

I was hoping that since Excel allows the use of & that there
would be a easy method to do it as opposed to typing in each
equation separately.

The operative word is "easy". To my knowledge, Excel does not provide
this feature. There might be third-party add-ins or separate
applications that do, probably at some cost. I'm not aware of any.
Presumably you can do the google search at least as well as I can.

Here is a simple example:
Aradius = ((2*R3)2- *R32)/4
= ((2*0.25)^2- *0.25^2)/4

If your formulas use only the 4 or 5 basic operators (including "^")
and nested parentheses, that makes the parser easier to design -- but
still "difficult" by most Excel programmers' standards, IMHO.

(And even the formula above uses one function, namely PI().)

I am merely trying to set your expectations -- and your customer's.
Sorry if it is not the answer you were hoping for.

 

[Dana DeLouis]

...Equation first, followed by the equation filled in with the numbers

used,
followed by the answer.

Hi. Early programming attemps usually go something like this.

= ((2*R3)^2-PI()*R3^2)/4
= ((2*0.25)^2-PI()*0.25^2)/4
1.34126147876595E-02

It becomes very difficult because it is hard to pick out what's an address
(ie R3) and what's part of another reference.

Sub Demo()
Dim s, p, a, v

[A1].Formula = "= ((2*R3)^2-PI()*R3^2)/4"
[R3] = 0.25

'Now...
s = [A1].Formula
Debug.Print s
For Each p In Range("A1").Precedents
a = p.Address(False, False)
v = p.Value
s = Replace(s, a, v)
Next p
Debug.Print s
Debug.Print Evaluate(s)
End Sub


(?? equation re-written as ((4 - Pi)*R3^2) /4
--
Dana DeLouis
Windows XP & Excel 2007

I haven't used anything like the roundup function you show. My customer
wants
all the work shown, just like your math teacher in high school. Equation
first, followed by the equation filled in with the numbers used, followed
by
the answer. I do it all the time using a "& in front of the variable and
&"
following the variable. I was hoping that since Excel allows the use of &
that there would be a easy method to do it as opposed to typing in each
equation separately. Here is a simple example:

Aradius = ((2*R3)2-?*R32)/4
= ((2*0.25)^2-?*0.25^2)/4
= 0.013 in2

The second cell actually contains the formulation

="((2*"&R_3&")^2-?*"&R_3&"^2)/4"

This plugs in the values for R3.

I don't need it for evaluation. My customer wants me to show all work
in the
equations that I am using.
[...]
The actual equations are much more complicated and it takes a lot of
time
to format them with the &'s included.

If I understand what you mean (and I think I do), I think you -- that
is, your customer -- is asking for the "impossible", although I
certainly understand why he might want it.

Consider the following hypothetical "simple" example:

=roundup(nper(A1/12, roundup(pmt(A1/12, 12*A2, -A3), 0), -A3), 0)

I think you are saying your customer would like to see:

=roundup(nper(6%/12, roundup(pmt(6%/12, 12*30, -100000), 0), -100000),
0)

Right?

That requires interpreting each element of a formula, recognizing cell
references (of all forms), and replacing them with the evaluated value
of their formulas. It also requires that the parser recognize strings
and (usually) avoid interpreting their contents.

Even in the "simple" case above, that would require a powerful parser
and expression evaluator. Things get more interesting when a formula
involves named formulas, INDIRECT() and perhaps OFFSET(), depending on
your customer's expectations. And what would your customer expect of
formulas that include a look-up function? And what about array
formulas or array parameters?

I am as service-oriented as a person can be. But there is a point at
which I know I must tell a customer "it is cannot be reasonably done"
-- at least, not at a reasonable cost in dollars and time.

(Unless someone can find a ready-made application that perhaps that
kind of interpretation for a reasonable cost.)

There might be some reasonable alternatives that you can present to
your customer.

For example, it is not difficult to print spreadsheets that show each
formula, not their partial evaluation.

If you -- that is, your customer -- is amenable to that and you need
help doing that, that would be easy to assist you with.

On the other hand, if I misunderstood and unduly over-complicated your
requirements, forgive me -- and please clarify what you need.

For example, perhaps your formulas are not all that complicated after
all. If you can characterize your formulas in simpler terms, it might
be possible to write a macro with a very simple parser and evaluator.
("Possible", but perhaps stilll not likley, IMHO.)

 

[joeu2004]

Parsing and evaluating expressions are much easier than such AI
feats. But they are still a challenge for most Excel programmer's, I
suspect.

Wow, was I wrong! Exposes my ignorance of Excel objects and the power
of VBA.

Hi. Early programming attemps usually go something like this.
[....]
Sub Demo()
Dim s, p, a, v

[A1].Formula = "= ((2*R3)^2-PI()*R3^2)/4"
[R3] = 0.25

'Now...
s = [A1].Formula
Debug.Print s
For Each p In Range("A1").Precedents
a = p.Address(False, False)
v = p.Value
s = Replace(s, a, v)
Next p
Debug.Print s
Debug.Print Evaluate(s)
End Sub

This is really great, Dana!

I was not aware of the Precedents method and the Replace() VBA
function, which obviates the need to parse.

With the following changes, I suspect this demonstrates a paradigm
that begins to approach what the OP is looking for.

I put appropriate values into A1, A2 and A3, and I put my "complex"
formula into A4. I also put just the PMT() and NPER() subexpressions
into A5 and A6.

I changed your macro to the following:

Sub Demo()
Dim s, p, a, v
Dim cell

for each cell in Selection
s = cell.Formula
Debug.Print s
For Each p In cell.Precedents
a = p.Address(False, False)
v = p.Value
s = Replace(s, a, v)
Next p
Debug.Print s
Debug.Print Evaluate(s)
Range("Sheet2!" & cell.Address).Formula = s
next cell
End Sub

Thus, when I selected A4:A6 in Sheet1, then executed the macro, Sheet2!
A4:A6 mirrored Sheet1!A4:A6, but with cell references replaced by
their values.

Great work, Dana!