Database Techniques – Concatenate

Posted on 19. Jul, 2011 by in Excel, Resources

con·cat·e·nate | a scary looking word created to make life simple.

Simply, string concatenation joins two character strings together. OK, realistically simple example, if your excel database contains an address separated by columns and you’d like to mass mail each address without physically retyping the addresses into a single column, the excel tool concatenate will combine the columns for you. Ta Dah!

As concatenate was one of our more popular tweets, we’ve decided to create a simple excel practice and tool to help you master the life-saving device in seconds. Yes, seconds, that’s all it takes. And yes, life-saving, since we’ve seen the lists you commercial real estate cold-callers carry, and if you’re not formatted properly, we know how long you can spend performing mindless tasks like retyping. As we always say, if you think there’s an easier way something can be done in Excel, there probably is! (more…)

The “other” IF Statements

Posted on 06. Jul, 2011 by in Excel, Resources

 

In the past, we taught you how to become an Excel Bully, and while we don’t really condone stealing other kids’ lunch money, we do believe it’s an important first step. So if you haven’t yet read the beginner’s guide to Excel if statements, click here!

Excel has supplied us with a few close cousins to the IF statement, I call them the other IFs, they might be lesser known but they pack a valuable punch (and being a Bully, knowing how to punch is a good thing). They offer first, significant flexibility, and second, automation.

Excel Tip of the Day: If you’re doing anything manually these days, you’re bordering on heresy, so if you ever find yourself saying I wish Excel did this or that… It does, you just don’t know it yet!

So we’ve learned Logic plays a huge role in how an IF statement works (remember from beginning If statements: logic means a declarative sentence that is either true or false) but Excel likes to change its language when we advance even a little. When working with the other IFs we need to remember that Excel only relies on true statements (now the picture above makes sense!).

These are the other IFs and they carry out instructions only if a condition is met.

  • =SUMIF:  Adds the cells specified by a given condition or criterion
  • =SUMIFS:  Adds the cells specified by a given set of conditions or criteria
  • =COUNTIF:  Counts the number of cells within a range that meet the given condition
  • =COUNTIFS:  Counts the cells specified by a given set of conditions or criteria
  • =AVERAGEIF:  Finds the average for the cells specified by a given condition or criterion
  • =AVERAGEIFS:  Finds the average for the cells specified by a given set of conditions or criteria

Here is a brief example: (more…)