Centriq Training Print Logo

Tuesday

May 30 05:25 AM

Blog Banner Graphic

Blog

Fantastic Functions in Excel

Fantastic Functions in Excel

August 4, 2017 in Corporate IT Training, Microsoft Office / by Craig Gerdes

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)

Microsoft-Excel-Fantastic-Functions

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.

Microsoft-Excel-Fantastic-Functions

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.

Microsoft-Excel-Fantastic-Functions

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], …)

Microsoft-Excel-Fantastic-Functions

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.

Microsoft-Excel-Fantastic-Functions

The formula and result shown below uses the example above:

{=TRANSPOSE(A2:B6)} ANSWER:

Microsoft-Excel-Fantastic-Functions

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)

Microsoft-Excel-Fantastic-Functions

The formulas and results shown below uses the example above:

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

=SMALL(B2:B6,3) ANSWER: 8, (3rd 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.

Microsoft-Excel-Fantastic-Functions

The formulas and results shown below uses the example above:

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

=MATCH(25,B2:B6,0) ANSWER: 4. (In the array, 25 is in the 4th 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.

 

Start Date
End Date
Day/Eve
Break Weeks
Track
Jan 23, 2023
Jul 27, 2023
Eve
4/3/23-4/7/23
5/22/23-5/26/23
7/3/23-7/7/23
CSSP-V
Jan 30, 2023
May 5, 2023
Day
3/6/23-3/10/23
4/10/23-4/14/23
CSSP-V
Feb 6, 2023
May 19, 2023
Day
3/27/23-3/31/23
FSCP-V
Mar 20, 2023
Jun 23, 2023
Day
4/24/23-4/28/23
5/22/23-5/26/23
CSSP-V
Apr 10, 2023
Jul 28, 2023
Day
5/29/23-6/2/23
7/3/23-7/7/23
FSCP-V
Apr 24, 2023
Oct 19, 2023
Eve
7/3/23-7/7/23
8/21/23-8/25/23
CSSP-V
May 15, 2023
Dec 14, 2023
Eve
7/3/23-7/7/23
9/4/23-9/8/23
11/20/23-11/24/23
FSCP-V
Jun 5, 2023
Sep 8, 2023
Day
7/3/23-7/7/23
8/7/23-8/11/23
CSSP-V
Jun 19, 2023
Oct 6, 2023
Day
7/3/23-7/7/23
9/4/23-9/8/23
FSCP-V
Jul 24, 2023
Jan 25, 2024
Eve
10/2/23-10/6/23
11/20/23-11/24/23
12/25/23-12/29/23
CSSP-V
Aug 14, 2023
Nov 17, 2023
Day
9/5/23-9/8/23
10/16/23-10/2/23
CSSP-V
Aug 28, 2023
Dec 15, 2023
Day
9/4/23-9/8/23
11/20/23-11/24/23
FSCP-V
Oct 2, 2023
Jan 5, 2024
Day
10/23/23-10/27/23
11/20/2023-11/24/23
12/25/23-12/29/23
CSSP-V
Oct 30, 2023
May 2, 2024
Eve
11/20/23-11/23/23
3/4/23-3/7/23
CSSP-V
If you don't see the Cohort Start date you are looking for don't forget to check out our campus calendars.
CSSP-I: Cloud & Security Specialist Program (In-Person Modality)
CSSP-V: Cloud & Security Specialist Program (Live Virtual Modality)
FSCP-I: Full Stack Coding Program (In-Person Modality)
FSCP-V: Full Stack Coding Program (Live Virtual Modality)
Please note that Centriq will be closed on the following observed holidays: New Year’s Day, Memorial Day, Independence Day, Labor Day, Thanksgiving Day, the day following Thanksgiving Day, and Christmas Day.
Start Date
End Date
Day/Eve
Break Weeks
Track
Feb 13, 2023
Mar 19, 2023
Day
3/20/23-3/24/23
4/17/23-4/21/23
CSSP-I
Apr 17, 2023
Jul 21, 2023
Day
5/22/23-5/26/23
7/3/23-7/7/23
CSSP-I
Jun 5, 2023
Sep 8, 2023
Day
7/3/23-7/7/23
8/7/23-8/11/23
CSSP-I
Jul 31, 2023
Nov 3, 2023
Day
9/4/23-9/8/23
10/2/23-10/6/23
CSSP-I
Sep 25, 2023
Dec 22, 2023
Day
11/20/23-11/24/23
CSSP-I
Nov 27, 2023
Mar 1, 2024
Day
12/25/23-12/29/23
1/29/24-2/2/24
CSSP-I
If you don't see the Cohort Start date you are looking for don't forget to check out our online instructor-led calendar.
CSSP-I: Cloud & Security Specialist Program (In-Person Modality)
CSSP-V: Cloud & Security Specialist Program (Live Virtual Modality)
FSCP-I: Full Stack Coding Program (In-Person Modality)
FSCP-V: Full Stack Coding Program (Live Virtual Modality)
Please note that Centriq will be closed on the following observed holidays: New Year’s Day, Memorial Day, Independence Day, Labor Day, Thanksgiving Day, the day following Thanksgiving Day, and Christmas Day.
Start Date
End Date
Day/Eve
Break Weeks
Track
Jan 16, 2023
Apr 21, 2023
Day
2/2023-2/24/23
3/20/23-3/24/23
CSSP-I
Jan 23, 2023
Jul 27, 2023
Eve
4/3/23-4/7/23
5/22/23-5/26/23
7/3/23-7/7/23
CSSP-I
Feb 27, 2023
Jun 2, 2023
Day
4/3/23-4/7/23
5/1/23-5/5/23
CSSP-I
Apr 10, 2023
Jul 14, 2023
Day
5/15/23-5/19/23
7/3/23-7/7/23
CSSP-I
May 22, 2023
Aug 18, 2023
Day
7/3/23-7/7/23
CSSP-I
Jun 26, 2023
Sep 29, 2023
Day
7/3/23-7/7/23
8/7/23-8/11/23
CSSP-I
Aug 7, 2023
Nov 10, 2023
Day
9/4/23-9/8/23
10/9/23-10/13/23
CSSP-I
Sep 11, 2023
Dec 15, 2023
Day
10/16/23-10/20/23
11/20/23-11/24/23
CSSP-I
Oct 23, 2023
Jan 26, 2024
Day
11/20/23-11/24/23
12/25/23-12/29/23
CSSP-I
Dec 4, 2023
Mar 8, 2024
Day
12/25/23-12/29/23
2/5/24-2/9/24
CSSP-I
If you don't see the Cohort Start date you are looking for don't forget to check out our online instructor-led calendar.
CSSP-I: Cloud & Security Specialist Program (In-Person Modality)
CSSP-V: Cloud & Security Specialist Program (Live Virtual Modality)
FSCP-I: Full Stack Coding Program (In-Person Modality)
FSCP-V: Full Stack Coding Program (Live Virtual Modality)
Please note that Centriq will be closed on the following observed holidays: New Year’s Day, Memorial Day, Independence Day, Labor Day, Thanksgiving Day, the day following Thanksgiving Day, and Christmas Day.