Exploring Microsoft Excel 2016 Series Editor Mary Anne

Exploring Microsoft® Excel® 2016 Series Editor Mary Anne Poatsy Mulbery|Davidson Series Created by Dr. Robert T. Grauer

Chapter 8 Statistical Functions Analyzing Statistics Copyright © 2017 Pearson Education, Inc.

Objectives • Use Conditional Math and Statistical Functions • Calculate Relative Standing with Statistical Functions • Measure Central Tendency • Load the Analysis Tool. Pak • Perform Analysis Using the Analysis Toolpak • Create a Forecast Sheet Copyright © 2017 Pearson Education, Inc.

Objective 1: Use Conditional Math and Statistical Functions Skills: Use the SUMIF, AVERAGEIF, and COUNTIF Functions Use the SUMIFS, AVERAGEIFS, and COUNTIFS Functions Enter Math and Statistical Functions Copyright © 2017 Pearson Education, Inc.

Use Conditional Math and Statistical Functions • Excel Math and Statistical functions: §SUMIF—calculates the total of a range of values when a specified condition is met =SUMIFS(sum_range, criteria_range 1, criteria_range 2, criteria 2…) §AVERAGEIF—calculates the average of a range when a specified condition is met =AVERAGEIFS(average_range, criteria_range 1, criteria_range 2, criteria 2… ) §COUNTIF—counts the number of cells in a range when a specified condition is met =COUNTIFS(criteria_range 1, criteria_range 2, criteria 2…) Copyright © 2017 Pearson Education, Inc.

Use Conditional Math and Statistical Functions • Excel Math and Statistical functions: §SUMIFS—calculates the total value of cells in a range that meet multiple criteria =SUMIFS(sum_range, criteria_range 1, criteria_range 2, criteria 2…) §AVERAGEIFS—calculates the average value of a range that meet multiple criteria =AVERAGEIFS(average_range, criteria_range 1, criteria_range 2, criteria 2…) §COUNTIFS—counts the number in a range that meet multiple criteria =COUNTIFS(criteria_range 1, criteria_range 2, criteria 2…) Copyright © 2017 Pearson Education, Inc.

Use Conditional Math and Statistical Functions Copyright © 2017 Pearson Education, Inc.

Objective 2: Calculate Relative Standing with Statistical Functions Skills: Use the RANK and PERCENTRANK Functions Use the QUARTILE and PERCENTILE Functions Copyright © 2017 Pearson Education, Inc.

Calculate Relative Standing with Statistical Functions • Excel ranking functions: §RANK. EQ—identifies a value’s rank omitting the next rank when tie values exist =RANK. EQ(number, ref, [order]) §RANK. AVG—identifies the rank of a value but assigns an average rank when identical values exist =RANK. AVG(number, ref, [order]) Copyright © 2017 Pearson Education, Inc.

Calculate Relative Standing with Statistical Functions Same Salary RANK. AVG RANK. EQ Copyright © 2017 Pearson Education, Inc.

Calculate Relative Standing with Statistical Functions • Excel ranking functions: §PERCENTRANK. INC—displays a value’s rank as a percentile of the range, where values range from 0 to 1 inclusive =PERCENTRANK. INC(array, x, [significance]) §PERCENTRANK. EXC—displays a value’s rank as a percentile of the range, where values range from 0 to 1 exclusive =PERCENTRANK. EXC(array, x, [significance]) Copyright © 2017 Pearson Education, Inc.

Calculate Relative Standing with Statistical Functions PERCENTRANK. INC Copyright © 2017 Pearson Education, Inc. PERCENTRANK. EXC

Calculate Relative Standing with Statistical Functions • Excel ranking functions: §QUARTILE. INC—identifies the value at a specific quartile including 0 and 4 =QUARTILE. INC(array, quart) §QUARTILE. EXC—identifies the value at a specific quartile excluding 0 and 4 =QUARTILE. EXC(array, quart) Copyright © 2017 Pearson Education, Inc.

Calculate Relative Standing with Statistical Functions QUARTILE. EXC QUARTILE. INC Copyright © 2017 Pearson Education, Inc.

Calculate Relative Standing with Statistical Functions • Excel ranking functions: §PERCENTILE. INC—identifies the value at a specific percentile including 0 and 1 =PERCENTILE. INC(array, k) §PERCENTILE. EXC—identifies the value at a specific percentile excluding 0 and 1 =PERCENTILE. EXC(array, k) Copyright © 2017 Pearson Education, Inc.

Calculate Relative Standing with Statistical Functions PERCENTILE. EXC PERCENTILE. INC Copyright © 2017 Pearson Education, Inc.

Objective 3: Measure Central Tendency Skills: Use the Standard Deviation Function Use the Variance Function Use the CORREL Function Use the FREQUENCY Function Copyright © 2017 Pearson Education, Inc.

Measure Central Tendency • Statistical groups: • Population—dataset that contains all the data • Sample—smaller portion of the population §Central tendency functions: • Variance—measures a dataset’s dispersion • Standard deviation—measures of how far the data sample is spread around the mean Copyright © 2017 Pearson Education, Inc.

Measure Central Tendency • Standard deviations functions: §STDEV. P—calculates the standard deviation based on the population =STDEV. P(number 1, number 2) §STDEV. S—calculates the standard deviation based on a sample =STDEV. S(number 1, number 2) Copyright © 2017 Pearson Education, Inc.

Measure Central Tendency • Variance functions: §VAR. P—calculates the variance based on the population =STDEV. P(number 1, number 2) §VAR. S—calculates the variance based on a sample =STDEV. S(number 1, number 2) Copyright © 2017 Pearson Education, Inc.

Measure Central Tendency STDEV. S VAR. S Copyright © 2017 Pearson Education, Inc.

Measure Central Tendency • CORREL—determine the strength of a relationship between two variables =CORREL(array 1, array 2) CORREL Copyright © 2017 Pearson Education, Inc.

Measure Central Tendency • FREQUENCY—determine the frequency distribution of a dataset =FREQUENCY(data_array, bins_array) FREQUENCY Copyright © 2017 Pearson Education, Inc.

Objective 4: Load the Analysis Tool. Pak Skills: Load the Analysis Tool. Pak Copyright © 2017 Pearson Education, Inc.

Load the Analysis Tool. Pak Data Analysis Add -in added to Ribbon Select Analysis Tookpak Add-in Copyright © 2017 Pearson Education, Inc.

Objective 5: Perform Analysis Using the Analysis Tool. Pak Skills: Perform Analysis of Variance (ANOVA) Calculate COVARIANCE Create a Histogram Copyright © 2017 Pearson Education, Inc.

Perform Analysis Using the Analysis Tool. Pak Copyright © 2017 Pearson Education, Inc.

Perform Analysis Using the Analysis Tool. Pak -1484. 57 Copyright © 2017 Pearson Education, Inc.

Perform Analysis Using the Analysis Tool. Pak Copyright © 2017 Pearson Education, Inc.

Objective 6: Create a Forecast Sheet Skills: Create a Forecast Sheet Copyright © 2017 Pearson Education, Inc.

Create a Forecast Sheet Forecast sheet Copyright © 2017 Pearson Education, Inc.

Summary Additional Excel functions: • Math and statistical related functions: §SUMIF, AVERAGEIF, COUNTIF, SUMIFS, AVERAGEIFS, and COUNTIFS • Relative standing functions, which formed various groups: §RANK, PERCENTRANK, QUARTILE, and PERCENTILE • Descriptive statistical functions: §STDEV, VAR, CORREL, and FREQUENCY • Inferential statistical functions: §ANOVA, COVARIANCE, Histograms, and Forecast sheets Copyright © 2017 Pearson Education, Inc.

Questions ? Copyright © 2017 Pearson Education, Inc.

Copyright All rights reserved. No part of this publication may be reproduced, stored in a retrieval system, or transmitted, in any form or by any means, electronic, mechanical, photocopying, recording, or otherwise, without the prior written permission of the publisher. Printed in the United States of America. Copyright © 2017 Pearson Education, Inc.