Where to start to get The Best Excel Homework Help Service?

Choose PureAssignment for reliable, secure, and high-accuracy online Excel help services that guarantee your academic success. If you need help with a similar project, Click HERE to opt to our Excel homework help webpage.

Excel SAP Assignment Instructions

EX 7-2Using Lookup Functions

Often data analysts receive large data sets with hundreds or even thousands of records and dozens of fields. They must then reduce that wealth of data to a few important statistics and charts. In this module, you’ll learn the skills you need to reveal facts and trends hidden within a mass of information.

Claire has a workbook containing results of a social media marketing survey. The raw data is stored in an Excel table named “Media” containing records from 785 social media posts made to Facebook, Instagram, and Twitter. Within each record, Claire included the date of the post, the post’s general topic, and the response it received. You need to summarize the social media data to evaluate the impact of the company’s ad campaign on its social media presence.

To open Claire’s workbook containing the marketing data:

Open the NP_EX_7-1.xlsx workbook located in the Excel7 > Module folder included with your Data Files, and then save the workbook as NP_EX_7_Syrmosta in the location specified by your instructor.

In the Documentation sheet, enter your name and the date.

Go to the Media Log worksheet. The Media Excel table on this sheet contains Syrmosta’s social media posts from the past year. See Figure 7-1.

Figure 7-1Media Excel table in the Media Log worksheet

Review each of the worksheets, and then go to the Terms and Definitions worksheet to study the fields used with this data table.

Go the Media Log worksheet.

Successful social media marketing is all about getting the user engaged. An engaged user is more likely to turn into a loyal customer. There are four general types of social media engagement with increasing levels of user involvement:

The user has viewed the social media post.

The user has indicated approval or “like” of the post.

The user has taken the time and effort to comment on the post.

The user has shared the post with others.

In the Media table, Claire recorded the total number of views, likes, comments, and shares for each social media post made in the last year. You will add another field that calculates the sum of these engagements for each post.

To calculate the total engagements per post:

In the Media Log worksheet, click cell K5 to select it.

Type =SUM( to begin the SUM function.

Use your mouse to select the range G5:J5. The field reference Media[@[VIEWS]:[SHARES]] is added to the formula to reference the Views through Shares field in the current row of the Media table.

Type ) to complete the SUM function, and then press ENTER. Excel calculates the sum of the engagements for each post, returning a total of 343 engagements for the first post. See Figure 7-2.

Figure 7-2Engagements calculated field added to the Media table

Claire is also interested in the engagement rate, which is the percentage of followers that engage with each post. The number of followers tells Claire how many subscribers a post might have, but the engagement rate shows how often they respond to the post’s contents. You will calculate the engagement rate for each post by dividing the total engagements by total followers at the time the post was uploaded.

To calculate the engagement rate per post:

In the Media Log worksheet, click cell L5 to select it.

Type = to begin the formula, click cell K5 to select the Engagements field, type / for division, click cell E5 to select the Followers field, and then press ENTER. The formula =[@ENGAGEMENTS]/[@FOLLOWERS] is entered in the Engagement Rate column. See Figure 7-3.

Figure 7-3Engagement rate per post

The first post, in row 5, has an engagement rate of 0.29% indicating that about 29 out of 10,000 followers engaged with the post in some way. The last post, in row 789, has an engagement rate of 2.49% or about 2.5 users per 100 followers. Are these rates low? The answer depends on the social media content. Social media sites for sports or politics typically have much higher engagements rates than this, but the fashion industry must work harder to engage its followers. Claire grades engagement rates at the following levels:

Poor—0% to less than 0.5%
Average—0.5% to less than 1.0%
Good—1.0% to less than 1.5%
Very Good—1.5% to less than 2.0%
Excellent—Greater than or equal to 2.0%

Claire wants to show the engagement level for each post. You will display the level using an approximate match lookup.

EX 7-2aCreating Approximate Match Lookups

An approximate match lookup is a lookup in which the lookup value is compared to ranges of values rather than a single, specific value. For example, an engagement rate of 0.38% would receive a grade of “Poor” from Claire because it falls within the range of 0% to less than 0.5%, and a value of 1.83% would receive a grade of “Very Good” because it falls within the 1.5% to less-than 2.0% range.

Approximate match lookups can reference a vertical or horizontal lookup table. A vertical lookup table for approximate match lookups must be organized as follows:

The first column of the lookup table stores the values that will be compared to the lookup value.
The values in the first column must be sorted in ascending order.
The first entry in first column contains the lowest possible comparison value.
Each subsequent entry in the first column contains the lower end of the range that the lookup value is compared to.

Excel finds an approximate match by going down the first column of the table until it locates the row containing the largest value that is less than or equal to the lookup value. Then it retrieves the corresponding values from that row. Figure 7-4 shows how a vertical lookup table would find an approximate match for a lookup value of 1.83%.

Figure 7-4Approximate match returned by a vertical lookup table

Because 1.83% falls between the 1.50% row and the 2.0% row, the largest value that is less than or equal to the lookup value is 1.50%. Excel scans down the first column to that row and then goes to the second column, returning an engagement level of “Very Good.”

Reference

Creating an Approximate Match Lookup

Construct a table with the values to compare to the lookup value in the first column for a vertical lookup table or first row for a horizontal lookup table.
In the first row or first column, enter values that represent the lower end of each range for looking up data.
Sort the values in the first column or first row in ascending order.
For vertical lookup tables, use the function

where lookup_value is the value to look up, table_array is the reference to the vertical lookup table and col_index is the column number containing the value to be returned. You do not have to specify a range_lookup value in the VLOOKUP function, but if you do, it must be set to TRUE for approximate match lookups.

For horizontal lookup tables, use the function

To perform an approximate match lookup with a vertical lookup table, you use the same VLOOKUP function used for exact match lookups

where lookup_value is the value to look up, table_array is the reference to the vertical lookup table, and col_index is the column number containing the value to be returned. You do not have to specify a value for the optional range_lookup argument because Excel assumes an approximate match lookup by default. If you do include that argument, set the value to TRUE to specify an approximate match lookup. The following VLOOKUP function performs the approximate match lookup shown in Figure 7-4:

The values in the first column of the lookup table must be sorted in ascending order. If they are not, the VLOOKUP function might return the wrong value.

You will use a lookup table and approximate match lookup to show the engagement level for each post. You will start by creating a lookup table storing the engagement levels associated with different engagement rates.

To create the vertical lookup table of engagement levels:

Go to the Lookup Tables worksheet.

In the range B5:B9, enter the values 0.0%, 0.5%, 1.0%, 1.5%, and 2.0% representing the lower end of each range of engagement rate values in ascending order.

In the range C5:C9, enter Poor, Average, Good, Very Good, and Excellent as the descriptive names for each level. See Figure 7-5.

Figure 7-5Vertical lookup table for the engagement rate

Select the range B4:C9, and then enter Rate_Lookup in the Name box. Lookup tables are easier to use when given a defined name.

Next you will apply the VLOOKUP function to the Rate_Lookup table to display the engagement level for each media post.

To apply the VLOOKUP function with an approximate match:

Go to the Media Log worksheet and click cell M5.

On the ribbon, click the Formulas tab. In the Function Library group, click the Lookup & Reference button, and then click VLOOKUP in the list of functions. The Function Arguments dialog box opens.

Make sure the insertion point is in the Lookup_value box, and then click cell L5 in the Media table. The cell reference [@[ENGAGEMENT RATE]] references the value of the Engagement Rate field in the current row.

Press TAB to move to the Table_array box, and then type Rate_Lookup to reference the lookup table you defined in the Lookup Tables worksheet.

Press TAB to move to the Col_index_num box, and then type 2 to indicate that the value to be returned is in the second column of the lookup table. See Figure 7-6.

Figure 7-6Function arguments for the approximate match VLOOKUP function

Click OK to close the dialog box and apply the VLOOKUP function to the Engagement Level field for all records in the table. See Figure 7-7.

Figure 7-7Engagement levels displayed from VLOOKUP function

An engagement level appears for each post based on the value of the engagement rate. Later, you’ll explore whether these engagement levels differ based on the social media site or the content of the post.

Proskills

Problem Solving: Avoiding Common VLOOKUP Mistakes

The VLOOKUP function is one of the most useful and widely applied of the Excel functions. But, it’s also easy to make a mistake using it. Here are some common VLOOKUP errors to avoid:

Forgetting to include a range_lookup value of FALSE for exact match lookups. If you don’t include the range_lookup value, VLOOKUP will assume an approximate match lookup.
Failing to sort the first column in ascending order for approximate match lookups. VLOOKUP requires that the first column be assorted in ascending order or else you may get an incorrect result.
Failing to specify the lower end of each range with an approximate match lookup. Always start with the lowest possible compare value in the first row and then build your ranges from there.
Placing duplicate values in the first column. VLOOKUP requires unique values for both exact and approximate match lookups.
Using relative references to the lookup table. If you use AutoFill to copy your VLOOKUP formulas, make sure you use an absolute reference or a defined name for the lookup table so that the copied formulas will always reference the lookup table’s correct location.
Failing to revise the table for new data. If you add new columns or new rows to the lookup table, you must edit your VLOOKUP functions accommodate the additional content.

Whenever you use VLOOKUP, take some time to compare your answers to the table, ensuring that you are getting the results you expect. Be sure to try a wide range of lookup values from the lowest possible to the highest. Don’t get caught with an error in your table or in your formula.

EX 7-2bUsing Horizontal Lookup Tables

Depending on the data, it might be simpler or more logical to arrange the lookup values in rows rather than columns, creating a horizontal lookup table. Figure 7-8 shows the lookup table for the engagement levels arranged in rows rather than columns.

Figure 7-8Approximate match returned by a horizontal lookup table

To retrieve data from a horizontal lookup table, use the HLOOKUP function

where lookup_value references the value to look up, table_array references the location of the horizontal lookup table, row_index_num is the number of the row containing the data to be returned, and range_lookup is TRUE or omitted for approximate match lookups or FALSE for exact matches. For example, to return the value shown in Figure 7-8, you would enter the following function to create an approximate match lookup:

For approximate matches, horizontal lookup tables have the same rules as vertical lookup tables. The values in the first row of a horizontal lookup table must be sorted in ascending order. The lower value of each range is entered in the first row.

Insight

Performing Partial Lookups with Wildcards

Partial lookups can be helpful when you’re working with large data sets. A partial lookup matches a character pattern rather than a specific value. You use wildcards to create the character pattern for the lookup. For example, the following VLOOKUP function uses the * wildcard to match any string of characters that start with letters “WIL” from the third column of a lookup table named “LastNames”:

Because VLOOKUP ignores case, values such as William, Willet, Will, or Willey would be matched by this function. Be aware that Excel will choose the first match it encounters in the table even if other entries would match the pattern. To combine a wildcard character with a cell value, use the & character. For example, the following function looks up values starting with the text stored in cell B10:

Partial matches can be done only with exact match lookups, so the range_lookup argument must be set to FALSE.

EX 7-2cRetrieving Data with Index Match Lookups

The VLOOKUP function finds data based on values in the first column. The HLOOKUP function finds data based on values in the first row. If you want to find data using values from both the first row and the first column, you can do an Index Match lookup. As the name suggests, an Index Match lookup combines the INDEX and MATCH functions.

The INDEX function returns the value from a data range at the intersection of a specific row and column, and has the syntax:

where array is a reference to a data range, row_num is a selected row from that range, and col_num is a selected column. Figure 7-9 shows an example of the INDEX function used to retrieve data on total media shares broken down by site and month.

Figure 7-9INDEX function returns a data value

In this example, the INDEX function retrieves the value from the data in the range B3:F6 at the intersection of the third row and fourth column, displaying the number of shares on Instagram during March. Of course, in most cases you won’t know the row and column numbers. That is where the MATCH function becomes useful.

The MATCH function returns the position of a value found within a row or column. The syntax of the MATCH function is

where lookup_value is the value to locate, lookup_array is the row or column in which to search, and match_type is an optional argument specifying how the match should be done. As with the VLOOKUP function, searches can be done as exact matches or approximate matches depending on the following match_type values:

match_type = 1—(default ) An approximate match lookup that finds the largest value less than or equal to lookup_value. The values must be arranged in ascending order.
match_type = 0—An exact match lookup that finds an exact match to lookup_value.
match_type = −1—An approximate match lookup that finds the smallest value greater than or equal to lookup_value. The values must be arranged in descending order.

Figure 7-10 shows how to apply the MATCH function to locate the column location of the text “MAR” in the range B3:F3 and the row location of the text “Instagram” in the range B3:B6. Note that because match_type is set to zero, these are exact match lookups.

Figure 7-10MATCH function finds row and column locations

By nesting MATCH functions within the INDEX function, you can retrieve data from a table by searching through the entries in the table’s first column and row, locating matching values. Figure 7-11 shows how to combine the INDEX and MATCH function to find the media shares from Instagram during March. By changing the values in cells C8 and C9 you can retrieve the media shares for any site and any month listed in the lookup table.

Figure 7-11INDEX and MATCH functions combined

An Index Match lookup is a powerful technique for retrieving data from tables. Anything you can do with VLOOKUP or HLOOKUP, you can also do with Index Match. An Index Match lookup has two main advantages over the lookup functions:

You can do an approximate match lookup based on ranges sorted in descending order rather than ascending order by setting match_type to −1. This is not possible with the VLOOKUP and HLOOKUP functions.

You can locate values by matching terms from any column or row within the table.

Searches are not confined to the first column or first row of the lookup table as they are with the VLOOKUP and HLOOKUP functions. On the other hand, VLOOKUP and HLOOKUP formulas are easier to write because they don’t involve nesting one set of functions within another. Excel users should be comfortable with both approaches.

EX 7-3Exploring Logical Functions

The Excel family of logical functions is used to set data values based on whether a condition is true or false. So far, you’ve only used the IF function

where logical_test is the condition that is either true or false, value_if_true is the value returned by the IF function if logical_test is true, and value_if_false is the function’s value if logical_test is false. The IF function is limited to returning one of two possible results. To test for multiple conditions, returning different values for each condition, you can nest one IF function within the other, replacing the value_if_false argument with another IF function. For example, the following function uses two nested IF function to test the value of cell A1:

If A1 is less than 0.5%, the function returns the value “Poor.” Otherwise, the function tests for whether A1 is less than 1%. If that condition is true, the function returns the value “Average.” And if that condition is false, the function returns the value “Good.” By adding more nested IF functions, you can test for as many possible conditions as you want. However, at some point the collection of nested IF functions will become so convoluted that you’re better off using a lookup table to match each condition to a different value.

Reference

Applying a Logical Function

To test one condition against two possible outcomes, use

To test for multiple possible outcomes, use

where logical_test1, logical_test2, and so on are the different logical conditions, and value_if_true1, value_if_true2, and so on are the values associated with each condition, if true.

To return a true value if any one of multiple conditions are true, use

where logical1, logical2, logical3, and so on are conditions which are either true or false.

To return a true value only if all conditions are true, use

EX 7-3aUsing the IFS Function

Another way of working with multiple IF conditions is with the IFS function. The IFS function tests for multiple conditions without nesting and has the syntax

where logical_test1, logical_test2, and so on are logical conditions, and value_if_true1, value_if_true2, and so on are the values associated with each condition, if the condition is true. This means that you could rewrite the nested IF function from earlier using the following IFS function that specifies three possible conditions for the value of cell A1:

The IFS function doesn’t include a default value if all the conditions are false. However, you can add a default condition to the end of the list by setting the final logical test to the value TRUE, as in the following expression:

This IFS function will return a value of “Poor” if A1 is less than 0.5%. Otherwise, the function will return a value of “AVERAGE” if A1 is less than 1. But if neither of those conditions is met, the function will return a value of “Good.”

EX 7-3bCombining Conditions with the OR and AND Functions

Another way of combining multiple conditions is with the OR function or the AND function. The OR function combines multiple conditions, returning a value of TRUE if any of the conditions are true. The AND function returns a value of TRUE if all of the conditions are true. The two functions have a similar syntax:

where logical1, logical2, logical3, and so on are conditions that are either true or false. For example, the following expression returns a value of TRUE if cell A1 equal 2 or if cell B1 equals 4 or if cell C1 equals 10:

However, the following expression returns the value TRUE only if A1 equals 2 and B1 equals 4 and C1 equals 10:

The OR and AND functions can be nested within an IF function to provide a test involving multiple conditions. The following IF function tests three conditions enclosed within the AND function, returning the value “Pass” if A1 = 2 and B1 = 4 and C1=10; otherwise, it returns the value “Fail”:

Claire wants to know whether the clothing product discussed in the media post is a high-profit or low-profit item. Clothes from the Business Attire or Nightwear categories are considered high-profit items for the company, and clothes from the Casual Attire or Sportswear categories are considered low-profit items. You’ll add a field named Profit Group to the Media table, and then nest an OR function within an IF function to calculate the value of the Profit Group field for each record in the table.

To nest the OR in an IF function to display the product’s profitability:

Where to start to get The Best Excel Homework Help Service?

In the Media Log worksheet, click cell N5 to select it.

Because this will be a complicated nested function, you will enter the formula starting with the innermost function.

On the Formulas tab, in the Function Library group, click the Logical button, and then click OR. The Function Arguments dialog box opens.

With the insertion point in the Logical1 box, click cell F5 to enter the reference [@TOPIC] in the box, type =“Business Attire” as the first logical condition, and then press TAB to go to the Logical2 box.

In the Logical2 box, click cell F5 to enter the reference[@TOPIC], type =“Nightwear” as the second logical condition.

Click OK. The formula is entered in the Profit Group column. Each record in the Profit Group column displays either TRUE or FALSE. FALSE appears in cell N5, TRUE appears in cell N6, FALSE appears in cell N7, and so forth.

Double-click cell N5 to enter Edit mode. You will enclose the OR function in cell N5 within an IF function.

Click between the = symbol and OR, and then type IF( to begin inserting the IF function before the OR function.

Click the end of the formula, and then type , “High”, “Low”) to specify the two possible values that can appear depending on the value returned by OR function.

Press ENTER. The formula =IF(OR([@TOPIC]=“Business Attire”,[@TOPIC] =“Nightwear”), “High”, “Low”) is added to every record in the Profit Group column. See Figure 7-12.

Figure 7-12OR function nested within an IF function

Trouble? If Excel reports a syntax error, you made a mistake when typing the formula, which is easy to do with nested functions. Clear the formulas from the Profit Group column, and then repeat Steps 1, 2, 3, 4, 5, 6, 7, 8, and 9. Before pressing ENTER, make sure you have closed all quotation marks and matched opening and closing parentheses and brackets.

If you are still having trouble, clear the formulas from the Profit Group column, and then go to the Terms and Definitions worksheet. Copy cell G18, go back to the Media Log worksheet, and paste the formula into cell N5. Double-click cell N5, and then press ENTER to enter the formula for every record in the Media table.

The Media table is complete. Next, you will analyze the data in the table, starting by calculating summary statistics that tally the number of posts made to each social media site.

EX 7-4Applying Summary IF Functions

Excel supports several functions that combine statistics like AVERAGE, SUM, and COUNT, with logical expressions, so that you calculate statistics only on those cells that match that a logical condition.

Reference

Using a Summary IF Function

To count the number of cells within a range that satisfy specified criteria, use

where range references the range of cells or table field to be counted and criteria is a value or a condition that defines which cells to include in the count.

To calculate the sum of values for cells that satisfy specified criteria, use

where range is the range of cells to be evaluated by the criteria specified in the criteria argument, and sum_range is an optional argument that specifies the range of values to sum.

To calculate the average of values for cells that satisfy specified criteria, use

where average_range is an optional argument that specifies the range of values to average.

For conditions that involve multiple criteria ranges, use the COUNTIFS, SUMIFS, AVERAGEIFS, MAXIFS, and MINIFS functions.

EX 7-4aConditional Counting with COUNTIF

Claire wants to know how many posts over the past year were made to Facebook, Instagram, and Twitter. You can use the COUNT function to count the number of social media posts, but that would include all records, regardless of the social media site. To count only those records that match a specified condition, you can create a conditional count using the COUNTIF function

where range references the range of cells or table field to be counted and criteria is a value or a condition that defines which cells to include in the count. For example, the following expression counts the number of cells in the range D5:D789 whose value equals “Facebook”:

Rather than explicitly entering the criteria value, you can reference a cell containing that value. The following formula counts the numbers of cells in the range D5:D789 whose values equal the value stored in cell B10:

Figure 7-13 provides examples of other ways the COUNTIF function can be used for conditional counting.

Figure 7-13

Conditional counting with the COUNTIF function

Formula	Description
=COUNTIF(A1:A100, “Twitter”)	Counts the cells in the A1:A100 range with the text, “Twitter”
=COUNTIF(Media[SITE], “Twitter”)	Counts the records in the Media table whose Platform field value equals the text, “Twitter”
=COUNTIF(B1:B100, 25)	Counts the cells in the B1:B100 range that have a value of 25
=COUNTIF(C1:C100, D10)	Counts the cells in the C1:C100 range with values equal to the value stored in cell D10
=COUNTIF(E1:E100, “> 50”)	Counts the cells in the E1:E100 range with values greater than 50
=COUNTIF(Media[SHARES], “>=50”)	Counts the records in the Media table whose Shares value is greater than or equal to 50
=COUNTIF(F1:F100, “>=” & G10)	Counts the cells in the F1:F100 range with values greater than or equal to the value in cell G10
=COUNTIF(Media[VIEWS], “>” & Popular)	Counts the records in the Media table where the value of the Views field is greater than the value stored in the Popular defined name

Note that you can use the ampersand character (&) to combine text strings with cell values. For example, if cell A1 contains the value 50, then the expression “<=” & A1 is equivalent to the text string “<= 50”. By storing conditional values within cells, you can use the COUNTIF function to calculate different conditions by changing those cell’s values.

You will use the COUNTIF function to count the number of posts from each social media site, placing the formulas in the Dashboard worksheet.

To do conditional counting with the COUNTIF function:

Go the Dashboard worksheet and click cell B11.

Type =COUNTIF(Media[SITE], B10) to count the number of records in Media table where the value of the Site field equals the value stored in cell B10 (Facebook).

Press ENTER. The formula returns 332, indicating that 332 posts were made to Facebook over the past 12 months.

Copy the formula in cell B11 and paste it into cells D11 and F11 to count the number of posts made to the Instagram and Twitter sites. There were 303 posts to Instagram and 150 posts to Twitter.

In cell H11, enter the formula =COUNTA(Media[SITE]) to calculate the total posts from all sites. The formula returns 785, which is also equal to the sum of cells B11, D11, and F11. See Figure 7-14.

Figure 7-14COUNTIF function applied to the Media table

Note that the COUNTIF function makes no distinction between text values and numeric values unlike the COUNT function, which does not count text values.

EX 7-4bCalculating Conditional Sums with SUMIF

The SUMIF function calculates the sum of values that match specified criteria, creating a conditional sum. The syntax of the SUMIF function is

where range is the range of cells to be evaluated by the criteria specified in the criteria argument, and sum_range is an optional argument that specifies the values to sum. The following expression uses the SUMIF function to calculate the sum of values in the range A1:A100, but only those cells whose value is greater than 50:

If you include the sum_range argument, the selected cells in that range will correspond to the selected cells in the criteria range. Figure 7-15 shows an example of using the SUMIF function to sum the values in the range E3:E12 whose cells correspond to cells in the criteria range (range C3:C12) with the value “Twitter.”

Figure 7-15Conditional sums with the SUMIF function

You will use the SUMIF function to calculate the total number of views for each social media site in Media table. You will use the SITE field as the criteria range and the VIEWS field as the sum range.

To calculate a conditional sum with the SUMIF function:

In the Dashboard worksheet, click cell B15.

Type =SUMIF(Media[SITE], B10, Media[VIEWS]) to sum the values from the Views field for records whose Site field value equals “Facebook” (cell B10).

Press ENTER. The formula returns 153,677, which is the total number of views from posts made to Facebook over the past year.

Copy the formula in cell B15 and paste it into cells D15 and F15 to find the total views from posts made to Instagram and Twitter. The values 121,134 and 6,269 appear in cells D15 and F15, respectively.

In cell H15, enter the formula =SUM(Media[VIEWS]) to calculate the total views from all sites. The formula returns 281,080. See Figure 7-16.

Figure 7-16SUMIF function applied to the Media table

These calculations reveal that most views come from Facebook and Instagram, and very few come from Twitter. This indicates that those two sites might be better platforms for advertising Syrmosta products and promotions.

EX 7-4cCalculating Conditional Averages with AVERAGEIF

The AVERAGEIF function calculates a conditional average by taking the average only of those values that match specified criteria. The syntax of the AVERAGEIF function is

where range is the range of cells to be evaluated by the criteria specified in the criteria argument, and average_range is an optional argument that specifies the values to be averaged. The following expression uses the AVERAGEIF function to calculate the sum of values in the range A1:A100, but only those cells whose value is greater than 50:

If you include the average_range argument, Excel will calculate averages for cells in that range corresponding to values in the criteria range. Figure 7-17 shows how to apply the AVERAGEIF function to calculate the average of the values in the range D3:D12 but only for followers of the Facebook site.

Figure 7-17Conditional averages with the AVERAGEIF function

The number of followers for each social media site varies from day-to-day throughout the year. Claire wants you to calculate the average number of followers per post. You will use the AVERAGEIF function to calculate the average number of followers broken down by the social media site.

To calculate a conditional average with the AVERAGEIF function:

In the Dashboard worksheet, click cell B19.

Type =AVERAGEIF(Media[SITE], B10, Media[FOLLOWERS]) to calculate the average number of followers for the Facebook posts over the past year.

Press ENTER. The formula returns 126,683, which is the average formatted to the nearest integer.

4. 4.

Copy the formula in cell B19 and paste it into cells D19 and F19 to calculate the average followers per post for Instagram and Twitter. The formulas return 64,055 and 33,509, respectively.

In cell H19, enter the formula =B19+D19+F19 to calculate the sum of the three averages. The average for all the three sites is 224,248. See Figure 7-18.

Figure 7-18AVERAGEIF function applied to the Media table

Save the workbook.

Facebook is the most popular social media site for following news about Syrmosta. Your calculations show that an average post has almost 127,000 followers on Facebook, 64,000 followers on Instagram, and more than 33,000 followers on Twitter. The total number of followers is about 224,000.

EX 7-4dUsing Summary IFS Functions

The COUNTIF, SUMIF, and AVERAGEIF functions support a single criteria argument. Excel also supports summary functions that allow for multiple criteria—COUNTIFS, SUMIFS, and AVERAGEIFS. To apply multiple criteria for a conditional count, use the COUNTIFS function

where range1 is the range in which to count cells indicated in criteria1 ; the optional arguments range2, criteria2, and so on are additional ranges and criteria for choosing which cells to count. You can include up to 127 range/criteria pairs. Each additional range must have the same number of rows and columns as range1 and criteria1, though the ranges do not have to be adjacent.

Figure 7-19 shows conditional counting with two range/criteria pairs. Notice that the count includes only those records that satisfies both criteria.

Figure 7-19Conditional counting with the COUNTIFS function

The SUMIFS and AVERAGEIFS functions have similar syntaxes to the COUNTIFS function except that both begin with a reference to the range containing the sum or average to be calculated followed by one or more criteria/range pairs. The syntax for the SUMIFS function and AVERAGEIFS function are

where sum_range is a reference to cells to sum and avg_range is a reference to the cells to average. Note that the order of arguments has changed from SUMIF and AVERAGEIF. In the SUMIF and AVERAGEIF functions, the range to be calculated is an optional argument listed last. With the SUMIFS and AVERAGEIFS functions, the range to be calculated is listed first and is required.

Figure 7-20 shows how to calculate conditional sums and averages using multiple range/criteria pairs. Once again, only those cells that match all criteria are included in the calculated result.

Figure 7-20Sums and averages with multiple criteria

Finally, to calculate the minimum and maximum value in a range under multiple criteria, use the MINIFS function and the MAXIFS function, which have the syntax

where min_range and max_range reference the cells in which to find the minimum and maximum value, subject to constraints specified in the range/criteria pairs.

The summary IFS functions calculate summary statistics based on several criteria. But that approach becomes cumbersome as you add more criteria ranges to the function. A better approach in those situations is to construct a PivotTable. You’ll work with PivotTables in the next session.

ReviewSession 7.1 Quick Check

A school gives out grades in the following ranges: F: 0 – < 60; D: 60 – < 70; C: 70 – < 80; B: 80 – < 90; A: 90 – 100. Create a vertical lookup table for this grade scale with the range values in the first column and the letter grades in the second.
What is the function to retrieve the letter grade stored in the second column of the GradeScale table using a lookup value of 83 in an approximate match lookup?
What is the function to retrieve the value located in the fifth row and second column of the data in the range A10:G50?
What is the function to return the index number of the cell within the range E1:E50 that is equal to “Carter”, using an exact match?
What are two advantages of index matches over the VLOOKUP and HLOOKUP functions?
What is the function to count the number of cells in the range B1:B50 that equal “B”?
What is the function to calculate the average of the cells in the range C1:C50 for which the adjacent cell in the range B1:B50 equals “B”?
What is the function to calculate the sum of the values in the range D1:D50 for which in the adjacent value in the range A1:A50 equals “Senior” and the adjacent value in the range B1:B50 equals “B”?

Creating PivotTables

One of most useful Excel tools for data analysis and exploration is PivotTables. A PivotTable is an interactive table that groups and summarizes data in a concise tabular format. A single PivotTable can do the work of multiple summary IF functions but with more ease and flexibility.

Every PivotTable includes the following four primary areas:

Rows area —displays category values from one or more fields arranged in separate rows
Columns area —displays categories from one or more fields arranged in separate columns
Values area —displays summary statistics for one or more fields at each intersection of each row and column category
Filters area —contains a filter button that limits the PivotTable to only those values matching specified criteria

These four areas are identified in the PivotTable shown in Figure 7-21. This PivotTable counts the number of social media posts submitted to Facebook, Instagram, and Twitter during May broken down by the post topic. The social media sites are placed in the Columns area. Values from the Topic field are displayed in the Rows area. The COUNT function is applied to the Post field and displayed in the Values area. The Filters area displays a filter button limiting the PivotTable to data records from the month of May. A table also includes grand totals across all row and column categories.

Figure 7-21Structure of a PivotTable

The PivotTable in Figure 7-21 provides a comprehensive breakdown of where Syrmosta is posting their messages and for what line of clothing products. In May, Syrmosta posted 58 times on social media sites, and more than half of those posts were on Facebook (30 posts). Of the 58 posts, 22 were on Syrmosta’s line of casual attire. Of those 22 posts, 12 were on Facebook, 8 were on Instagram, and 2 were on Twitter.

Reference

Creating a PivotTable

Click anywhere within a data range or Excel table.
On the Insert tab, in the Tables group, click the PivotTable button; or on the Table Tools Design tab, in the Tools group, click the Summarize with PivotTable button.
Specify whether to insert the PivotTable in a new worksheet or at a cell location within an existing worksheet.
Click OK.
Drag fields from the field list on the PivotTable Fields pane and drop them on the Filters, Columns, Rows, or Values area boxes.

Inserting a PivotTable

You can create a PivotTable from any data range or Excel table and insert the PivotTable within a new worksheet or an existing worksheet. To create a PivotTable, click in a data range or an Excel table, and then click the PivotTable button in the Tables group on the Insert tab. Excel reserves a space 3 columns wide by 18 rows tall for the initial PivotTable report. If the worksheet does not have enough empty space, you’ll be prompted to overwrite the cell content

Claire wants to analyze social media posts by site and product line. You’ll create a PivotTable from the data in the Media table for this purpose.

To create a PivotTable from the Media table:

If you took a break at the end of the previous session, make sure the NP_EX_7_Syrmosta workbook is open.

Go to the Media Log worksheet and click cell B4 to select a cell in the Media table.

On the ribbon, click the Insert tab, and then in the Tables group, click the PivotTable button. The Create PivotTable dialog box opens.

Verify that Media appears in the Table/Range box. This specifies the Media table as the source for the PivotTable.

Click the Existing Worksheet option button, and then press TAB to move the insertion point into the Location box. You want to insert the PivotTable into the Data Explore worksheet starting at cell B6.

Click the Data Explore sheet tab, and then in the Data Explore worksheet, click cell B6. The 3-D reference ‘Data Explore’!$B$6 is entered into the Location box. See Figure 7-22.

Figure 7-22Create PivotTable dialog box

Click OK. The empty PivotTable report appears in the Data Explore worksheet. See Figure 7-23.

Figure 7-23Empty PivotTable report

Trouble? If you do not see the PivotTable Fields pane, you need to display it. Click the PivotTable Tools Analyze tab, and then in the Show group, click the Field List button.

The empty PivotTable report section starts from cell B6 in the Data Explore worksheet. When any part of the PivotTable report section is selected, the PivotTable Tools tabs appears on the ribbon. You can select commands to populate and format the PivotTable from these Analyze and Design tabs. The PivotTable Fields pane shows the fields from the Media table. The boxes below the fields list represent the four areas of the PivotTable. You place the fields you want assigned to each area in these boxes.

Like Excel tables, each PivotTable has a unique name. The first PivotTable created is named PivotTable1, the second is named PivotTable2, and so forth. You can give each PivotTable a more descriptive name by entering a new name in the PivotTable Name box in the PivotTable group on the PivotTable Tools Analyze tab. PivotTable names have the same rules as Excel table names: The name can include spaces. The name cannot be any name reserved by Excel for other purposes.

Claire wants you to rename the PivotTable with a more descriptive name. You will change the name now.

To change the PivotTable name:

On the PivotTable Tools Analyze tab, in the PivotTable group, double-click the PivotTable Name box. The default name “PivotTable1” is selected.

Press DELETE, and then type Explore PivotTable as the new name.

Press ENTER. The PivotTable is renamed.

Next you will begin populating the PivotTable with the fields from the Media table.

Creating a PivotTable Layout

The PivotTable layout is created by dragging fields from the field list into one of the four area boxes. This determines the basic structure of the PivotTable. There is no particular order that the areas of the table need to be filled in. Although it’s often best to start with the field containing the data you want to summarize and then expand the PivotTable from there. Excel updates the PivotTable in the report area and does all the calculations to match your selections. Once the PivotTable layout is in place, you can fine-tune its format and appearance.

Claire wants this PivotTable to count the number of posts made to the different social media site broken down by topic. You will start by placing the Post field in the Values area box, followed by the Site field in the Columns areas and the Topic field in the Rows area.

To place fields in the Values, Columns, and Rows areas:

In the PivotTable Fields pane, point to POST in the field list to highlight it, and then drag the Post field into the Values areas box. “Count of POST” appears in the Values box and the PivotTable shows the count of the Posts field. See Figure 7-24.

Figure 7-24Post field added to the Values area

Drag the Site field into the Columns area box. The number of posts is broken down by social media site, showing 332 posts made to Facebook, 303 posts made to Instagram, and 150 posts made to Twitter. These are the same numbers you calculated with the COUNTIF function (refer back to Figure 7-14), but the PivotTable does all those calculations for you.

Drag the Topic field onto the Rows area box. The number of posts for each social media site are now broken out by topic. See Figure 7-25.

Figure 7-25Fields added to the Rows and Columns areas

EX 7-6cModifying the PivotTable Layout

PivotTables are excellent for data exploration because you can quickly change the PivotTable layout to view your data from many different angles. To change the content of the PivotTable, drag fields out of any of the four area boxes to remove them from the table and drop new fields into the area boxes to add them. The PivotTable calculations are automatically updated to reflect the new layout.

Claire wants to see the sum of shares broken down by media site and profit group. You’ll modify the PivotTable to show these calculations.

To modify the PivotTable to show shares by media site and profit group:

Drag TOPIC from the Rows area box and drop the icon on an empty section of the worksheet. The Topic field is removed from the PivotTable.

Drag the PROFIT GROUP field from the field list into the Rows area box to add it to the PivotTable.

Drag Count of POST from the Values area box and drop the icon on an empty section of the worksheet. The Post field is removed from the PivotTable.

Drag the SHARES field from the field list into the Values area box. The sum of the Shares field is added to the PivotTable. PivotTables by default use the SUM function for numeric data placed in the Values area. See Figure 7-26.

Figure 7-26Modified PivotTable

Over the past year, there was a total of 30,452 shares of Syrmosta social media posts. The row totals show the media shares were fairly evenly split between the high-profit and low-profit items, with high-profit items receiving slightly more shares across all media sites.

To nest fields within a PivotTable:

Drag PROFIT GROUP from the Rows area box and drop it into the Columns area box directly above the SITE field. Both the Profit Group field and the Site field appear in the PivotTable columns. Subtotals appear for each profit group and site.

Drag the DATE field from the field list and drop it into the Rows area box to add it to the PivotTable. A new field named Months is added to the PivotTable, which groups the dates in the PivotTable by month. See Figure 7-27. You can expand any month to view daily statistics.

Figure 7-27Nested fields in the PivotTable

In the Rows area of the PivotTable, to the left of the Jan entry, click the plus box to expand the January group. The posts made for dates in January are displayed. See Figure 7-28.

Figure 7-28Grouped field expanded

Click the minus box to left of the Jan entry to collapse the group, showing only the month totals.

Click the Date check box in the field list. The Date field is deselected, removing it from the PivotTable. The Months field remains, leaving only the monthly totals.

Click the Profit Group check box in the field list to remove that field from the PivotTable.

The PivotTable now shows the sum of the Shares field broken down by media site and month. Next you will explore how to filter the PivotTable data.

To add the Topic field to the Filters area:

Drag the TOPIC field from the field list and drop it in the Filters area box. A label and filter button are added to the range B4:C4.

Click the filter button in cell C4, and then click Sportswear from the list of topics.

Click OK to apply the filter to the PivotTable. See Figure 7-29.

Figure 7-29Filtered PivotTable

For more filter options, you can use the filter buttons next to the row and column labels in the PivotTable, choosing which row or column categories to include in the PivotTable. Claire wants you to limit the PivotTable to show only Facebook and Instagram posts for the months of January through March. You’ll use the column and row labels filter buttons to do this.

To filter the column and row categories:

In cell C6, click the Column Labels filter button. The filter menu opens.

Click the Select All check box to deselect it, and then click the Facebook and Instagram check boxes to select them.

Click OK to apply the filter to the column categories. Only Facebook and Instagram posts appear in the PivotTable.

In cell B7, click the Row Labels filter button. The filter menu opens.

Click the Select All check box to deselect it, and then click the Jan, Feb, and Mar check boxes to select them.

Click OK to apply the filter to the row categories. Only January, February, and March posts appear in the PivotTable. See Figure 7-30.

Figure 7-30Filtered column and row categories

On the PivotTable Tools Analyze tab, in the Actions group, click the Clear button, and then click Clear Filters. All of the filters are removed from the PivotTable.

Trouble? If the entire PivotTable disappears, you might have clicked Clear All instead of Clear Filters. To restore the PivotTable, click the Undo button on the Quick Access Toolbar and then repeat Step 7.

As you’ve seen, PivotTables are extremely flexible. With them you can quickly explore data from a variety of views without writing a single formula.

Insight

Choosing a Recommended PivotTable

If you’re not sure what to include in a PivotTable or how to structure it, you can use the Recommend PivotTables tool. To use the tool, select any cell within a data range or Excel table, and then click the Recommended PivotTables button in the Tables group on the Insert tab. A gallery of PivotTable layouts suitable for that data opens. Choose the one you find most useful and relevant. The PivotTable is inserted on a new sheet in the workbook using the layout you selected.

To create a PivotTable that summarizes social media engagement:

Go to the Media Log worksheet and click cell B4 if necessary to select it.

On the ribbon, click the Insert tab, and then in the Tables group, click PivotTable. The Create PivotTable dialog box opens.

Click the Existing Worksheet option button, press TAB, click the Dashboard sheet tab, click cell J11 on the Dashboard worksheet, and then click OK. A blank PivotTable Report is added to the Dashboard worksheet starting from cell J11.

On the PivotTable Tools Analyze tab, in the PivotTable group, click the PivotTable Name box, type Engagement Pivot, and then press ENTER. The PivotTable is renamed.

Drag the VIEWS, COMMENTS, LIKES, SHARES, and ENGAGEMENTS fields from the field list into the Values area box in the order listed. The sum of each field is displayed in the PivotTable.

When a PivotTable contains multiple value fields, Excel organizes those fields into an item called ∑Values, so you can place all value fields within a single location in the PivotTable. In this PivotTable, the ∑Values item is added to the Columns area so that each of the fields appears in a separate column.

Drag ∑Values from the Columns area box and drop it in the Rows area box. The five fields are now placed in separate rows.

Drag the SITE field from the field list into the Columns area box to add the sums for each field by media site. The PivotTable shows the total number of views, likes, comments, shares, and engagements for Facebook, Instagram, and Twitter. See Figure 7-31.

Figure 7-31Sum of social media engagements

To format the five PivotTable calculated value fields:

In cell J13 of the PivotTable, double-click the Sum of VIEWS label. The Value Field Settings dialog box opens so you can set the options for this value field.

Click the Number Format button. The Format Cells dialog box opens and contains only the Number tab.

In the Category box, click Number, enter 0 in the Decimal places box, click the Use 1000 Separator (,) check box, and then click OK to return to the Value Field Settings dialog box.

In the Custom Name box, change the text to Viewed. See Figure 7-32.

Figure 7-32Value field settings dialog Box

Click OK. The title in the PivotTable’s first row changes to Viewed and the calculated sums in the first row have a thousands separator and no decimal places.

Repeat Steps 1, 2, 3, 4, and 5 for the Sum of LIKES, Sum of COMMENTS, Sum of SHARES, and Sum of ENGAGEMENTS value fields, changing their names to Liked, Commented, Shared, and Engaged and changing the number formats to display zero decimal places and include a thousands separator. See Figure 7-33.

Figure 7-33PivotTable with custom labels and number formats

Although you can format PivotTable values using the formatting commands on the Home tab, this formatting is lost if the PivotTable layout changes. Instead, you should format values using the Value Field Settings dialog box, which maintains the formats even when the PivotTable layout changes.

The next PivotTable Claire wants added to the dashboard will count the number of posts by social media site. You’ll create and format this PivotTable, changing the label to “Total Posts.” Rather than creating a new PivotTable from the Media table, you can copy the PivotTable that already exists on the Dashboard worksheet and then edit that new PivotTable.

To create a PivotTable counting the number of social media posts:

In the Dashboard worksheet, copy the range J11:N17 and paste it into cell J20. The Engagement Pivot PivotTable is duplicated.

On the ribbon, click the PivotTable Tools Analyze tab, in the Actions group, click the Clear button, and then click Clear All to remove all the fields from the pasted PivotTable. If prompted, click the Clear PivotTable button.

Place the SITE field into the Columns area of the PivotTable.

Place the POST field in the Values area.

Double-click cell J22 containing the label “Count of Post” to open the Value Field Settings dialog box.

In the Custom Name box, type Total Posts to revise the field label, and then click OK.

On the PivotTable Tools Analyze tab, from the PivotTable group, click the PivotTable Name box, type Total Posts Pivot as the new name, and then press ENTER. See Figure 7-34.

Figure 7-34Total posts by media site

To create a PivotTable of average engagement rates:

In the Dashboard worksheet, copy the range J20:N22 and paste it into cell J25. The Total Posts Pivot PivotTable is duplicated.

Drag Total Posts out of the Values area, and then drag the ENGAGEMENT RATE field into the Values area.

On the ribbon, click the PivotTable Tools Analyze tab, and then in the PivotTable Name box, enter Engagement Rate Pivot as the PivotTable name.

Double-click cell J27 containing the “Sum of ENGAGEMENT RATE” label. The Value Field Settings dialog box opens.

In the list of summary statistics, click Average.

In the Custom Name box, type Engaged Rate as the new name.

Click the Number Format button. The Format Cells dialog box opens.

In the Category box, click Percentage, and then click OK in each dialog box to return to the worksheet. See Figure 7-35.

Figure 7-35Average engagement rate by media site

The Engagement Rate Pivot PivotTable shows that the highest average engagement rates are associated with posts on Instagram (1.36%) followed by Facebook (0.73%). Twitter has the lowest average engagement rate with 0.25%.

Insight

Moving a PivotTable

You can move any PivotTable to a new location in the workbook. To move a PivotTable within its current worksheet, select the entire PivotTable range and then drag the table to a new location, being careful not to overwrite other content in the process. To move a PivotTable to a different worksheet, click anywhere within the PivotTable to select it, and then click the Move PivotTable button in the Actions group on the PivotTable Tools Analyze tab. The Move PivotTable dialog box opens so you can choose the new location for the PivotTable. You can create a new worksheet for the PivotTable or choose an existing worksheet in the workbook. You can also select the entire PivotTable and then use the Cut and Paste buttons in the Clipboard group on the Home tab to move the PivotTable within the workbook.

The last PivotTable Claire wants added to the dashboard will summarize the media posts by values of the Engagement Level field. You will create this PivotTable now.

To create a PivotTable of engagement level vs. media site:

Copy the range J25:N27 and paste it into cell J30.

Remove the Engaged Rate value field from the PivotTable and replace it with the Post field.

Place the ENGAGEMENT LEVEL field in the Rows area.

Click the PivotTable Tools Analyze tab, and then change the name of the PivotTable to Engagement Level Pivot.

Right-click cell J30 containing the label “Count of POST,” and click Value Field Settings on the shortcut menu. The Value Field Settings dialog box opens.

Change the custom name to Engagement Levels and then click OK. See Figure 7-36.

Figure 7-36Engagement Level PivotTable

To rearrange the Engagement Level categories:

Click cell J32 containing the Average category value.

Type Poor as the category name, and then press ENTER. The values for the Poor category are displayed in row 32 and the other categories are shifted down.

Click cell J36 containing the Very Good category value.

Type Excellent as the category name, and then press ENTER. The Engagement Level values are now listed in the order Poor, Average, Good, Very Good, and Excellent.

To set the PivotTable options:

On the PivotTable Tools Analyze tab, in the PivotTable group click the Options button. The PivotTable Options dialog box opens.

On the Layout & Format tab, verify that the For empty cells show check box is selected, and then type 0 as the value to display for blank PivotTable cells.

Click the Autofit column width on update check box to remove the checkmark. The row will remain the same width no matter how the might PivotTables change. See Figure 7-37.

Figure 7-37PivotTable Options dialog box

Click the Totals & Filters tab to display options for PivotTable totals and filters.

Click the Show grand totals for columns check box to deselect it.

Click OK to return to the worksheet and verify that the grand total row has been removed and blank cells are displayed as zeros.

Click cell J25 to select the Engagement Rate Pivot PivotTable.

On the PivotTable Tools Analyze tab, in the PivotTable group, click the Options button, click the Autofit column width on update check box to deselect it, and then click OK. The PivotTable will not be resized.

9. 9.

Click cell J20 to select the Total Posts Pivot PivotTable and then repeat Step 8 so the PivotTable will not resize.

Click cell J11 to select the Engagement Pivot PivotTable, and then repeat Step 8 so that the PivotTable will not resize.

To apply a design style to a PivotTable:

In cell J10, enter USER RESPONSES to label the top PivotTable, and then apply the Accent3 cell style to cell J10.

Click cell J11 to select the Engagement Pivot PivotTable.

3. 3.

On the ribbon, click the PivotTables Tools Analyze tab, and then in the Show group, click the Field Headers button to deselect it. The Column Labels filter button disappears from the PivotTable.

Click the PivotTable Tools Design tab to display commands related to the layout and design of the PivotTable.

In the PivotTable Styles group, click the More button to open the PivotTable Styles gallery, and then in the Medium section, click the Light Yellow, Pivot Style Medium 4 style located in the first row and fourth column of the Medium section.

In the PivotTable Style Options group, click the Banded Rows and Banded Columns check boxes to add gridlines to the PivotTable. See Figure 7-38.

Figure 7-38PivotTable with style and options

Claire wants all the PivotTables on the Dashboard worksheet to have the same look and design. You will repeat the formatting for the remaining PivotTables on the Dashboard worksheet.

To complete the designs of the remaining PivotTables:

In cell J19, enter MEDIA POSTS as the label, and then format that cell using the Accent2 cell style.

Click cell J20 to select the Total Posts Pivot PivotTable.

On the ribbon, click the PivotTable Tools Analyze tab, and then in the Show group, click the Field Headers button to remove the Column Labels filter button.

On the ribbon, click the PivotTable Tools Design tab, in the PivotTable Styles group, click the More button, and then in the Medium section, click the Light Orange, Pivot Style Medium 3 style in the gallery.

In the PivotTable Styles Options group, click the Banded Rows and Banded Columns check boxes to select them.

Repeat Steps 1, 2, 3, 4, and 5 for the Engagement Rate Pivot PivotTable, inserting ENGAGEMENTS in cell J24 with the Accent4 cell style and applying the Lavender, Pivot Style Medium 5 style to the PivotTable.

7. 7.

Repeat Steps 1, 2, 3, 4, and 5 for the Engagement Level Pivot PivotTable, inserting USER RESPONSES in cell J29 with the Accent6 cell style and applying the Ice Blue, Pivot Style Medium 7 style to the PivotTable. See Figure 7-39.

Figure 7-39Completed PivotTables in the Dashboard worksheet

Save the workbook.

To begin building a PivotChart:

If you took a break at the end of the previous session, make sure the NP_EX_7_Syrmosta workbook is open.

Go to the Media Log worksheet and click cell B4 if necessary to select the Media table.

On the ribbon, click the Insert tab, and then in the Charts group, click the PivotChart button. The Create PivotChart dialog box opens.

Click the Existing Worksheet option button, press TAB to move to the Location box, click the Follower History sheet tab, and then click cell B4 in the Follower History worksheet. The reference ‘Follower History’!$B$4 appears in the Location box.

Click OK. An empty PivotTable report and an empty PivotChart appear in the worksheet. See Figure 7-41.

Figure 7-41Empty PivotTable report and PivotChart

When a PivotChart is selected, the PivotChart Tools Analyze, PivotChart Tools Design, and PivotChart Tools Format tabs appear on the ribbon. The Design and Format tabs include the same commands you’ve seen for working with other Excel charts. The Analyze tab includes many of the same commands you’ve seen for working with PivotTables. So, once you know how to work with PivotTables and charts, you’ve already mastered many of the tools applicable to PivotCharts.

PivotCharts are built the same way as PivotTables: by choosing fields from the field list and dropping them into one of four PivotChart areas. Claire wants you to create a PivotChart line chart showing the average number of followers for each media site per month to determine whether the number of followers has increased over the past year. You will create this PivotChart layout now.

Where to start to get The Best Excel Homework Help Service?

To lay out the PivotChart contents:

Drag the FOLLOWERS from the field list into the Values area box. The PivotChart becomes a column chart showing the sum of the Followers field. The PivotTable is also updated to show the value of that sum.

Drag the SITE field from the field list into the Legend (Series) area box. The column PivotChart and PivotTable update to show the sum of the Followers broken down by media site.

Drag the DATE field from the field list into the Axis (Categories) area box. The column PivotChart and PivotTable show the sum of the Followers field for each month of the year. The Date field is grouped, so you can ungroup individual months to view day-to-day values of the Followers field.

In the upper-left corner of the PivotChart, right-click the Sum of FOLLOWERS cell, and then click Value Field Settings on the shortcut menu. The Value Field Settings dialog box opens. Claire wants to the view the average of the Followers field.

Click Average in the list of statistical functions, and then in the Custom Name box, change “Average of Followers” to Followers followed by a blank space (to avoid a name conflict with the Followers field.

Click the Number Format button to open the Format Cells dialog box, click Number in the Category box, set the value of the Decimal place box to 0, and then click the Use 1000 Separator (,) check box to select it.

Click OK in each dialog box to return to the PivotChart. See Figure 7-42.

Figure 7-42PivotChart of the average followers per month

Not every Excel chart type can be created as a PivotChart. You can create PivotCharts from only the Column, Line, Pie, Bar, Area, Surface, Radar chart types, and from Combo charts created from the preceding chart types. Claire thinks this data would be better displayed as a line chart. You’ll change the chart type now.

To change the chart type:

On the ribbon, click the PivotChart Tools Design tab to show commands for changing the design of the selected PivotChart.

In the Type group, click the Change Chart Type button. The Change Chart Type dialog box opens.

Click Line in the list of chart types, and then click OK. The PivotChart changes from a column chart to a line chart.

Next to the chart, click the Chart Elements button , click the Chart Title check box, type Followers by Month as the chart title, and then press ENTER. The chart title appears at the top of the chart.

Click the Chart Elements button , click the Gridlines arrow, and then click the Primary Major Vertical check box. Vertical gridlines appear on the chart.

In the Chart Elements menu , click the Legend arrow, and then click Bottom. The legend moves to the bottom of the chart. See Figure 7-43.

Figure 7-43Line PivotChart showing average followers per month

To name and move the PivotChart:

Click cell B4 to make the PivotTable the active object in the workbook.

On the ribbon, click the PivotTable Tools Analyze tab, and then in the PivotTable group, click the PivotTable Name box and enter Followers by Month Pivot as the name of the PivotTable.

Click the PivotChart to select it, click the PivotChart Tools Analyze tab on the ribbon, and then in the Actions group, click the Move Chart button. The Move Chart dialog box opens.

Verify that the Object in option button is selected, click the Object in box, click Dashboard as the worksheet to place the PivotChart in, and then click OK. The PivotChart moves to the Dashboard worksheet.

In the Dashboard worksheet, move and resize the PivotChart to cover the range B24:H36. Claire is not going to revise the layout of this PivotChart, so you can hide the field buttons.

On the PivotChart Tools Analyze tab, in the Show/Hide group, click the Field Buttons button to hide the field buttons. See Figure 7-44.

Figure 7-44Line PivotChart moved to the Dashboard worksheet

To create the pie PivotChart showing the breakdown of user engagements:

Go to the Media Log worksheet, click the Insert tab on the ribbon, and then in the Charts group, click the PivotChart button. The Create PivotChart dialog box opens.

2. 2.

Place the PivotChart on the existing User Engagement worksheet in cell B4, and then click OK. A blank PivotTable and a PivotChart appear on the User Engagement worksheet.

In the field list, click the Views, Likes, Comments, and Shares check boxes to add those fields to the Values area box.

Drag the ∑Values item from the Legend (Series) box to the Axis (Categories) box.

On the ribbon, click the PivotChart Tools Design tab, and then in the Type group, click the Change Chart Type button. The Change Chart Type dialog box opens.

Click Pie as the chart type, and then click OK. The PivotChart changes to a pie chart.

Double-click cell B5, change the value in the Custom Name box to Viewed, and then click OK.

Repeat Step 7 to change the label in cell B6 to Liked, the label in cell B7 to Commented, and the label in cell B8 to Shared. See Figure 7-45.

Figure 7-45Pie PivotChart of the engagement totals

You’ll move the pie chart to the Dashboard sheet and format it for Claire’s report.

To format the pie chart PivotChart:

Click the PivotTable Tools Analyze tab, in the PivotTable group, click the PivotTable Name box, type Engagement Pie Pivot as the PivotTable name, and then press ENTER.

Click the Engagement Pie Pivot PivotChart to select it, click the PivotChart Tools Analyze tab on the ribbon, in the Actions group, click the Move Chart button.

Move the PivotChart to the Dashboard worksheet, and then move and resize the Engagement Pie Pivot PivotChart to cover the range P11:S21.

4. 4.

On the ribbon, click the PivotChart Tools Analyze tab, and then in the Show/Hide group, click the Field Buttons button to the hide the field buttons on the chart.

Change the chart title to User Responses.

Click the PivotChart Tools Design tab, in the Chart Layouts group, click the Quick Layout button, and then click Layout 6 to add data callouts to the pie chart.

In the Chart Styles group, click Style 1 to add a white border around each slice. See Figure 7-46.

Figure 7-46Formatted pie chart on the dashboard

The pie chart shows that about 12% of all user response are due to comments and shares. This is a respectable percentage, but is the engagement rate constant through the year? Recall that the company had instituted a new ad campaign to improve its social media presence. Claire wants you to add a line chart to the dashboard, showing the average engagement rate by month broken down by media site.

To create a PivotChart of engagement rate per month:

Go to the Media Log worksheet, click the Insert tab on the ribbon, and then in the Charts group, click the PivotChart button.

Place the PivotTable/PivotChart on the Engagement History worksheet starting at cell B4.

Place the SITE field in the Legend (Series) area box, place the DATE field in the Axis (Categories) area box, and then place the ENGAGEMENT RATE field in the Values area box.

Change the PivotChart chart type to a Line chart.

Right-click cell B4, and then click Value Field Settings on the shortcut menu. The Value Field Settings dialog box opens.

Click Average in the list of functions.

7. 7.

Click the Number Format button, click Percentage in the Category box, and then click OK in each dialog box to return to the worksheet.

On the ribbon, click the PivotTable Tools Analyze tab, and then in the PivotTable group, enter Engagement Rate per Month Pivot in the PivotTable Name box.

You’ll move the line chart to the Dashboard worksheet and finish formatting it.

To place the line chart in the Dashboard worksheet:

Move the line PivotChart to the Dashboard worksheet, and then resize the chart to cover the range P23:S36.

On the PivotChart Tools Analyze tab, in the Show/Hide group, click the Field Buttons button to hide the field buttons.

Click the PivotChart Tools Design tab, in the Chart Layouts group, click the Quick Layout button, and then click the Layout 3 layout to place the legend at the bottom of the chart.

Change the chart title to Engagement Rate per Month.

Close the PivotChart Fields pane. See Figure 7-47.

Figure 7-47Chart of engagement rate per month

To add a slicer for the Topic field to the Engagement Pivot PivotTable:

On the Dashboard worksheet, click cell J11 to select the Engagement Pivot PivotTable.

On the ribbon, click the Insert tab, and then in the Filters group, click the Slicer button. The Insert Slicers dialog box opens, displaying a list of fields from the PivotTable.

Click the TOPIC check box, and then click OK. The TOPIC slicer appears on the Dashboard worksheet.

Move and resize the TOPIC slicer to cover the range B4:H7.

On the Slicer Tools Options tab, in the Buttons group, change the value in the Columns box to 2 to arrange the slicer buttons in two rows and two columns.

In the TOPIC slicer, click the Business Attire button to filter the Engagement Pivot PivotTable to show only posts regarding business attire. The filtered PivotTable shows there were 100,603 engagements for posts dealing with business attire. See Figure 7-48.

Figure 7-48Business Attire posts

Click the Nightwear, Casual Attire, and Sportswear slicer buttons to filter the summary statistics for the other topic categories in the PivotTable.

Click the Clear Filter button (or press ALT+C ) to clear the filters and redisplay summary statistics for all topics.

To apply a slicer to all the PivotTables in the dashboard:

Click in the TOPIC slicer to make sure it is selected.

On the Slicer Tools Options tab, in the Slicer group, click the Report Connections button. The Report Connections (TOPIC) dialog box opens.

Click the check box for every PivotTable listed except the Explore PivotTable from the Data Explore worksheet. See Figure 7-49.

Figure 7-49Report Connections (TOPIC) dialog box

Click OK to apply the slicer to all selected PivotTables.

In the TOPIC slicer, click the Business Attire, Casual Attire, Nightwear, and then Sportswear buttons, and then confirm that all of the PivotTables and PivotCharts displayed in the Dashboard worksheet are filtered by the slicer.

Click the Clear Filter button (or press ALT+C ) to clear the filters from all the PivotTables and PivotCharts on the dashboard.

To review the income statement for the Athena I:

Open the NP_EX_8-1.xlsx workbook located in the Excel8 > Module folder included with your Data Files, and then save the workbook as NP_EX_8_Athena in the location specified by your instructor.

In the Documentation worksheet, enter your name and the date.

Go to the Income worksheet and review its contents and formulas. See Figure 8-5.

Figure 8-5Income statement for the Athena I bicycle

As itemized in the Income worksheet, the company projects that it will sell 3,000 Athena Is for $1,400 each, generating $4.2 million in revenue. The variable expenses involved in producing those bicycles is $2.85 million and the company’s fixed expenses are about $1.16 million. Based on this sales volume, the company would generate $193,500 in revenue.

To use Goal Seek to find the break-even point for the Athena I:

On the ribbon, click the Data tab. In the Forecast group, click the What-If Analysis button, and then click Goal Seek. The Goal Seek dialog box opens with the cell reference in the Set cell box selected.

In the Income worksheet, click cell B27 to replace the selected cell reference in the Set cell box with $B$27. The absolute reference specifies the Net Income cell as the cell whose value you want to set.

Press TAB to move the insertion point to the To value box, and then type 0 indicating that the goal is to set the net income value in cell B27 to 0.

Press TAB to move the insertion point to the By changing cell box, and then click cell B5 in the Income worksheet to enter the cell reference $B$5. The absolute reference specifies that you want to reach the goal of setting the net income to 0 by changing the units produced and sold value in cell B5.

Click OK. The Goal Seek Status dialog box opens once Excel finds a solution.

Click OK to return to the worksheet. The value 2,570 appears in cell B5, indicating that the company must produce and sell about 2,570 Athena I bicycles to break even. See Figure 8-6.

Figure 8-6Sales required to break even

Click cell B5 and enter 3000 to return to the original units produced and sold projection.

To set up the one-variable data table to examine the impact of changing sales volume:

In cell D3, enter Break-Even Analysis as the table label, merge and center the range D3:G3, and then format the text with the Heading 3 cell style.

Select the range D4:G4, enter Units Sold, Revenue, Expenses, and Net Income as the labels, center the text in the selected cells, and then apply the Accent 2 cell style to the selected cells.

In cell D5, enter the formula =B5 to reference the input cell to be used in the data table.

In cell E5, enter the formula =B25 to reference the result cell that displays the total revenue.

In cell F5, enter the formula =B26 to reference the total expenses.

In cell G5, enter the formula =B27 to reference the company’s net income.

Format the range E5:G5 using the Accounting format with no decimal places.

Format the range D5:G5 with the 40% – Accent1 cell style and add a bottom border.

In the range D6:D14, enter Units Sold values from 1000 to 5000 in 500-unit increments, and then format the selected cells with the Comma style and no decimal places.

Select cell D5. See Figure 8-8.

Figure 8-8Setup for the one-variable data table

A one-variable data table is based on either a row input cell matching the values placed in the first row of the data table or a column input cell matching the values in the data table’s first column. You want to find the revenue, expenses, and net income values for different units sold figures, so you will match the value from cell B5 to the units sold values in the first column of the data table.

To complete the one-variable data table:

Select the range D5:G14 containing the cells for the data table.

On the Data tab, in the Forecast group, click the What-If Analysis button, and then click Data Table. The Data Table dialog box opens.

Press TAB to move the insertion point to the Column input cell box, and then click cell B5 in the Income worksheet to indicate that all the result values in the data table first column should be matched with cell B5. The absolute reference $B$5 appears in the Column input box. See Figure 8-9.

Figure 8-9Data Table dialog box

4. 4.

Click OK. Excel completes the data table by entering the revenue, expenses, and net income for each units sold value specified in the data table’s first column.

Use the Format Painter to copy the format from cell B25 and apply it to the result values in the range E6:G14.

Select cell G14. See Figure 8-10.

Figure 8-10Completed one-variable data table

To set up the two-variable data table:

In cell I3, enter Net Income Analysis, merge and center the range I3:N3, and then format the merged range with the Heading 3 cell style.

In cell I4, enter Sales Price, and then merge and center the range I4:N4.

In the range J5:N5, enter the possible sales prices $1,000 through $1,800 in increments of $200.

4. 4.

Copy the values in the range D6:D14, and then paste them into the range I6:I14.

Select the two sets of input values in the nonadjacent range J5:N5, I6:I14, and then format the selected range with the 40% – Accent1 cell style.

Add a right border to the range I6:I14 and a bottom border to the range J5:N5.

In two-variable data tables, the reference to the result cell is placed in the upper-left corner of the table at the intersection of the row and column input values. In this case, you’ll enter a formula in cell I5 that references the company’s net income.

In cell I5, enter the formula =B27. The current net income value $193,500 is displayed in cell I5. See Figure 8-14.

Figure 8-14Setup for the two-variable data table

The two-variable data table is generated using the same Data Table command used with the one-variable data table, except that you specify both the row input cell (matched to the values in the first row of the table) and the column input cell (matched to the values in the table’s first column).

To generate the result values:

Select the range I5:N14 containing the row input values, the column input values, and the reference to the result cell.

On the ribbon, click the Data tab. In the Forecast group, click the What-If Analysis button, and then click Data Table. The Data Table dialog box opens.

In the Row input cell box, type B6 to reference the sales price from the income statement.

4. 4.

In the Column input cell box, type B5 to reference the number of units sold from the income statement.

Click OK. The data table values appear in the range J6:N14.

Use the Format Painter to copy the formatting from cell G14 to the range J6:N14.

Click cell J6 to deselect the highlighted range. See Figure 8-15.

Figure 8-15Completed two-variable data table

To apply a custom format to cell I5:

Right-click cell I5, and then click Format Cells on the shortcut menu (or press CTRL+1). The Format Cells dialog box opens.

If necessary, click the Number tab, and then in the Category box, click Custom.

3. 3.

In the Type box, select the format code text, and replace it with the text string “Units Sold” (including the quotation marks) as the custom text to display in the cell. See Figure 8-16.

Figure 8-16Format Cells dialog box

Click OK. The text “Units Sold” appears in cell I5 even though the cell’s underlying formula is =B27.

Trouble? If “Units Sold” does not appear in cell I5, you probably didn’t include the quotation marks in the custom format. Repeat Steps 1, 2, 3, and 4, making sure that you include both opening and closing quotation marks.

To create a chart of the two-variable data table:

Select the range I6:N14. You’ll plot this range on a scatter chart. You did not select the unit prices in row 5 because Excel would interpret these values as data values to be charted, not as labels.

On the ribbon, click the Insert tab. In the Charts group, click the Insert Scatter (X, Y) or Bubble Chart button , and then click the Scatter with Straight Lines chart subtype (the second chart in the second row of the Scatter section).

3. 3.

Move and resize the chart so that it covers the range I15:N27.

Remove the chart title, and then position the chart legend to the right of the chart.

Change the fill color of the chart area to Ice Blue, Accent 1, Lighter 80%, and then change the fill color of the plot area to White, Background 1. See Figure 8-17.

Figure 8-17Chart of net income values

The chart shows a different trend line for each of the five possible values for unit price. However, the prices are not listed in the chart, and Excel uses generic series names (Series1, Series2, Series3, Series4, and Series5). To display the unit prices rather than the generic names in the chart, you must add the unit price values as series names.

To edit the chart series names:

On the Chart Tools Design tab, in the Data group, click the Select Data button. The Select Data Source dialog box opens.

In the Legend Entries (Series) box, click Series1, and then click Edit. The Edit Series dialog box opens.

With the insertion point in the Series name box, click cell J5 to insert the reference =Income!$J$5, and then click OK. The Select Data Source dialog box reappears with the Series1 name changed to $1,000. See Figure 8-18.

Figure 8-18Select Data Source dialog box

Repeat Steps 2 and 3 to edit Series2 to use cell K5 as the series name, edit Series3 to use cell L5 as the series name, edit Series4 to use cell M5 as the series name, and edit Series5 to use cell N5 as the series name. All the chart series are renamed to match the sales price values in row 5 of the two-variable data table.

Click OK. The Select Data Source dialog box closes, and the legend shows the renamed series.

On the Chart Tools Design tab, in the Chart Styles group, click the Change Colors button, and then click Monochromatic Palette 10 in the Monochromatic section. The line colors change to shades of gray, reflecting the increasing value of the unit price. See Figure 8-19.

Figure 8-19Final chart of net income values

Save the workbook.

The chart shows how different unit prices will affect the relationship between sales volume and net income; where each line crosses the horizontal axis indicates the break-even point for that sales price. For example, the $1,600 line (the second highest of the five lines) crosses the horizontal axis near 1,800 units, indicating that with sales price of $1,600, the company will have to sell about 1,800 Athena I bicycles to break even.

To define names for the income statement values:

If you took a break after the previous session, make sure the NP_EX_8_Athena workbook is open, and the Income worksheet is the active sheet.

In the Income worksheet, select the range A5:B6,A18:B21,A25:B27. You’ll define names for each of these cells.

3. 3.

On the ribbon, click the Formulas tab, and then in the Defined Names group, click the Create from Selection button. The Create Names from Selection dialog box opens.

Click the Left column check box to insert a checkmark, if necessary, and then click any other check box that has a checkmark to deselect it.

Click OK. The cell values in column B are named using the labels in the corresponding cells in column A.

Click cell A1 to deselect the range.

To add the Status Quo scenario:

On the ribbon, click the Data tab. In the Forecast group, click the What-If Analysis button, and then click Scenario Manager. The Scenario Manager dialog box opens. No scenarios are defined yet.

Click Add. The Add Scenario dialog box opens.

In the Scenario name box, type Status Quo, and then press TAB. The cell reference in the Changing cells box is selected.

The Scenario Manager refers to input cells as “changing cells” because these worksheet cells contain values that are changed under the scenario. Changing cells can be located anywhere in the current worksheet. You can type the range names or locations of changing cells, but it’s faster and more accurate to select them with the mouse.

The changing cells for each of the four scenarios are:

Cell B5: Units Sold
Cell B6: Sales Price per Unit
Cell B18: Salaries and Benefits
Cell B19: Shipping and Distribution
Cell B20: Stocking and Storage
Cell B21: Miscellaneous

You’ll specify these cells as the changing cells for the Status Quo scenario.

To specify the changing cells for the Status Quo scenario:

With the Changing cells box still active, select the nonadjacent range B5:B6,B18:B21. Absolute references for the range appear in the Changing cells box. These are the input cells.

Press TAB to select the default text in the Comment box, and then type Scenario assuming no change in values in the Comment box. See Figure 8-21.

Figure 8-21Edit Scenario dialog box

Click OK. The Scenario Values dialog box opens so you can enter values for each changing cell you entered in the Changing cells box in the Edit Scenario dialog box. The Status Quo scenario values already appear in the dialog box because these are the current values in the workbook. See Figure 8-22.

Figure 8-22Scenario Values dialog box

Click OK. The Scenario Manager dialog box reopens with the Status Quo scenario listed in the Scenarios box. See Figure 8-23.

Figure 8-23Scenario Manager dialog box

You’ll use the same process to add the remaining three scenarios on Roy’s list—Expanded Operations, Reduced Operations, and Reduced Pricing.

To add the remaining scenarios:

Click Add. The Add Scenario dialog box opens.

In the Scenario name box, type Expanded Operations, press TAB twice to go the Comment box, and then type Scenario assuming expanded operations in the Comment box.

Note that the nonadjacent range you selected for the Status Quo scenario appears in the Changing cells box. Because you want to use the same set of changing cells, you didn’t edit the range.

Click OK. The Scenario Values dialog box for the Expanded Operations scenario opens.

Enter the following values, pressing TAB to move from one input box to the next: 3500 for Units_Sold, 1400 for Sales_Price_per_Unit, 755000 for Salaries_and_Benefits, 235000 for Shipping_and_Distribution, 200000 for Stocking_and_Storage, and 225000 for Miscellaneous.

Trouble? If the Scenario Manager dialog box reopens, you probably pressed ENTER instead of TAB. Make sure that the Expanded Operations scenario is selected in the Scenarios box, click Edit, and then click OK to return to the Scenario Values dialog box. Enter the remaining values in the scenario, being sure to press TAB to move to the next input box.

Click Add. The Add Scenario dialog box reopens so you can enter the next scenario.

Trouble? If the Scenario Manager dialog box reopens, you clicked OK instead of Add. Click Add in the Scenario Manager dialog box to return to the Add Scenario dialog box, and then continue with Step 6.

Type Reduced Operations in the Scenario name box, press TAB twice, type Scenario assuming reduced operations in the Comment box, and then click OK.

7. 7.

Enter 2250 for Units_Sold, 1400 for Sales_Price_per_Unit, 575000 for Salaries_and_Benefits, 150000 for Shipping_and_Distribution, 125000 for Stocking_and_Storage, and 180000 for Miscellaneous.

Click Add to enter the final scenario.

Type Reduced Pricing in the Scenario name box, press TAB twice, type Scenario assuming a price reduction in the Comment box, and then click OK.

Enter 4000 for Units_Sold, 1300 for Sales_Price_per_Unit, 616500 for Salaries_and_Benefits, 190000 for Shipping_and_Distribution, 160000 for Stocking_and_Storage, and 230000 for Miscellaneous.

Click OK. The Scenario Manager dialog box reappears with all four scenarios listed.

Where to start to get The Best Excel Homework Help Service?

Engin

Author Since: 08/08/2020

Hi,
My name is Engin. I am the founder of pureassignment, a traveller, fitness lover and a cat person.