Excel - Best way to graph the change of a cell as a function of the change of another cell

Question

I have a huge excel workbook that I've been developing to do some cost analysis of an engineering project. Right now, I have a cell where I enter the size of a storage tank, which updates another cell which contains the total savings.

The process of calculating the savings is quite complex. There are 20 sheets in my workbook, one for each year of operation, and each of them references the tank size. Normally I'd try to make two columns and drag the formula for the savings down to get my x and y values, but I don't think I can do this as the savings cell depends on 20 other sheets.

What I want to know is this: What's the best way to graph the relation between size of tank and savings. Ideally I'd like to say "OK, vary the value in this cell between zero and 2 million in increments of 1000, then record the value of the savings cell for each of these increments, then plot," but I can't seem to figure out how to make that happen.

Answer 1

You will want to use one of the "what-if" analysis tools, specifically Data Tables or Scenarios, depending on the number of input variables:

What-if analysis in general:

https://support.office.com/en-us/article/Introduction-to-what-if-analysis-22BFFA5F-E891-4ACC-BF7A-E4645C446FB4

Data Tables are probably your best bet if you have 1 or 2 input variables:

https://support.office.com/en-us/article/calculate-multiple-results-by-using-a-data-table-e95e2487-6ca6-4413-ad12-77542a5ea50b

Answer 2

If you're OK using some VBA, a simple solution might be (ALT+F11 to get in the editor, then insert module)

Sub GenerateData()
    Dim curDataPt As Long, curVal As Long
    Dim rngOut As Range, rngIn As Range, rngData As Range, rngVar As Range

    '*** SET VALUES HERE ***'
    Const maxVal As Long = 2000000
    Const minVal As Long = 0
    Const stepVal As Long = 1000

    Set rngIn = Sheet1.Range("A1")
    Set rngOut = Sheet1.Range("B1")
    Set rngVar = Sheet1.Range("D1")
    Set rngData = Sheet1.Range("E1")
    '************************'
    For curVal = minVal To maxVal Step stepVal
        curDataPt = curVal / stepVal
        rngIn = curVal
        rngVar.Offset(curDataPt) = curVal
        rngData.Offset(curDataPt) = rngOut
    Next curVal
    Sheet1.Names.Add "DataIn", rngVar.Resize(curDataPt + 1)
    Sheet1.Names.Add "DataOut", rngData.Resize(curDataPt + 1)
End Sub

This assumes the size of your storage tank goes in A1 on sheet 1 and the total savings are in B1. Sheet1 in the code is a code name for the sheet object. Look in the project explorer pane in the VB editor window to make sure this is correct.

The way to use this would be to run the VBA (ALT+F8) to generate the data set and use the chart wizard to generate a chart. When selecting the source data on the series tab, however, enter =Sheet1!DataOut for Values and =Sheet1!DataIn for Category (X) axis labels.

Now you can play with varying min/max/step and the chart will update the data series automatically.