Formulas

6 posts

Finding the Largest Negative Number in Excel

If you have a list of descending values and want to find the position of the largest negative number, how would you do it?

First of all, when talking about negative numbers, which number is greater and which is lesser? Is -8 greater than -3, or is it less than -3? The answer is -8 is less than -3. So when finding the largest negative number, the number closer to zero is greater than the number further from zero.

To sum it up, -3 is larger than -8. Keeping this in mind will help when trying to find the largest negative number in a list.

A question to ask is: When would you want to find the position of the largest negative number in a descending list?

When Would You Want to Find the Largest Negative Number?

Imagine that you have a starting balance that is reduced each week, and you want a formula to calculate what week the balance ends up being below zero. This would be the week that has the largest negative number. Now this can change – the starting balance could be different; the amount it is reduced each week could be different.

See the example below:

excel largest negative number

So you want to find the week number for the largest negative number. You can see in the example image that this is Week 17.

But now, how would you use a formula to find it automatically?

What Formula Can Find the Largest Negative Number?

The formula would need to ensure that all variables of are handled. The starting balance could change; the amount the balance is reduced each week could vary; the value that you want returned is to the left of the value you are checking. So a standard VLOOKUP wouldn’t work.

How can this be handled? The formula to calculate would include four separate functions – INDEX, MATCH, LARGE, and COUNTIF. And here it is:

=INDEX(A:A, MATCH(LARGE(B:B,COUNTIF(B:B,”>=0″)+1),B:B,0))

Now that’s a lot of functions to combine, and the formula can be complex at first glance. However, breaking it makes it simpler and easier to understand what’s happening. Lets’s start breaking down the formula.

COUNTIF(B:B,”>=0″)

This function counts the number of numbers in all of column B that are greater than or equal to zero. This seems counter-intuitive since we want the largest number that is less than zero. But hold on…we’ll get to that later.

Using the data above, this function returns the value of 16, since there are 16 numbers in column B that are greater than or equal to zero.

LARGE(B:B,COUNTIF(B:B”>=0″)

This function returns the nth largest number in column B. The nth number that the LARGE function is being told to return is the result of the above COUNT function, which in this example, is 16.

The 16th largest number in column B is 5. Still not the number we are looking for – that comes next.

+1

This takes the number returned from the above COUNTIF function and adds one to it. Why do this?

Remember that the result of the COUNTIF function was 16, and this number was passed to the LARGE function. Without adding 1 to the 16 that is being passed to the LARGE function, the LARGE function would take the list of of descending numbers in column B and find the 16th largest number that is greater than or equal to zero.

That is not what is wanted. What is actually wanted is the first negative number (the greatest negative number).

That is why 1 is added to the 16. Since, in the list of descending numbers, there are 16 numbers greater than or equal to zero, the next largest number would be the largest negative number.

And that is the number that we want! In this example, the 17th largest number is -28.

Now, to get the matching week name. The example shows that the week name is in cell A18 and is Week17. Since the week name is in the column to the left of the searched value in column B, a standard VLOOKUP function won’t work. A combination of INDEX and MATCH will allow the value to the left to be returned.

MATCH(LARGE(B:B,COUNTIF(B:B,”>=0″)+1),B:B,0)

Here the MATCH function takes the result of the functions above, which is -28. It then looks in column B for that number and returns its position. The match type argument is 0. This tells the function that an exact match is wanted. The -28 in column B is in position 18.

Lastly, the resulting 18 is passed to the INDEX function part of the formula.

INDEX(A:A, MATCH(LARGE(B:B,COUNTIF(B:B,”>=0″)+1),B:B,0))

The index function now looks in the 18th cell of column A and returns that value, which is:

Week17

Here you have seen how to use a formula in Excel to find the largest negative number. While this formula uses four different functions, and does seem complex, it is very flexible.

Live Transpose in Excel With an Array Formula

Excel’s transpose feature is great for changing data in a vertical format to a horizontal format, and from a horizontal format to a vertical format. Excel’s transpose, though, creates a static copy of the original data. If the original data changes, the transposed copy does not change.

This may be your desired result, but if you want the transposed copy of the data to update at the same time as the original data changes, there is a way create a transposed copy that changes along with the original data. This will create a “live” transposed copy.

The way to do this is by using an array formula. Check here if you want more information on array formulas. As a quick note, you do need to use CTRL + SHIFT + ENTER when entering an array formula for it to work.

Live Transpose Example

To illustrate how a live transpose in Excel works, take a look at the sample image below. It shows the original table of data that will be the source for the live transpose copy.

excel live transpose source data

Once you decide where you want the live transposed copy of the original data to be placed, you just need to highlight a range of cells that is equal to the transposed version of the original data. Note that it is the opposite size of the original data, since it is a transposed copy.

Therefore, In this example, since the original data is 14 columns wide by 9 rows high,when the data is transposed, its shape will change to be 9 columns wide by 14 columns high.

So you need to select a range that is 9 columns wide by 14 columns high.

highlight a range for live tranpose

Once you have highlighted the range where you want the live transposed copy of the data to be located, start to type the TRANSPOSE formula. You can either click in the formula bar first, or just start typing the TRANSPOSE formula. In the example here, it would be:

=TRANSPOSE(A1:N9)

And when you type CTRL + SHIFT + ENTER after typing it, it turns into this (NOTE the curly braces that were automatically added):

{=TRANSPOSE(A1:N9)}

And this is the resulting view:

a live transpose in Excel

Anywhere you click in the range, the formula in the formula bar does not change. It stays the same.

Now, if you change any values in the original data, the transposed version will automatically update to the new value you have entered.

Summary

Hopefully, this post has shown you how to use an array formula version of the TRANSPOSE function in Excel to create a “live” transposed version of the original data.

Let me know what you think, or if you have any tips or comments about this method.

Here’s a Quick Way to Concatenate Text Strings

When you need to concatenate text strings together in Excel, there are a few ways of doing it. There’s the old standby CONCATENATE function, there’s the ampersand (&) in the formula method, and with Excel 2016 there’s now the CONCAT function and TEXTJOIN function.

Here’s a summary of the ways to concatenate text strings. I’ll use the same text strings for each method to show the difference.

CONCATENATE Function

Excel’s standard CONCATENATE function looks like this:

CONCATENATE(text1, [text2], )

  • text1 = the first string to join. It’s required
  • [text2] = the second string to join to the first. It’s optional and there’s a maximum of 255 strings, or 8,192 characters

Here’s how the formula would look:

=CONCATENATE(“Hello world.”, ” “, “Today is”, ” “, “Monday.”, ” “, “There”, ” “, “are”, ” “, “no”, ” “, “appointments”, ” “, “in your calendar.”)

Here’s the result of the formula:

“Hello world. Today is Monday. There are no appointments in your calendar.”

The Ampersand Method

The ampersand (&) method is what I would use instead of the CONCATENATE function. I found it quicker to type either the couple of strings or cell references with the ampersand (&) between rather than typing out CONCATENATE.

It’s probably just a personal preference though. With the new functions added to Excel 2016, I just may change my method.

Here’s how the formula would look with the ampersand (&) method:

=”Hello world.” & ” ” & “Today is” & ” ” & “Monday.” & ” ” & “There” & ” ” & “are” & ” ” & “no” & ” ” & “appointments” & ” “& “in your calendar.”

Here’s the result of the formula:

“Hello world. Today is Monday. There are no appointments in your calendar.”

So, the same result as the CONCATENATE function, but looking at the two, I actually find this method of joining text harder to read the formula.

CONCAT Function

CONCAT is one of the two new Excel 2016 functions that you’ll see here. It has replaced CONCATENATE, though CONCATENATE is still available for backwards compatibility.

The CONCAT function looks like this:

CONCAT(text1, [text2], )

  • text1 = the first string to join. It’s required
  • [text2] = the second string to join to the first. It’s optional and there’s a maximum of 253 strings, or 32,767 characters

Here’s how the formula would look:

=CONCAT(“Hello world. “, “Today is”, ” “, “Monday.”, ” “, “There”, ” “, “are”, ” “, “no”, ” “, “appointments”, ” “, “in your calendar.”)

Here’s the result of the formula:

Hello world. Today is Monday. There are no appointments in your calendar.”

TEXTJOIN Function

TEXTJOIN is the other new text function added to Excel 2016. Like CONCATENATE and the new CONCAT, it combines text. However, it also includes the ability to add a text delimiter and some logic to the joining of text.

The TEXTJOIN function looks like this:

TEXTJOIN(delimiter, ignore_empty, text1, [text2], )

  • delimiter = a text string in double quotes
  • ignore_empty = TRUE or FALSE. If the function should ignore empty values
  • text1 = the first string to join. It’s required
  • [text2] = the second string to join to the first. It’s optional and there’s a maximum of 252 strings, or 32,767 characters

=TEXTJOIN(“”,TRUE,”Hello world.”, ” “, “Today is”, ” “, “Monday.”, ” “, “There”, ” “, “are”, ” “, “no”, ” “, “appointments”, ” “, “in your calendar.”)

Here’s the result of the formula:

“Hello world. Today is Monday. There are no appointments in your calendar.”

Notice, though, that the same empty characters are between the text. That’s not using the full power of the TEXTJOIN function.

If you remove the space strings between the words, and instead use the TEXTJOIN function’s ability to insert the space strings, the formula would look like this:

=TEXTJOIN(” “,TRUE,”Hello world.”, “Today is”, “Monday.”, “There”, “are”, “no”, “appointments”, “in your calendar.”)

Here’s the result of the formula:

“Hello world. Today is Monday. There are no appointments in your calendar.”

The result is the same, but the formula is now shorter.

This new TEXTJOIN function now allows you flexibility in how to use and manipulate text strings. You can specify what you want between each string, and the formula is shorter, and easier to read.

Conclusion

The new functions of CONCAT and TEXTJOIN that have been added to Excel 2016 have greatly increased the amount of characters that can be concatenated.

As well, the TEXTJOIN function has increased the flexibility available to concatenate text strings in Excel. This should give you more freedom and power to quickly concatenate text strings.

Let me know what you think about the new text functions in Excel 2016.

Excel Filters For Custom Reports

Excel is one of the most common software applications used for reporting on financial data. It’s power, flexibility and ease of use contribute to its use by so many users. Plus, having Excel as the most commonly used spreadsheet software in use on desktop computers around the world also helps make its use so popular.

Since Excel is so popular, it is commonly used for reports – even though it is not designed as a reporting system. It’s a good thing that Excel has the ability to automate so much and has so many built-in features.

The feature that will be shown are the Excel filters, and you will see how to use them to customize a report.

Start with the Report

What will be demonstrated here is how to automate some of the reporting. Let’s assume that you will be reporting monthly on the financial data on sales of fruit at two stores.

Below is the basic report that will be modified. Notice that it shows two stores on the report and the total of the two.

excel-filters-report
Excel Report to Filter

The report shows what the projected forecast was of quantity of fruit sold, and what was actually sold for both of the stores.

Determining the Report Needs

Making the report easier to understand is always good to do.  Therefore, you don’t want it to be cluttered with useless information.

Due to that, for this report, you want to only see:

  • fruit that were projected to sell and that did end up selling
  • fruit that weren’t projected to sell, but did end up selling

You don’t want to see:

  • fruit that weren’t projected to sell and that didn’t end up selling

So, for the West Store, you don’t want to see:

  • dragon fruit
  • strawberries
  • cherries

Also, for the North Store, you don’t want to see:

  • apples
  • oranges
  • dragon fruit
  • lemons
  • bananas
  • watermelon

Also, notice that it looks like the North Store is not doing so well, in comparison to the West Store, either.

Manually Customizing the Report

You could manually remove the lines on the report for those items that haven’t sold, and add in lines for any items that sold since the last time you reported on the data.

However, just imagine the extra work this creates each time you want to report on the data.

And if you want to report on new data more frequently than monthly, the amount of work and possible errors increases.

And besides, you’re using Excel, so you might as well use its built-in features to customize the report.

Seems like an excellent opportunity to use filters.

Unintended Results Using Excel Filters

Excel filters can be used to automatically hide the rows in the report to fit the criteria that you want.

Now you may be thinking that all that needs to be done is add the filters and then filter the two columns “Qty Forecast” and “Qty Sold” to remove any that are zero.

Doing that, though, will not produce the results that you want.

Just take a look at the image below.

excel-filters-report-unintended-filter
Unintended Filter Results

See how the report now doesn’t show the two stores, the sub totals or the grand total. For this example, filtering this way does not give the results wanted.

How do you correct it so that the report filters out the lines you don’t want to see since they are zero, while still showing the lines that you do want to see?

Using Excel Filters on a Formula

You can get the results you want if you add a column to the right side of the report that has a formula that identifies those rows that we want to keep visible, and then apply a filter to that column.

For this example, I left a blank column between the report and the column that I added to filter the report.

If you do it the same way, just remember to make sure that you highlight all the columns that you want the filter to be applied to before you apply the filter. If you don’t, that column won’t be filtered, and you need it filtered.

You could also not leave a blank column between the report and the filter column. Then Excel will apply the filter to the added column along with the rest of the report. It’s really your preference on how you want to do it.

The image below shows the extra column added to the right of the report.

excel-filters-report
Column Added to Report For Filtering

Now, here’s the crucial part:

The formula needs to be added to all the cells in the column starting from the first row of the report after the titles right down to the end of it.

And what is the formula?

It’s this, in cell I2:

=IF(AND(ISBLANK(A2),SUM(C2:D2)=0),”Hide”, “Show”).

What it is doing is checking if cell A2 is blank AND if the total of C2 and D2 is zero. If the result of both are TRUE, it results in the text “Hide”. If the result of either one is FALSE, it shows the text “Hide”.

Why do we care about cell A2?

Well, for this report, column A has the name of the store, and that row is needed to show on the report. If you didn’t add the ISBLANK function, that row would be hidden when you didn’t want it to be.

Now, once you have that formula copied down to all the rows of the report, you can see that the text in that column changes, depending on the results in each cell, as you can see below.

excel-filters-formulas-report-formula-column
The Column Can Now be Filtered

Because you have done that, it’s easy to filter the report to hide the rows that you don’t want.

Now, all that needs to be done is to click on the filter on the column with the formula.

The image below shows that the Excel filter is applied to only include those that say “Show”.

excel-filters-report-filter-choice
Filter Applied to Column

Now the report is filtered to show only those lines that fit the criteria that you want.

As a result, the report has less data and allows the user to focus on the applicable data. See in the image below.

excel-filters-report-result
Excel Filtered Report Result

Conclusion

Excel filters can be used in unique ways to customize how the filtering is done.

As a result, here you saw how adding a column with a formula to determine which rows to include allows the Excel filters to change what is included on the report.

Please let me know if you find this useful, or if you have any suggestions for this method.

How to Handle Errors in Excel Formulas

I’m sure that this is everyone’s favorite subject – error handling in Excel formulas. The errors that typically show up on a spreadsheet are #N/A, #VALUE, #REF, #DIV/0!, #NUM, #NAME?, and #NULL!. There are a variety of built-in functions that Excel uses to handle errors. They do similar functions but have a few varieties and differences.

Some of the built-in formula error functions are: ISERR, ISERROR, ISNA, IFERROR, and IFNA. There is a difference between the “IS” and “IF” functions. The “IS” functions check if a value is an error and then return either a TRUE or FALSE. The “IF” functions return a value that you specify if there is an error, or the result of the formula if there is no error.  The variety of functions allow flexibility and creativity on how you decide to handle errors in your spreadsheets.

First, let’s consider the “IS” error functions.

“IS” Error Functions in Excel Formulas

ISERR

ISERR(value)

    • value = the value that you want to check if it results in an error. This can be a cell or a formula

ISERR will check if a value is an error and returns TRUE or FALSE. It checks these errors: #VALUE, #REF, #DIV/0!, #NUM, #NAME?, and #NULL!.  Therefore, note that it checks for every error, except for the #N/A error.

ISERROR

ISERROR(value)

    • value = the value that you want to check if it results in an error. This can be a cell or a formula

ISERROR will check if a value is an error and returns TRUE or FALSE. It checks these errors: #N/A, #VALUE, #REF, #DIV/0!, #NUM, #NAME?, and #NULL!.  Therefore, note that it checks for every error, including the #N/A error.

ISNA

ISNA(value)

    • value = the value that you want to check if it results in an error. This can be a cell or a formula

ISNA will check if a value is an error and returns TRUE or FALSE. It only checks for the #N/A error.

“IF” Error Functions in Excel Formulas

IFERROR

IFERROR(value, value_if_error)

    • value = the value that you want to check if it results in an error. This can be a cell or a formula
    • value_if_error = the value that you want to return if there is an error. This is a required value

IFERROR will check if a value is an error and returns a specified value or formula if there is an error. If there is no error, it will return the result of the cell or formula being checked. It checks for these errors: #N/A, #VALUE, #REF, #DIV/0!, #NUM, #NAME?, and #NULL!.  Note that it checks for every error, including the #N/A error.

IFNA

IFNA(value, value_if_na)

    • value = the value that you want to check if it results in an error. This can be a cell or a formula
    • value_if_na = the value that you want to return if there is an #N/A error. This is a required value

IFNA will only check if a value is an #N/A error and returns a specified value or formula if it is. If there is no #N/A error, it will return the result of the cell or formula being checked. It checks only for the #N/A error.

Why the Difference in Functions for Excel Formulas?

There are basically two differences in the functions. The first is the difference between the “IS” and ”IF” types of functions. The second is the #N/A difference.

The “IS” and “IF” Error Functions Difference

The “IS” functions return a TRUE or FALSE. This can be used for logical evaluations and actions within a formula in the same cell or within other other cells.

The “IF” functions can be used to change what is displayed in the current cell if there is an error.

While both functions can be used to change what is displayed in a cell, the “IF” function is shorter and less complicated to read. If the “IS” function is used, it needs to be combined with an IF function to produce the same result as using the “IF” function by itself.

For example, which of the following two error handling formulas do you find easier to read and understand? Which one requires less calculation steps?

ISERROR FORMULA IFERROR FORMULA
=IF(ISERROR(D1/C1),B1,D1/C1) =IFERROR(D1/C1,B1)

Both Excel formulas yield the same results. The ISERROR function, though, needs to be wrapped within an IF function to perform the same action as the IFERROR formula. This creates a longer formula, and, don’t quote me on this, but I’m sure that it requires Excel to do an extra calculation step in the cell compared to the IFERROR formula. That may be fine for a few error trapping formulas, but imagine multiplying that by thousands or tens of thousands of cells on a large spreadsheet.

Not only that, but with the shorter IFERROR formula, it is easier to follow the flow of the formula.Imagine if, a few months down the road, you are trying to decipher what  your formula is doing, or someone else who isn’t familiar with it is trying to figure out your formulas. The simpler, more direct formula would certainly be easier to understand.

Notice in the two images below the two different formulas, and that they both have the same result.

Excel Formulas
Formula View
Excel Formulas
Result View

The #N/A Difference

Both the “IS” and “IF” functions allow you to handle #N/A errors in your Excel formulas differently. Both types of functions allow you to specifically check for only the #N/A error by itself, or to group it with all of the errors that you can come across in Excel.

However, the “IS” functions has the unique ISERR function that checks for all errors, except for #N/A. The “IF” functions do not have this unique ability.

So if you need to check for all errors except the #N/A error, you will need to use the ISERR function and perhaps wrap it within an IF function.

Conclusion

Hopefully, I have shown, and explained to you, the variety of ways to handle and trap errors in your Excel formulas.

Depending on what you need in the types of errors you want to trap, Excel has a way to handle the error.

If you have any questions, or comments on ways that you handle Excel errors, please comment below.

Using SUM, SUMIF and SUMIFS in Excel

How many ways can Excel add thee, let me count the ways. Butchered Elizabeth Barrett Browning quotes aside, Excel has many functions for summing numbers. Last time I counted the different SUMs in Excel 2013, there were eight different SUM functions. There are SUM, SUMIF, SUMIFS, SUMPRODUCT, SUMSQ, SUMX2MY2, SUMX2PY2, SUMXMY2. I had never even heard of the last four functions until I started to write this blog.

I would say, though, that the most common ones are SUM, SUMIF and SUMIFS.

How do you know which one of these three functions to use?

Let’s take a look at each function and see what they do. That should help us decide when to use each function.

Use the Lowly SUM

SUM(number1, number2)

  • number1 = first number, cell, or cell range to sum
  • number2 = second number, cell, or cell range to sum
  • = next number(s) to sum, up to a total of 255

SUM is the most basic of the SUM functions. It adds the numbers entered between the brackets. This can include hard-entered numbers, cells, cell ranges, or a mix of them. Text values are ignored.

The formula =SUM(E1:E5) in the image below will sum the values in cells E1:E5, which for this example results in 19.

sumif
Example of SUM in a Formula

Expand Your Spreadsheets With SUMIF

SUMIF(range, criteria, [sum_range])

  • range = the cells that you want Excel to look in for a match to your criteria. It can be the same range as the range of cells that you are summing, or a different range of cells
  • criteria = what you want to find in the range. Wildcard characters can be include
  • [sum_range ] = If you want to sum a range of cells different from the first range, you enter the sum range here. This is optional

Like SUM, SUMIF also adds the numbers that are entered between the brackets, however you can specify one criteria that has to be matched in the range for it to included in the SUM.

For example, if you have a column with color names and a column of values, you can specify what matching color to sum. The formula =SUMIF(D1:D5,”Blue”,E1:E5) in the image below will sum the values in the second column (E1:E5) that have a matching “Blue” text in the first column (D1:D5), which for this example results in 7 (2+5).

sumif
Example of SUMIF in a Formula

Supercharge Your Formulas With SUMIFS

SUMIFS(sum_range, criteria_range1, criteria1,)

  • sum_rangethe cells to sum
  • criteria_range1the cells that you want Excel to look in for a match to your criteria
  • criteria1 = what you want to find in the range
  • = additional range and criteria, up to 127

Like SUMIF, SUMIFS sums a range. However, it allows multiple criteria to be specified.

Notice the difference between SUMIF and SUMIFS in the order of the arguments.

Expanding on the example above, let’s say you have a column with color names, column of values, and another column of items. You can specify what matching color and item to sum.

In this example, we will sum the value of all of the pencils that are blue.

The formula =SUMIFS(E1:E5,D1:D5,”Blue”, F1:F5,”Pencil”) in the image below will sum the values in the second column (E1:E5) that have a matching “Blue” text in the first column (D1:D5), and a matching “Pencil” in the third column (F1:F5), which for this example results in 5, since there is only one row that has both “Blue” and “Pencil”.

sumif
Example of SUMIFS in a Formula

(SUM)MARY

One beneficial and practical use of both SUMIF and SUMIFS is the ability to create a grand total or summary of a large amounts of cells.

Following the examples above, say you have hundreds of rows of data of colors and want a summary of the total value for each color. SUMIF and SUMIFS are excellent functions to simply  create that summary.

Hopefully, now  you have found that SUM, SUMIF and SUMIFS are some really great functions in Excel.

Perhaps you have thought of some creative and interesting ways to use these functions. If so, please comment on how you have used them.