Section 3: Composite models—Using different formulas for different parts of the graph
Compound models add two or more modeling functions together. The other main way in which models are combined is to “compose” a new model by using different modeling formulas depending on what the input values are. The graphs of such composite models will usually have a sharp break, in value or slope or both, at each point in the input range at which there is a switch from one function to another.
Income taxes due are described by a composite model with a change in slope for each tax bracket. In this case each piece of the model is a straight line segment starting at the end of the previous one.
Each segment of a composite model can be built of any kind of modeling formula. The model output can have abrupt jumps in value, although it is more usual for changes at the transition values to be in the slope, since few processes have big output changes for small input changes. Sometimes the position of the transition between formulas is a parameter of the model, so that the fitting process lets the data tell you where the transition is. In the common situation where the transition is at the intersection of two fitted formulas, the transition can usually be deduced algebraically by setting the two formulas equal to each other (using the fitted parameters as coefficients) and solving for the x and y of the crossing point.
Two main approaches are used to “composing” a composite model. The simplest approach is to use each input value to evaluate two or more formulas, then pick the largest of the results to use as the output value. The effect is to make a model whose graph follows the top of the graphs of each of the component formulas. This can be implemented in a spreadsheet with the MAX function, whose value is equal to the largest of its arguments (e.g., the value of “=MAX(14,22,30,5)” is 22).
The graph and table below show the output of a model formula that uses two linear formulas (y=3x+23 and y=7x+2) as arguments to the MAX function. The solid circles are the output of the composite model that results from the MAX function, while the empty triangles and squares show the portions of the two linear formulas that are not used because the other formula is larger at that x value.

Example 5: A cylindrical water tank drains through two outlets, one near the bottom of the tank and the other near the middle. Each outlet pumps at a steady (but different) rate until the water drops below the opening to its outlet. The dataset to the right shows the water depth measured in feet at oneminute intervals during drainage. Based on this data, find the answer to these questions: [a] What percentage of the total pumping capacity is provided by the outlet at the bottom of the tank? [b] What is the depth of the tank at its upper outlet?


Solution:
Steady drainage of a cylindrical tank is a linear process, so it should work to model this situation with two straight lines – one to match the part of the process where the tank is being emptied through both outputs (the left part of the data graph), and the other line for the part of the process where the tank is emptying only through the outlet at the bottom (the right part of the graph). As the model to fit, we will use the MAX function with two linear formulas.
This model will have four parameters: intercept1, slope1, intercept2, and slope2; if cells G3:G6 are used, the formula in C3 should be “=MAX($G$3+$G$4*A3,$G$5+$G$6*A3)” (this should be spread down beside the data as usual).
Use a graph of the data to choose and check initial values for the parameters. The intercept for the first segment is near 50, and its slope is roughly –10; the second segment has an intercept of roughly 30 (extend the points after the bend back to the yaxis), and the second slope is less steep, roughly –3 or so.
[a] Since pumping capacity is proportional to the slope of the lines, the percentage requested in the problem can be computed from the ratio of slope2, which reflects the activity of the bottom pump, to slope1, which reflects the activity of both pumps together. This ratio is 0.28797, so the bottom pump is 28.8% of total capacity.
[b] To determine the height of the higher outlet, we need to know the location of the point at which the two lines cross. This can be done with algebra by solving the equation that results from equating the two linear formulas to each other, using the bestfit numerical coefficients that were found with Solver.
[latex]\begin{align}&\text{At the crossing point,}{{y}_{cross}}=53.278.23{{x}_{cross}}\text{ and }{{y}_{cross}}=33.462.37{{x}_{cross}}\\&\text{Therefore: }53.278.23{{x}_{cross}}=33.462.37{{x}_{cross}}\\&\text{}8.23{{x}_{cross}}+2.37{{x}_{cross}}=33.4653.27\\&\text{}5.86{{x}_{cross}}=19.81\\&\text{}{{x}_{cross}}=3.3805\text{ minutes}\\&\text{So}{{y}_{cross}}=53.278.23{{x}_{cross}}=53.278.23\cdot3.3805=53.2727.82=25.45\text{ feet}\\\end{align}[/latex]
Bestfit Solver results
53.27  Intercept 1 
8.23  Slope 1 
33.46  Intercept 2 
2.37  Slope 2 
The same approach can be used with the MIN function, which evaluates to the lowest value among its arguments (e.g., the value of “=MIN(14,22,30,5)” is –30), but in this case the graph of the resulting model follows the bottom of the graphs of the component formulas, rather than the top.
The other main approach to creating a composite function is to explicitly test the input x value and select between two different formulas based on whether the test is true or false. This is done with the IF function, which uses the format IF(test_expression, formula_if_true, formula_if_false). For example, the formula “=IF(A3<6,4*A3,A3^2)” will use the 4*A3 formula (evaluating to 20) if A3=5, but will use the A3^2 formula (evaluating to 49) if A3=7. This approach is most useful when the test expression uses a model parameter that is also used in one or both of the formulas, since otherwise it is simpler to simply divide the data into several sections based on x values and to separately model each section.