I have a spreadsheet that has to calculate a list price for thousands of
products from about ten different factors. The problem is that there are
about 12 different formulas to calculate the list price base AND those
formulas change frequently and are almost randomly assigned to the products.
Is there a way to externalize a formula in Excel to make changing the formula
easier?

List Price Factor 1 Factor 2 Factor 3 Factor 4 Factor 5 Etc.
Product 1 Formula 1 12 23 15 16 10
Product 2 Formula 2 2 14 9 5 6
Product 3 Formula 4 45 78 54 5 32
Product 4 Formula 1 34 75 54 5 67
Product 5 Formula 5 23 23 65 7 34
Product 6 Formula 3 1 54 67 55 23
Product 7 Formula 1 32 43 78 45 12
Product 8 Formula 5 23 67 89 3 43
Etc.

Formula 1 = (C + D)/F + E/G
Formula 2 = (C + D)/G + E/F
Formula 3 = (G + C)/F + E/G
Formula 4 = (D + D*2)/E + E/G
Formula 5 = (C + D)/(F*1.12) + E/G
Formula 6 = (C + D)/(F+G) + E/G

Hi Dwayne

Use Named ranges to define your formulas.
With your cursor in cell B1
InsertNameDefineName Formula1 Refers to = (C1 + D1)/F1 + E1/G1
Repeat the procedure for each of the other formulae.

Then in column B enter =Formula1 or whatever is appropriate for each
product. As the references are all relative, they will adjust as you
enter further down column B.

To amend any formula, just amend the named item.

--
Regards

Roger Govier


"Dwayne" wrote in message
...
I have a spreadsheet that has to calculate a list price for thousands
of
products from about ten different factors. The problem is that there
are
about 12 different formulas to calculate the list price base AND those
formulas change frequently and are almost randomly assigned to the
products.
Is there a way to externalize a formula in Excel to make changing the
formula
easier?

List Price Factor 1 Factor 2 Factor 3 Factor 4 Factor 5 Etc.
Product 1 Formula 1 12 23 15 16 10
Product 2 Formula 2 2 14 9 5 6
Product 3 Formula 4 45 78 54 5 32
Product 4 Formula 1 34 75 54 5 67
Product 5 Formula 5 23 23 65 7 34
Product 6 Formula 3 1 54 67 55 23
Product 7 Formula 1 32 43 78 45 12
Product 8 Formula 5 23 67 89 3 43
Etc.

Formula 1 = (C + D)/F + E/G
Formula 2 = (C + D)/G + E/F
Formula 3 = (G + C)/F + E/G
Formula 4 = (D + D*2)/E + E/G
Formula 5 = (C + D)/(F*1.12) + E/G
Formula 6 = (C + D)/(F+G) + E/G