Normally, data tables are restricted to just two input variables, but sometimes we'd like to show a third input variable in our data table. There are a number of ways to do this. In this example, we look at using the offset function to achieve this. In our sales model we have three variables that we want to flex to see how profit is affected: Price, cost, and demand. The first thing we need to do is to build our offset formulas for unit cost and demand in the assumptions area.

Let's go to C16 and do our first offset formula.

So it starts with =offset, open bracket.

And then the first thing it's after is our cell reference. And in this case, we want to choose the cell in C22 which is at the end of our range of different unit costs. So you can see across here we've got our unit costs in this row 22. So I'm just gonna come across to the beginning of that series of values. So this is where I'm gonna offset from. I'm gonna fix that. So F4 to dollarize that address, comma. And it's asking us how many rows would I like to move? And I don't wanna move any rows. It's columns I want to move by so I'm gonna put a zero in there. And then how many columns would I like to move by? Well, I'm gonna go and move by whatever value is in C14 so the offset amount. And again it's always gonna be in the cell so F4 again to fix that.

That's that one done so I'm gonna close the bracket, press Enter.

Let's do the same thing for demand. So, we're gonna just copy and paste the formula. Save ourselves a little bit of time. Yeah, and the only thing I need to do here is F2 here to go and edit the cell. And then instead of C22, we can see that demand is in C21, but other than that, it's exactly the same.

So, by default, when we don't have any offset in the C14 cell, C16 and C17, the unit cost and demand are both zero. But as soon as I start putting a value into my offset formula, you can see it starts pulling in values from our table headings. So if I put one in, we've got 0.35 and 29,000. If I go and put six in, we'll come across and we'll get 0.35 and 30,000. I'm just gonna reset that back to a zero.

Okay. The next thing we can do now is we can go and build our actual calculations. And to do this, we're gonna use an IF statement to work out the price. Obviously by default, we'd like all our calculations to be driven by our assumption cells, which we've got in D15 down to D18. And we're gonna assume that that's true except when we are running our data table which is gonna be using our offset function. So, this is gonna look like this. It's gonna be =if, open a bracket. We're gonna say if C15, which is our price, is empty, so it's got nothing in it like it has at the moment, then I would like to go and have whatever the contents of D15 is, which is our default value. If that isn't true, then I'd like to go and take whatever's in the price cell, close the bracket, press Enter. We should by default get the price of four. But when we run our data table, yeah, that's gonna then change and affect our formula. So that's the first one done. Let's go and do demand. So this one's gonna look like this one. So it's gonna be =if, open bracket. If our offset cell equals zero then what would we like to do? If that's true, then I'm gonna come down and to pick out the demand from C17. So if we're not offsetting, oh, sorry, that's D17, isn't it? We're going... If it's not, if it's zero, we're gonna take the default value. If it isn't, then we're gonna come and choose the demand cell here so D17. Close the bracket, press Enter and we've got our demand value in. Let's do the same thing for unit costs, =if, open a bracket. And we're gonna say if our offset is zero then we'd like to come and choose our standard unit cost. Otherwise, we're gonna take the unit cost from C16.

Press enter. Lovely. Our fixed costs are always gonna be the same. So I'm just gonna link that through to our assumption. So we've just got all the figures in the right place. And now we can do our calculations. So our revenue is gonna be equal to our price multiplied by the demand.

Our variable cost is gonna be equal to our unit cost, multiplied by the demand.

And our profit is gonna be equal to the revenue, subtract our different costs, our fixed costs, and our variable costs, which gives us 57,950.

So I've done my calculations, I've done my count, so I can now link that formula into the top left corner of my data table in the place we normally put it. So I'm just gonna go equals and go and link that to C11. Once we've done this I now need to populate my top header over here with the lookups that I want. Sorry, the offsets that I want to do for each of our different columns. So I'm gonna say that I want to offset by an incrementing amount here.

And I'm just gonna populate these in here. So we've just got offset by one, two, three, four, five, six, seven, eight, nine, or ten items. Now I've got those in, I can go and build my data table. So I'm gonna go and select all of the data, so my headings, and then we're gonna run the data table.

On our row input cell is gonna be the offset. And if you think about it, what's gonna happen here is, each of these offset numbers are gonna get populated into this offset cell, which is then gonna trigger different unit cost and demand which is then gonna impact my profit.

And then for the column input sale that's simply gonna be the price cell in C15. Once I've done that, click Okay.

And we've built our three input data table. One nice feature that you might want to add is if I just select row 23 and then we go to the data tab and group that row, we've now got a convenient way to show and hide that little extra row on our data table. So that's one way of how to do three input data tables.