Externalizing formulas.

[Dwayne]

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

 

[Roger Govier]

Hi Dwayne

Use Named ranges to define your formulas.
With your cursor in cell B1
Insert>Name>Define>Name 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.