Counting Empty: Using COUNTA and COUNTBLANK in Excel

[EXCERPT ONLY, CLICK ABOVE TITLE FOR FULL POST WITH IMAGES]

Rule number 542 of spreadsheet law: blanks are evil. 

They cause all sorts issues!  They interrupt quick-key selections of columns and rows, they cause errors when referenced by certain formulas, and they break most VBA macros unless explicitly handled.  It's in our best interest to find blanks and replace them with data wherever possible.

How do we find them?  There are many ways, among them COUNTA and COUNTBLANK.

Let's consider four columns of data.

The COUNTA function takes a range of cells and returns the number of non-blank cells in that range. 

The COUNTBLANK formula does the inverse:  it takes a range of cells and returns the number of blank cells.

Pay careful attention below to what Excel considers "blank".  A cell is NOT blank if it has errors or spaces.  The latter is particularly insidious, because if there is even one space in a cell it will be counted as non-blank. 

If you think you may have these in your spreadsheet, better to use a combinations of COUNTIF functions that looks for all the blank-like possibilities.

Need additional help with COUNTA, COUNTIF, or other Excel issues?  Reach out today via live chat, email, or phone and our experts will get you the help you need in minutes!

Being Choosy: Using the COUNTIF Function in Excel

[EXCERPT ONLY, CLICK ABOVE TITLE FOR FULL POST WITH IMAGES]

As we discussed in yesterday's post, Excel provides a number of helpful ways to count things because it's something spreadsheets are deservedly famous for:  stacking big hunks of data in an orderly way so that humans can make sense of it!

COUNTIF has a number of advantages over COUNT, especially if the criteria we are trying to count is a bit more nuanced than just looking for any ol' number.

Let's take the same four columns of data as we worked with yesterday.

But instead of using COUNT to total up the number of values, we'll use COUNTIF with the condition that we're only counting cells whose values are greater than zero.

What makes COUNTIF so powerful is the fact that it has an embedded error catching function--when it encounters uncountable cell values (those that don't match the condition or are of an entirely different data type), it doesn't choke!  COUNTIF perseveres through such difficulties and faithfully returns the number of successful matches.

Need additional help with COUNT, COUNTIF, or other Excel formulas?  Reach out today via live chat, email, or phone and our friendly experts will get your issue solved pronto!

How Many? Using the COUNT Formula in Excel

[EXCERPT ONLY, CLICK ABOVE TITLE FOR FULL POST WITH IMAGES]

Microsoft Excel owes much of its popularity to its helpfulness with HOW questions.  How much...?  How long...?  How big...?

Perhaps the most common of these is How many...?  This week we'll be looking at the various formulas that help us answer this question.  Let's start with the most basic formula in this family:  the COUNT function.

The COUNT function takes a range (or multiple ranges!) of cells and returns the count of cells with number values in the group specified.

As you'll notice below, this particular COUNT formula does not take into account cells that are blank, have errors, or contain text.  Only numerical values get counted.

Do you need assistance with COUNT or other features of Excel?  Contact us today via live chat, email, or phone and we'll get your issue solved in minutes.  We can also do that Excel project for you if you like... let us know!

Close Enough: Using the QUOTIENT Formula in Excel

[EXCERPT ONLY, CLICK ABOVE TITLE FOR FULL POST WITH IMAGES]

We live in a precise world where fractions of fractions make a difference... but refreshingly, sometimes all you need is to nestle in close in order to have a "good enough" answer.

If one such occasion means dividing one number into another, the QUOTIENT formula is your new best friend!  Here's how it works.

Suppose you have two columns of numbers, one (the denominator) that has to go into another (the numerator).

Sure, you could start out super precise and divide it and use formatting tricks to get rid of the decimals...

...but why not use a function whose sole purpose is to return the integer value of a division problem?  It's a bit like we learned in grade school:  there is the quotient and the remainder.  Here, we're just after the quotient, and Excel provides a QUOTIENT function.

See, integers only!

Need additional help with Excel math operators, QUOTIENT, or other Excel issues?  Don't hesitate to reach out today via live chat, email, or phone and we'll get your issue solved in mere minutes!

Quick Multiplying: Using PRODUCT to Multiply in Excel

[EXCERPT ONLY, CLICK ABOVE TITLE FOR FULL POST WITH IMAGES]

We're all creatures of habit, aren't we?  We learn to do things a certain way, and as long as it works, we'll cling to our tried-and-true method... even if it takes way longer than a better alternative!

The purpose of this post is to offer a helpful, speedy way to multiply, but it may be uncomfortable at first if you've heretofore been used to using the star * operator to multiply in Excel.

So let's say we have two or more numbers we need to multiply.

Of course we have at our disposal the * operator such that we can type =cell1*cell2

But the more numbers we have to multiply, the more sense it makes to use the PRODUCT formula instead.  You'll notice below we don't even need to specify individual cells, we can just drag our mouse lazily across a whole range and be done with it!

Need additional help with *, PRODUCT, multiplying, or other issues in Excel?  Please reach out today, we'd love the opportunity to work with you via live chat, email, or phone!

Three Functions in One: Using SUMIFS in Excel

[EXCERPT ONLY, CLICK TITLE FOR FULL POST WITH IMAGES]

With so many functions on offer in Excel, it can be quite a challenge knowing the easiest way to do something.  SUMIFS happens to be one of those formulas that does the work of three formulas--SUM, IF, and AND--all at once!  Here's how it works.

Suppose we had important information in two separate columns, like a first and last name, and a value in a third column that we needed to SUM depending on the full name.

Unlike SUMIF, the first term in SUMIFS is the range of values we may or may not be summing--that depends on which of our criteria matches.

After that first term, each pair of terms thereafter specified a cell range and the criterion that those cells need to meet.  The criteria check supplies the IF, and the fact that we can do pair after pair after pair of checks works much like an AND function.  Only if all of the criteria are true of a row does the value get included in the sum.  In this example, we're checking for a first name of "Harry" and a last name of "Smith."

Only two rows meet these criteria, and the sum of values in these rows is 11.

Need additional help with SUMIFS or other Excel formulas?  Reach out today via live chat, email, or phone for live support getting your issue fixed in mere minutes!

Row Rodeo: Using SUMIF in Excel

[EXCERPT ONLY, CLICK TITLE FOR FULL POST WITH IMAGES]

It's sometimes assumed that everyone knows this about spreadsheets, which probably means that someone ought to come out and state it a bit more clearly:  a row of data is a row for a reason.  In other words, something about One Row is (or ought to be) meaningful in every spreadsheet. 

Maybe in this spreadsheet a row signifies a person in my class; maybe in another, each row is a product kept in inventory; or in another, a specific day of the week.  The point is, spreadsheets are lists, and lists don't make sense unless each row is a record unto itself.

This concept is especially important when considering formulas such as SUMIF that depend on the value in one column in order to do things in another column on the same rows.  Confused?  Let's walk through an example.

Let's say we have a simple data set with names and corresponding values.

Just below this table, we can list the unique names and a place to calculate the aggregate sum corresponding to each name.  How do we do this?  With a SUMIF formula that references the search range (the names), the search criterion (one unique name), and the sum range (the values).

What we get as a result is a cascade-ready formula that would work no matter how big the data set or the number of names.

Need additional help with SUMIF or other Excel formulas?  Reach out now via live chat, email, or phone for help working through that Excel issue.

Predictably Random: Using RAND and RANDBETWEEN in Excel

[EXCERPT ONLY, CLICK TITLE FOR FULL POST WITH IMAGES]

Traffic lights, plane and train schedules, TV guides... Life's full of predictability and cycles.  Ever feel like shaking things up?  Ironically, our beloved spreadsheet tool famous for introducing the thinking world to structure gives us a way to break the mold: the RAND and RANDBETWEEN functions!

If you need any random number between 0 and 1, you'll want to use

=RAND()

If instead you want a random integer that falls between two numbers, you'll use something like

=RANDBETWEEN(1,10)

Be aware that these randoms aren't just random once... they re-randomize (?) the values every time the sheet recalculates (typically any time you change something).  So if you want to generate random numbers and then keep them static, copy the cells and then Paste As Values.

Need additional help with RAND, RANDBETWEEN, or other Excel formulas?  Reach out today via live chat, email, or phone and we'll get your issue solved in minutes!

INT Hints: How to Use INT in Excel

[EXCERPT ONLY, CLICK TITLE FOR FULL POST WITH IMAGES]

Sometimes precision is important... but what if you just need to keep it simple?  ROUND, ROUNDUP, and ROUNDDOWN are great formulas, but if you just need to strip off the decimals of a number and get the integer left over, INT is the answer!

Suppose we have a stack of decimal numbers.

Unlike ROUNDDOWN, which requires a precision number of decimal places, INT only takes one input.

Need additional help with the ROUND functions or INT?  Reach out today via live chat, email, or phone and we'll get your issue solved in minutes!

The Many Ways To Add In Excel

[EXCERPT ONLY, CLICK ABOVE TITLE FOR FULL POST WITH IMAGES]

For many long-time Excel users, the SUM formula holds a special place in their hearts as the first formula they ever learned.  For those just starting out, it can be the perfect stepping stone to discovering a whole new world of Excel that goes way beyond list-making.

So today, let's learn a bit about how we might add in Excel!  The first way is simply to type an equals sign and use simple addition with the "+" operator:

Another way is to use the SUM formula, and choose each term individually.  We can do this by selecting each cell with our mouse, or typing out the different cell references separated by commas.

The last way, my personal favorite, is to select a range of cells.  We can do this with the mouse by holding down the left mouse button and dragging the cursor across a range, or by typing two cell references separated by a ":".  The colon simply means "everything between these two cells, and including these two cells."

Need additional help with addition, SUM, or other Excel elements?  Spreadsheet Sherpa is waiting to hear from you via live chat, email, or phone to fix your issue in minutes!

Around and Around: ROUND Options in Excel

[EXCERPT ONLY, CLICK TITLE FOR FULL POST WITH IMAGES]

Sometimes we like decimals, sometimes we just want the rounded integer, and sometimes we want decimals rounded down or rounded up.  Fickle bunch, aren't we?

Gladly, Excel makes it easy to turn decimals into whatever rounded integers we need with the ROUND, ROUNDUP, and ROUNDDOWN formulas.  The only thing tricky thing to be aware of is that the formula asks for the number of decimal places we want included.  If we want strictly integers, we provide zero for this term.

=ROUND(N7,0)

ROUNDUP and ROUNDDOWN take the same inputs, but give a different result that either strips the decimal (ROUNDDOWN) or rounds up to the next highest integer (ROUNDUP).

Need additional help with ROUND, ROUNDUP, or ROUNDDOWN?  Reach out today via live chat, email, or phone and our team would be happy to get your issue solved in minute or do your whole project for you!

Getting EVEN... or ODD... in Excel!

[EXCERPT ONLY, CLICK TITLE FOR FULL POST WITH IMAGES]

Now that we've been writing about IF formulas for a couple of weeks, you're ready to know a deep and profound truth:  an IF formula is kind of like making your own formula.  When there's no function that already does what you want it to do, your only recourse is to use an IF formula!

For example, let's say that you wanted a formula that forces a number to be even.  In English,

"If the number is already even, leave it unchanged; if not, round it up to the next even number."

Or the same thing, but odd:

"If the number is already odd, leave it unchanged; otherwise, round it up to the next odd number."

These are simply expressed in English, but the IF statement's condition gets a bit wonky looking for remainders and things.  It's doable, but wouldn't you know, Excel provides EVEN and ODD formulas that do exactly what we're describing!

No IF function required!

Learn the lesson well: before you launch into creating a big, nasty IF statement, take two minutes and contact Spreadsheet Sherpa to see if there's a prefab formula that already exists to do what you want!

Staying Positive: Using the ABS Formula in Excel

[EXCERPT ONLY, CLICK TITLE FOR FULL POST WITH IMAGES]

Negative numbers are important and all, but let's face it... sometimes we just need to keep things positive.  Am I right?

So given a list of numbers, is there a formula that gives the positive version of it?  Why yes, the ABS function!

Sadly, ABS won't reach through your computer and give you a six pack.  That's the SIXPACK function... kidding.  Let's see how ABS works, pretty simple:

=ABS(number,cell reference, or embedded formula)

Notice, however, that ABS doesn't just skip over text--if it encounters a non-number, ABS spins off an error.  So if there's a chance you've got dirty data, may want to put some error checking in there (e.g., "=IF(ISERROR(ABS(")

Need additional help with ABS, ISERROR, or other Excel elements?  Reach out today via live chat, email, or phone and we'll get your question answered in minutes or even do that project for you!

Excel is for the Birds! Nested IF Formulas

[EXCERPT ONLY, CLICK TITLE FOR FULL POST WITH IMAGES]

Use a GPS lately?  Common as it is, it's actually a technological miracle made possible by tons and tons of IF statements.  "If he turns off the route, recalculate..."  "If she slows down, reassess the arrival time..." And on and on!

Somewhat similar is Excel's ability to handle what are called nested IF statements.

Let's say we had three letters of the alphabet--A, B, and C--and we needed to determine the extent to which they were in order.  One in order?  Two?  All three?

In English, we might say it like this:

"If A is in the first position, then look at the second position.  If B is in the second position, then look in the third position.  If C is in the third position, then return the phrase 'IN ORDER.'"

If any of these conditions fail along the way, we can return a text string accordingly.

So here we go!  First we have our strings.

We use a MID formula to break out the first, second, and third character.

Then we use our nested IF formulas, one inside of the other.  Rather than "nesting," some prefer to see them as links in a chain or branches of a decision tree.  Whatever works for you!

Need additional help with IF, nested IFs, MID, or other Excel elements?  Reach out today via live chat, email, or phone and we'll get you fixed up in minutes!

Text Detective: Fixing Text in Excel with IF and FIND

[CLICK ABOVE HEADER FOR FULL POST WITH IMAGES]

A good IF statement is like a good hound dog--just give him a sniff o' what he needs to find and turn him loose to find the trail!

Today's topic is something I use literally every day:  IF statements whose behavior is driven by looking for certain text strings.  There are COUNTLESS uses of this thing, but a very common one is cleaning long lists of hyperlinks, some of which have the "http://" and some don't.

In English first, we want our IF statement to do the following:

"If I can't find 'http://' in the string, concatenate 'http://' with the string; otherwise, just leave the text string unchanged"

Excel-speak for "can't find" is a combination of two formulas:  ISERROR and FIND.

ISERROR(FIND("http://",N7))

When the FIND function doesn't find it's target, it generates an error--so putting that inside an ISERROR gives us the TRUE/FALSE we need to power our IF statement.

Need additional help with IF, FIND, ISERROR, CONCATENATE, or other Excel elements?  Reach out today via live chat, email, or phone and we'll get your issue fixed in minutes?

I Am Excel User, Hear Me OR!

[EXCERPT ONLY, CLICK ABOVE HEADER FOR FULL POST WITH IMAGES]

Meatloaf was on to something... "Now don't be sad... 'cause two outta three ain't bad!"

Sometimes when we're using conditionals in Excel, we don't need everything to come up roses; we just need ONE of our search terms to be TRUE and we're in business.

That's what OR is for!

Let's take the same two columns of numbers from yesterday.

But instead of an AND function, which returns TRUE only if every condition inside of it is TRUE, let's use an OR function.  In English,

"If either of the two numbers is greater than one, return 'AT LEAST ONE > 1'; otherwise, return 'ALL LESS THAN 1.'"

Need additional help with OR, IF, or other Excel elements?  Don't hesitate to reach out via live chat, email, or phone with your question and we'll answer in minutes!

AND Candy! Using IF With AND In Excel

[EXCERPT ONLY, CLICK ABOVE HEADER FOR FULL POST WITH IMAGES]

Here at Spreadsheet Sherpa, we get certain questions all the time.  One of them is, what do I do if I need to check whether two or more things are true in an IF formula?

This calls for an AND formula!

Normally, an IF statement checks just one condition:

=IF(N7>1,"BIG NUMBER","LITTLE NUMBER")

But if we want the conditional to check the truth of two or more terms, we can consider the following example with two columns of numbers.

As always, we express what we want to do in English first:  "If BOTH N7 and O7 are greater than 1, return the text string 'BOTH GREATER'; otherwise, return the text string 'BOTH NOT GREATER.'"

Then we type our formula!

=IF(AND(N7>1O7>1),"BOTH GREATER","BOTH NOT GREATER")

Remember, AND is only for situations when every condition needs to be TRUE.  If only one condition needs to be TRUE, we need the OR formula.  More on that tomorrow!

Need additional help in AND, IF, or other Excel elements?  Reach out today via live chat, email, or phone!

Data Smell Dirty? More Tips On ISERROR With IF In Excel

[EXCERPT ONLY, CLICK ABOVE HEADER FOR FULL POST WITH IMAGES]

If you're like me, you have to deal with spreadsheets all the time that...

  1. Someone else created,
  2. Has dirty, unorganized, mixed-up data all over the place, and
  3. You have no other data source to get what you need.

What to do?  Here's another instance where using ISERROR with IF can help you "flag" trouble spots for data cleaning.

Let's take the same data set as yesterday, two columns that we want to divide.  In this case, however, there are some pesky text cells mixed in that are cramping our style.

We can "flag" those rows for cleaning with the formula,

=IF(ISERROR(N7/O7),"NEEDS CLEANING",N7/O7)

Now you can sort by the third row and have everything needing cleaning in a nice, manageable chunk of data.

Need additional help with ISERROR, IF, or other Excel elements?  Reach out today via live chat, email, or phone!

Error Catching: Using ISERROR With IF In Excel

[EXCERPT ONLY, CLICK ABOVE HEADER FOR FULL POST WITH IMAGES]

The great baseball catcher, coach, and philosopher Yogi Berra once said, "We made too many wrong mistakes."  You can be a mistake catcher too, brave Excel user!

More precisely, you can be an error catcher.  Let's say you wanted to divide the numbers in one column by the numbers in another column.  But look what happens when you divide by zero!  AN ERROR!

We can catch this sort of thing and make the cell return something else with a conditional statement (IF) and the ISERROR function.

=IF(ISERROR(N7/O7),0,N7/O7)

In English, this formula basically says "If dividing N7 by O7 results in an error, just return zero; otherwise, go ahead and divide N7 by O7."

Need additional help with IF, ISERROR, or other Excel elements?  Reach out today by live chat, email, or phone to our friendly team of Sherpas!

The Mighty IF: You Can Learn IFs In Excel!

[EXCERPT ONLY, CLICK ABOVE HEADER FOR FULL POST WITH IMAGES]

We discussed in the last post what it means to first put your conditional into an English sentence and then translate that into an Excel IF formula.  But what if the answer you want returned needs to use other cells' values?

Say, for instance, we had two columns of values.  Our English sentence would go something like,

If both values are positive, then return the sum of the values; otherwise, just return the largest of the two values.

In Excel, this would translate to

=IF(AND(N7>0,O7>0),N7+O7,MAX(N7:O7))

Looks complicated, but take it piece by piece!

both values are positive = AND(N7>0,O7>0)

return the sum of the value = N7+O7

return the largest of the two values = MAX(N7:O7)

Need additional help with IFs, ANDs, MAXs, or other Excel elements?  Reach out today via live chat, email, or phone!