could you let me know if what I'm trying to do here is achievable somehow? I have a feeling that INDIRECT could come in handy, but my mind hasn't been able to come up with how that would look. My goal is similar in concept to a What-If Analysis, but instead of a table, it would be using a single cell formula referencing a master formula and some inputs. What-If doesn't work here, because the inputs are not neatly organised in my analysis sheet.

Here's the story:

I am creating a spreadsheet ("AnalysisSheet") I use for predicting and analysing results during beer brewing.

I take many measurements throughout the process for quality control, and very often the same formula will be applied to the multiple raw data points at various steps for analysis (e.g. conversion of hot liquid volume to cold liquid volume with simple thermal expansion approximation).

These formulas can be complex and therefore messy to repeat each time, therefore I would like to create a separate sheet ("FormulaSheet") that has the "master" formulas neatly laid out (along with dummy inputs), so that I can simply reference those master formulas from the "AnalysisSheet" along with input values to be substituted into the dummy inputs.

Here's an example of a simple formula:

In "FormulaSheet":

A1: 20 [Volume dummy input value]

A2: 80 [Temperature dummy input value]

A3: =A2*(1-0.04*(A3-20)/80) ["Master" formula]

Now in "AnalysisSheet":

F7: 90 [Temperature reading]

F11: 40 [Volume reading]

F13: = Calculation as FormulaSheet!A3, but replacing its input reference FormulaSheet!A1 with AnalysisSheet!F11, and FormulaSheet!A2 with AnalysisSheet!F7

My question is basically:

What would need to be inserted into F13 for it to perform what I describe?

2 Answers

I'm not sure you can do what you're describing, but I did see a very good page on defining your own functions:

It does require you to write the function in VBA, but it's not as hard as it sounds.

1. Open Developer Tools. Usually you need to go into Excel Preferences > View > Show Developer tab.
2. Got to the newly-displayed Developer tab and click Visual Basic.
3. In the VB Editor window that opened, select Insert > Module.
4. Enter your function(s)

Eg:

Public Function HotToColdVolume(temperature As Integer, volume As Integer) HotToColdVolume = volume * (1 - 0.04 * (temperature - 20) / 80)
End Function

Note You will need to save your spreadsheet as a macro-enabled workbook (.xlsm).

Now in your spreadsheet you can simply call:

=HotToColdVolume(80,20)

Ok this way doesn't use VBA, but it's arguably worse. Apparently Excel had these "Excel 4.0" functions that you could use in defined names. They do what you want, but they still need to be saved in a .xlsm file:

Hack level 1

There is a function (?) called EVALUATE (I can't find any official documentation on this, but this page explains it) which can evaluate text as a function. But you can't type it directly into a cell, it needs to "run" from a named range:

Of course the problem is if you try to evaluate the formula A2*(1-0.04*(A3-20)/80) you don't get to specify your own inputs.

Hack level 2

In your master formula you can specify an OFFSET, but you need a reference cell. You can't just reference A1 for the same reasons as before. You need a way to reference "this" cell. Enter another genius hack I found here. Select cell A1, create a named range (eg THIS_CELL), enter the formula =!A1 (! just means "this sheet").

Now if you want to refer to the cell immediately to the right (1 col across), you use:

OFFSET(THIS_CELL,0,1)

So on your master formula page you put formulas in column A, and arguments in columns B, C, D, etc. And you get massively ugly formula like this:

=OFFSET(THIS_CELL,0,1)*(1-0.04*(OFFSET(THIS_CELL,0,2)-20)/80)

Combine the hacks

Now you just need to create a named range that EVALUATEs the FORMULATEXT of the fancy formula with in-built offsets:

=EVALUATE(FORMULATEXT(MasterFormulaTest!$A$2))

Enter the named range into a cell, and you're done!

All with no VBA, the same disadvantages as if you were using VBA, but much more complicated than simply using VBA. Thoroughly worse.