Excel has various functions, including functions to calculate inverse cosine of a given value, to multiply
2 matrices, to estimate the internal rate of return. But, most of us(well, just me then..) use just about
5-6 formulas to do our jobs. And IF formulas are a majority of these, so it doesn’t harm to learn a few
interesting things you can do with just the excel IF functions.
1. Sum alternative rows / columns:
There comes a time when you are slapped with a sheet of data and need to sum every other row in it
(dont ask me why, it happens, for eg. when you copy paste your credit card statement in excel),
Ofcourse, we can always type the sum function with all those arguments, but we would rather chomp
on that donut while excel does the dirty work for us. Thats why it helps to know that you can sum
alternative rows / columns of data using sumif() formula [syntax and examples]. Sumif function
sums a range of data that meets a specific criteria. In our case the criteria will be, “if the data
element is in odd number rows”.
All you need to do is add an additional column at the end of the table and fill it with 1s and 0s. (just
enter 1 and 0 in 2 rows, select both of them and drag till the end of the table). Now we can use this
column to test our condition by writing the sumif function as =sumif(condition range, 1, sum
range) [learn how you can highlight alternative rows / columns in an excel table]
2. Count how many times each item on list A is in List B vice
versa
Often when you are working on data spread across multiple sheets, it helps to know how many times
each item on one list is repeated in another list(s). This can be done easily using a good old countif
function. Excel countif formula counts data that meets a specific criteria. [syntax and
examples]
In the above example, I have used countif function to findout how many customers are there in each
city (where customer data is in List B and city data is in List A). The formula looks
like =countif(condition rage, condition), eg. countif($g$32:$g$47,"chicago") would tell us
how many customers are in Chicago.
3. Quickly Summarize Data with countif / sumif:
Now that we have figured out how to use sumif and countif, you can use these two functions to create
quick summary of your data.
For example, we can findout, average sales per customer per city dividing total sales data per each
city (obtained using a sumif) with total customers in that city (obtained using a countif) as shown
below. This can be a quick way to do pivot analysis of data without actually using excel pivot tables
(very useful if you are allergic to excel pivot tables or not very happy using and constantly updating
them)
4. Lookup second, third … nth occurrence of an item in a list in
excel:
Often we work with data that has lot of duplicates, for eg. customer phone number data that has
grown over a period of time with new numbers added at the bottom of the list. Getting second, third,
fourth or nth occurrence from the list can be tricky, by using a combination of countif and
vlookup we can lookup nth occurrence from a list. [VLOOKUP tutorial and examples]
First in our data list we will insert another column and place the formula =current-
item&countif(range till that point, item), this will append the number of smiths till that point
to the end of smith, thus first smith would become smith1, second smith would become smith2, so
on…
Next, when looking up smith instead of using the initial column of customer data we will use our
modified customer data to fire the lookup, for eg. vlookup("smith3", lookup range, 2,
false) would tell us the 3rd phone number of smith. Note the last argument to the vlookup as
“false”, since our list may not be alphabetically sorted, we have to use “false” to force excel to keep
looking till it finds the 3rd smith row.
5. Reduce your nested if()s to one function
Do you know that you cannot nest if functions beyond 7 levels (in earlier versions of Excel)?
Thankfully, most of us never go beyond 3 or 4 levels. But why write even that many levels when you
can use choose() function, which is like a switch structure in programming. A typical switch function
would look like, =switch(condition, outcome1, outcome2, outcome3...), for eg,
=switch(3,"good","average","poor") would return “poor” when used. The only limitation to the
switch() excel function is that it accepts numbers for testing the condition. But you can overcome this
with some creativity, like I have shown in the below student letter grading example:
How did I convert the letter grade to a numeric in the choose(), well thats for you to figure
out