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
- Users can adjust how columns are calculated
- Users can adjust how columns are calculated without directly editing every cell in the column or knowing excel syntax
- When needed, users can easily edit cells using Excel syntax (don't need to parse multiple levels of IF statements).
Potential Methods
- 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.
- 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.