From the course: Excel: Advanced Formulas and Functions

Writing 3D formulas

From the course: Excel: Advanced Formulas and Functions

Start my 1-month free trial

Writing 3D formulas

“

- [Instructor] 3-D formulas, they can simplify your life, immensely, if everything is set up right, and let's take a look at that. We want to make an overview tab, and that's where we are right now, and we've got these other five tabs. Let's look at 'em. Trip one, we've got gas, that word in B2 and then the amount that was spent on gas in C2, right? Gas, lodging, destination, days. Go to the trip two tab, same order, everything starts in cell B2. Good. Trip four and then trip five. Right, let's go back to the overview. Now it is time. No more waiting. 3-D formula, I want a total of the number of days equals sum open parentheses, go to trip one. I'm going to hold down the shift key and go to five. Okay and I want the number of days. I'm going to select C5, enter. 43 total days. So look at that formula. So we are doing a sum of cell C5, and then we look at trip one colon trip five. So we've got everything in the middle. Let's get the amount that was spent on lodging. Equals sum, open parenthesis, trip one, hold down the shift key, trip five, lodging, enter. Great. Now I'm going to drag this down. We want the amount spent on gas. Gas is in C2. Go back, change this to C2, enter, and then the destinations. Let's look here, destinations, that's C4 on all of the sheets. Drag this down here, change this to C4, enter, and that's not $46. That is an integer. Change this to number, reduce the decimal places. Great. So now we did a sum. How about we get the maximum number of days that was spent on one of these five trips. Equals max, open parentheses, trip one, hold down the shift key, trip five, days, enter. 15 days was the maximum. Let's verify that. Trip five. So there was eight days, four, 15, four, 12. If this had been 29, enter, go back. See, it says 29, but that's not right so I'm going to undo that, go back to 12. Great. So that's how you do a 3D formula. When I wanted these sums, I did not have to go equal sum, open parentheses, and then go to every sheet and select the cell on each sheet. By using a 3D formula, I captured everything. This could have been 20 sheets long. It would've been just as easy.

Contents