expanding nested expressions

[pietlinden]

Hi,

Sorry, I'm an Access person, but I'm being paid to sort out someone's
spreadsheet that looks like it has grown over the years.

I have a very simple problem. I have a formula similar to the one
below

Column N = ((+AR15/+(1-T$12-AC$2)+BS15))

then
AR = AD15+AQ15

AD = a constant (so stop)
AQ = SUM(AF15:AP15)

I'm trying to move toward something like:
SomeColumn = Constant1 * Constant2/Constant3

Where Constant(n) is an "underived" cell. (Has no formula).
etc etc.

I can use Trace Dependencies to find all these, but is there an easy
way to have Excel do the work for me?

I am doing this so that I can basically re-create the calculated
values in queries or expressions in Access. The vast majority of the
functions are simple add, subtract, multiply, divide. About as
complicated as this spreadsheet gets is using absolute cell
references. It's just a nightmare trying to sort out all the
dependencies because you almost have to start at one end of the
dependency chain and work your way up. Then finally you have cells
that are only based on other cells that contain fixed values, if that
makes any sense.

(Well, think of simplifying algebraic expressions. That's EXACTLY
what I'm trying to do, but in this case, they're Excel formulas. Once
I have them substituted down as far as they can go, I'll move them to
Access. But there are like 70 columns of them.

Just wondering if there were an *easy* way to do this. I'm fine with
Excel VBA (well, I can understand VBA, but maybe not all of Excel's
object model, if that helps)

Thanks!
Pieter

 

[Jim Cone]

Pieter,
I don't think there is an easy way.
You may want to try out my 'Formula Map' code I posted a while back that...

' Finds worksheet formulas on each sheet in workbook.
' Adds a new worksheet and lists all formulas found and their cell addresses,
' values, precedents and dependents.
' Formulas that have error values in the formula will also appear on the list.
' Formulas that contain references to other sheets are marked with a "!" in column A.

Maybe it will be so some help.
There are over two hundred lines of code, find it here...
http://makeashorterlink.com/?D2151176C

--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware



Hi,
Sorry, I'm an Access person, but I'm being paid to sort out someone's
spreadsheet that looks like it has grown over the years.
I have a very simple problem. I have a formula similar to the one
below
Column N = ((+AR15/+(1-T$12-AC$2)+BS15))
then
AR = AD15+AQ15
AD = a constant (so stop)
AQ = SUM(AF15:AP15)
I'm trying to move toward something like:
SomeColumn = Constant1 * Constant2/Constant3

Where Constant(n) is an "underived" cell. (Has no formula).
etc etc.

I can use Trace Dependencies to find all these, but is there an easy
way to have Excel do the work for me?
I am doing this so that I can basically re-create the calculated
values in queries or expressions in Access. The vast majority of the
functions are simple add, subtract, multiply, divide. About as
complicated as this spreadsheet gets is using absolute cell
references. It's just a nightmare trying to sort out all the
dependencies because you almost have to start at one end of the
dependency chain and work your way up. Then finally you have cells
that are only based on other cells that contain fixed values, if that
makes any sense.
(Well, think of simplifying algebraic expressions. That's EXACTLY
what I'm trying to do, but in this case, they're Excel formulas. Once
I have them substituted down as far as they can go, I'll move them to
Access. But there are like 70 columns of them.
Just wondering if there were an *easy* way to do this. I'm fine with
Excel VBA (well, I can understand VBA, but maybe not all of Excel's
object model, if that helps)
Thanks!
Pieter

 

[KC]

We would try

Sub m()
for each c in "usedrange" <= you have to set this range
if c.hasformula=true then
debug.print c.address, c.formula
end if
next c
end sub

This show all cells with their formulae.
The list as is shows row wise.
If sorted by cell address, it shows column wise.

Close to what you are looking for?
Cheers!

 

[Guest]

I don't think that there is an easy way to audit a spreadsheet.

What I would do is find all the constants on the worksheet and list those
with cell address and label names

Then consider the output of the spreadsheet what is it doing can I do it.

Try recreating the details in your model.

On the auditing toolbar there is one item that is useful the button on the
far right (XP++ I think) this takes the formula and shows the steps in
calculating it.

Apart from that paper and pencil (and eraser)..

Good luck.

 

[pietlinden]

Thanks for the input, everybody. I'll check out your link, Jim. I
think I finally understand the way the sheet works. The fun thing is
that it's a homegrown looking thing, so the documentation doesn't
really exist. It's attempting to be a purchase order / quote /
invoice system, so doing the whole thing in Excel (IMHO) is either
brave or foolhardy. Excel is nice because it's really flexible, but
I'm tempted (being a database geek) to move it to Access (where I can
store the static data in tables) and just do calculations using
queries. The spreadsheet does little more than basic 3rd grade math,
so implementing it in Access should be easy. well, after I determine
what the real names of the columns should be.

the boss wants to be able to send quotes, invoices and purchase orders
to his customers and manufacturing plant... Right now I'm planning on
using ADO to transfer my Access data (customer info, invoice
discounts, line item data) to an Excel template.

Then I can also use pretty much standard routines for e-mail etc.

Anything wrong with the plan? (there are a limited number of values
that should be user-modifiable; it's a price quote system, so the only
modifiable inputs should be things like:
Markup Percent (for determining sale price of an item)

Various additional cost drivers, like:
- Discount Points,
- Shipping Costs (depends on container size),
- Freight Costs
- Packaging Costs
etc.

There are a limited number of variable values in each, but I think I'm
looking at Excel for doing "what-if" cost analysis (to determine
optimal selling price, which can depend on what the customer wants),
and then Access to store the actual independent variables. then if I
need to, I can recreate an invoice in Excel or whatever.

Anything wrong with this plan? (Just checking to make sure I haven't
left any logical parts out!) the Access stuff is pretty easy - I was
just thinking that imitating a spreadsheet in Access is *hard*, but I
could create a query based on the various relevant tables to
essentially look up data.

Okay... that about covers it. Thanks for reading. any things I
should be watching out for? Or is this a sound plan? For the final
invoice, I'm considering (1) a template with a few ranges of editable
cells. (2) locking the entire workbook before mailing (because it's a
legal contract at that point.) then if I need to, I can refer to
named ranges etc through ADO and shift data back and forth between
Access and Excel.

Pieter