From the course: Excel: Advanced Formulas and Functions

Formula vs. helper columns

From the course: Excel: Advanced Formulas and Functions

Start my 1-month free trial

Formula vs. helper columns

“

- [Instructor] When I was thinking about this course, Advanced Functions and Formulas, I figured I didn't want to just show you exotic, obscure functions or wild formulas. I also wanted to give you some strategy. In this video we're going to talk about writing formulas versus helper columns, and you need to be able to decide based on your skill, your comfort, the situation, all of those will dictate whether you use helper columns or formulas. In this situation, with all of these meeting spaces, from 37th Avenue down to The Triton, we want to calculate the square meters for all venues that have both Wi-Fi and a projector. For other venues we want those to stay blank. So, City Center Q, it doesn't have a projector so we do not want square meters filled in for that venue. All right, let's go to the Helper Columns tab. So, we had used helper columns for intermediate steps towards our goals. Let's get started here. Go here, say Wi-Fi or a projector equals COUNTA, open parenthesis, I want to count here, all right? The Wi-Fi and a projector, we do not care about 24-hour access. Enter, so we know City Center Q has two, Licorice Mill has none. Next, over here, let's say feet one, okay, equals TEXTBEFORE, what text? This text, comma, what's the delimiter? TEXTBEFORE what? Before, in double quotes we have to put the x, and then we can close the parentheses and Enter. So, now we have, we're at 50 feet for Lees Courtyard, but also we have to make sure that this is math, okay? Because we're going to multiply it. So, let's do feet two, okay, equals TEXTAFTER. Okay, which text? This text, comma delimiter is the x, put that in double quotes and Enter. Now, square feet equals the 31 times the 12, Enter. All right, we're getting there. So, now we have to do the square meters, go back, equals CONVERT the square feet comma, so I'm going to drag this down. Okay, so there is square foot, double-click. That's the from unit, comma to, we want square meter, double-click and Enter. Now we have our square meters, and that's what we wanted. We've got one more thing to do. We want blanks where there is either no Wi-Fi or no projector. Now we have to go back into CONVERT, equals if, open parenthesis, this value is less than two then double quote, double quote for stay blank, comma go ahead and do that conversion calculation. Go to the end of the formula, closed parenthesis, Enter, and then let's verify. Our calculations are in the right place, our blanks are all in the right places. All right, let's look at a formula way to do this, equals COUNTA open parenthesis, highlight here, closed parenthesis then two. Now I'm going to go back to that first cell, go to the formula, equals if this COUNTA is less than two then stay blank. Otherwise, now it's going to get hairy (laughs). All right, take a deep breath. Text, let's do TEXTAFTER, which text? This text, comma double quote x double quote, closed parenthesis for the TEXTAFTER times TEXTBEFORE. Double-click there, same text comma the delimiter double quote x double quote, closed parenthesis. And I am going to close the if statement and Enter. Oh, (vocalizing) look at that. But we aren't done because this is the square feet not the meters. All right, let's compare. Okay, we need 34.55993. Okay, now we have to be careful about how we go back into this formula. I'm going to go here before the TEXTAFTER, and I'm going to go CONVERT open parenthesis. CONVERT what number? That is the number, going to go all the way to the end comma CONVERT from square feet, okay, double-click, comma CONVERT it to square meters, double-click, closed parenthesis and Enter. We got it. We got it, got it, got it. So, now we can compare. The big formula we don't have a lot of extra columns, but if something is off we need to troubleshoot this. Somebody else needs to troubleshoot it, this long formula can be intimidating. Now we go over here. Now, yes, we have all these extra helper columns, but we can see what's going on. We can see that this 372 came from the 12 and the 31, we can see the 50 times 40 1/2 made up to 2,025, and we see that this is blank. We can see all of the individual pieces, makes it easy to troubleshoot. But maybe you're good enough to where you don't need all of these. Maybe you can build formulas like this, and you'll never pass it off to anybody who will need to modify this or troubleshoot it. It depends on your situation, and that's how you decide if you want to make really big formulas or several helper columns. It's up to you.

Contents