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, and in this example, we look at using the left and right text functions to add this third input.

In our sales model, we have three variables that we want to flex to see how profit is affected. We have price, cost, and demand. You'll see the cell in C6 has both the cost and demand input separated by forward slash. We will use this cell as one of our inputs for the data table, but first we need to split the cell out into two cells so that we can complete our sales model and calculate the profit. So let's pop into C7 and do the demand calculation first. So this is gonna be a right function 'cause I want the demand, which is on the right-hand side of the cell in C6. So it's gonna be =right, open bracket, and it's the right-hand side of C6. Then we're gonna have a comma, and then we need the number of characters that we want. Now, to calculate the number of characters we want, we want to work out the total number of characters in the whole string, which is the length, or len, function.

And then I'm gonna subtract where we find that forward slash character, and we need to find that somewhere within C6.

Close that one. We also need to subtract one from that answer, because we're gonna have one too many items. So I'm just gonna close the bracket, then, at the end, and that should give us our 29,000. Now, I've noticed that this has appeared over on the left-hand side of the cell, indicating that this is still a bit of, Excel's thinking this is still a text item. So what I'm gonna do is I'm gonna wrap this whole function that we've just created in the value function, and then gonna convert that to a numeric item. So the value function wrapped around the whole thing just to do the conversion. We're gonna do the same thing for our unit cost, except this this time, obviously, we're gonna use a left function. So we're gonna do =left, open bracket, and we want the left-hand side of our C6 cell. And then for the number of characters, we just need to work out where that forward slash is, so find, and in quotes, forward slash, within C6.

And then we just need minus two characters, because we don't want the slash or the space. Close the bracket. Hopefully we should have 0.45, which is good. And we have the same thing as before. Excel's recognizing this as a piece of text, so we're gonna wrap the whole thing in our value function as well.

Great. We can then go and complete the rest of our little model. So our revenue is gonna be =2, the price multiplied by our demand.

Let's put the formula in there for you. Then our variable costs is gonna be the unit cost multiplied by, oops, multiplied by our demand.

Put the formula in there for you. And then we can work on our profit, which is gonna be our total revenue and take away our fixed costs and our variable costs. So that's gonna give us 57,950. So there's our little model completed with our left and right functions to split out two of our inputs. The next thing to talk about is our data table itself. And you can see I've prepped some of this already. We've got the price variable down the rows in the left-hand column here of the table. We still need to do what we do normally for a data table, which is go and link in the top left corner of our data table to our profit cell. So I'm gonna go and do that. So I'm just gonna do =C12.

And then for our labels across the top, our unit cost and demand, you can see I've just used the same format that we've used in C6 where we've got the separator, the forward slash, separating our unit cost and our demand. And I've just repeated that across our data table in our header row.

Once we've done this, all I need to do is select all of my data table. So I can just select all of the columns and rows and then run the data table. And then our row input cell is gonna be the row changes across the top, you'll remember. So this is our cell in C6, and our column input cell, the one that changes down the column, is our price, so that is our cell in C5. Click okay, and you should have a nice completed three input data table.