-1

I'm trying to make an Excel sheet where people can easily change the formulas in multiple column. Viewers will have varying degrees of Excel experience.

Goals

  1. Users can adjust how columns are calculated
  2. Users can adjust how columns are calculated without directly editing every cell in the column or knowing excel syntax
  3. When needed, users can easily edit cells using Excel syntax (don't need to parse multiple levels of IF statements).

Potential Methods

  1. All possible formulas are in every column cell, with IF statements. The simplest way is to put IF statements in the formulas, which reference option cells (e.g. Enter 1 to calculate a sum, 2 to calculate a product). However, once there are multiple parameters like these, the formulas in each cell become very complicated and difficult for a new viewer to edit.
  2. Like above, but put formulas in separate cells using EVALUATE. E.g. If option 1 is selected, calculated cell references formula in A1; if option 2 is selected, calculated cell references formula in A2. In this option the formulas are easily editable. However, EVALUATE is not available in current versions of Excel for Mac, and some users will be on Mac.

Example

In the table below, c = a + b

C is sum?       1
C is product?   0

a   b   c
1   2   3
4   5   9
2   2   4
5   3   8
6   1   7

I'd like users to easily change column c to c = a * b

C is sum?       0
C is product?   1

a   b   c
1   2   2
4   5  20
2   2   4
5   3  15
6   1   6

I don't know VBA but welcome VBA answers.

cybernetic.nomad
  • 5,951
  • 15
  • 26
Unrelated
  • 149

1 Answers1

1

You could use the following (nice to find a worthwhile, not forced, use for SWITCH(), finally):

=SWITCH($E$1,"Sum",A2+B2,"Product",A2*B2)

You'd take their input about what to do in cell E1. What would really make it work nicely would be a Data Validation rule for E1. I used a List with the options Sum,Product. Must remember those double quotes around the options inside SWITCH().

Another approach could use LET():

=LET(Sum,A2+B2,Product,A2*B2,IF($E$1="Sum",Sum,Product))

Of course, there's the old EVALUATE() using a variety of methods to get the formula parts. One is to use a VLOOKUP() which returns complete formulas to EVALUATE(). Could combine that for the large structure if more complicated formulas are desired, with either of the above for the minor details inside them, like ranges. You are clearly aware of EVALUATE() and how to use it, generally, so it would just take the cell-side work, creating the lookup table and figuring out what smaller elements you want to use the above thoughts for.

Jeorje
  • 11
  • 1