With hundreds of functions available in Excel in multiple categories, it is nearly impossible for any one person to know how to use all of the functions.

This blog article will discuss a few of the lesser-known functions such as FORECAST, GETPIVOTDATA, TRANSPOSE, LARGE/SMALL (not the same as MAXIMUM/MINIMUM), and INDEX with MATCH.

As you may already know Excel functions are grouped into several categories including financial, logical, text, date/time, lookup and reference, math, statistical, engineering, cube, and of course, several others including the basic AutoSum functions.

Some helpful resources include Excel functions (by category) and Excel functions (alphabetical).  A quick read of Overview of formulas in Excel might also be useful. If you have a specific function question, you may be able to get an answer by posting a question in the Excel community forum.

Forecast

We will look at two forecast functions. You can use these functions to predict future sales, inventory requirements, or consumer trends.

The first function calculates, or predicts, a future value by using existing values. The predicted value is a y-value for a given x-value. The known values are existing x-values and y-values, and the new value is predicted by using linear regression.

FORECAST(x, known_y’s, known_x’s)

The second forecast function calculates or predicts a future value based on existing (historical) values. The predicted value is a continuation of the historical values in the specified target date, which should be a continuation of the timeline.

FORECAST.ETS(target_date, values, timeline)

The formula and result shown below uses the example data above (on the left) and answers the question if X=30, what will the value of Y be?

=FORECAST(30,B2:B6,A2:A6) ANSWER: 47.62366

The formula and result shown below uses the example data above (on the right) and answers the question what will the value be on July 17, 2017?

=FORECAST.ETS(D10,E2:E6,D2:D6) ANSWER: 28.60927

BONUS!!!!! One of the new features in Excel 2016 is to create the previous formula automatically with the simple click of a button. On the Data tab, in the Forecast group, click Forecast Sheet.

When you create a historical time-based forecast using the button option, Excel creates a new worksheet that contains both a table of the historical and predicted values and a chart that expresses this data similar to the screenshot below. Notice it also gives you Upper and Lower Confidence Levels.

GetPivotData

This function returns data stored in a PivotTable provided the data is visible in the table.

GETPIVOTDATA(data_field, pivot_table, [field1, item1, field2, item2], …)

The formulas and results shown below use the example PivotTable above:

=GETPIVOTDATA(“Amount”,G2:K7,”SalesPerson”,”Suzy”) ANSWER: $35.00

=GETPIVOTDATA(“Amount”,G2:K7,”Date”,”7-Jul”, “SalesPerson”,”Bill”) ANSWER: $15.00

 

Transpose

Sometimes you need to switch or rotate cells. You can do this by using two methods:

• Copy and paste using the Transpose option (more on that later).
• Use the TRANSPOSE function.

Each method has advantages and disadvantages and the situation may dictate which method you use. Let us look at the TRANSPOSE function first.

TRANSPOSE(array)

The key to getting TRANSPOSE to work:

1. First, select some blank cells. However, make sure to select the same number of cells as the original set of cells, but in the other direction.
2. After entering the formula above, make sure to press CTRL+SHIFT+ENTER.

NOTE: This is an array formula and needs to be entered with CTRL+SHIFT+ENTER. Excel will automatically wrap the formula in curly braces {}. If you try to enter them yourself, Excel will display the formula as text. If you do not use CTRL+SHIFT+ENTER then the formula will return a #VALUE! Error.

The formula and result shown below uses the example above:

{=TRANSPOSE(A2:B6)} ANSWER:

To use the copy and paste using the Transpose option:

1. Select the range of data you want to rearrange, including any row or column labels, and press Ctrl+C.
2. Right-click the first cell where you want to paste the data, and pick Transpose .

 

Large and Small

You may be familiar with the MAXIMUM and MINIMUM functions that return the highest or lowest value in a range. Now let us get acquainted with their cousins LARGE and SMALL.

The LARGE function returns the k-th largest value in a data set. You can use this function to select a value based on its relative standing. For example, you can use LARGE to return the highest, runner-up, or third-place score. Similarly, the SMALL function returns the k-th smallest value in a data set.

LARGE(array, k)

SMALL(array, k)

The formulas and results shown below uses the example above:

=LARGE(B2:B6,2) ANSWER: 10, (2^nd largest)

=SMALL(B2:B6,3) ANSWER: 8, (3^rd smallest)

 

Index and Match

If you have used the Lookup functions before (such as a VLOOKUP) then you may like the INDEX and MATCH functions, which you could use instead, especially when used together.

The INDEX function returns a value from within a table or range.

INDEX(array, row_num, column_num)

The MATCH function searches for a specified item in a range of cells, and then returns the relative position of that item in the range.

MATCH(lookup_value, lookup_array, [match_type])

NOTE: The match_type argument specifies how Excel matches lookup_value with values in lookup_array. A value of 0 finds the first value that is exactly equal to lookup_value.

Using these two functions in combination with each other is very powerful. Therefore, instead of using one of the LOOKUP functions, you might use the MATCH function to provide a value for the row_num argument of the INDEX function.

The formulas and results shown below uses the example above:

=INDEX(A2:B6,2,2) ANSWER: 10.?? (In the array, in the 2^nd row, 2^nd column, the value is 10)

=MATCH(25,B2:B6,0) ANSWER: 4. (In the array, 25 is in the 4^th position)

=INDEX(B2:B6,MATCH(B1,A2:A6,0)) ANSWER: 5. (The value typed in B1 locates the position (row_num) in the lookup array (A2:A6) and then provides the corresponding value in array (B2:B6)

As you can see these functions really are fantastic! I hope that you can find some practical ways to use them in your situation.

For more information about these functions and other formulas, take an Excel Class from Centriq. For course descriptions and dates, see our website at https://centriq.com/corporate/class-schedule/?q=Excel.

Be sure to watch for future blog articles in this Excel series. The next topic will feature Awesome Add-ins and then Super Settings. Also plan to attend a free Excel Lunch and Learn on Tuesday, September 12. We will feature live demonstrations of all of the items covered during this Excel blog series. If you would like to attend, CLICK HERE to register.

In addition, if you would like more information about Excel or other training classes available, contact a Centriq Training Advisor online or at 913.322.7062 for assistance.