Excel Business Analytics Free Course - Forecasting, Linear Programming, Simulation & more. - Basic Excel Business Analytics #17: FREQUENCY Array Function for Frequency Distribution & Histogram

FREQUENCY Array Function Histogram & Cumulative Frequency Distr. Distribution Download files: https://people.highline.edu/mgirvin/AllClasses/348/348/AllFilesBI348Analytics.htm Learn how to take 25,000 rows of web site transactional data and use the FREQUENCY Array Function to create Frequency Distribution and a Cumulative Frequency Distribution & a Histogram for the continuous quantitative variable Revenue. Learn how to create a helper column for our data set to calculate Revenue using VLOOKUP to lookup price and a discount rate to calculate the correct Revenue amount. Also learn about the correct categories that the FREQUENCY Array Function creates. Topics: 1) (00:19) Introduction to Data set and our goal which is to create a Frequency Distribution and Histogram 2) (01:27) Create formula for calculating Revenue when we have transactional records with product name, units and revenue discount and a separate table with product prices. The formula uses VLOOKUP and the ROUND function to calculate price times units times one minus the discount rate. 3) (04:07) How to determine the class limits (upper and lower limit for counting revenue amounts) and the interval between the upper and lower limit. We use the MIN, MAX and COUNT functions. We also create a formula for range and a formula to determine the number of classes ( 2^k greater than n ). We finally determine the lower limit of the first class and the increment for all classes. 4) (07:54) How to use FREQUENCY Array Function for counting between an upper and lower limit with the goal of creating a Frequency Distribution. This is an Array Function that requires the special key stroke: Ctrl + Shift + Enter. 5) (13:16) Formula for Relative Frequency or Percent Frequency 6) (13:51) Formulas for Cumulative Frequency 7) (14:36) Formula for Percent Cumulative Frequency 8) (15:34) Combination Chart for a Histogram to show Frequency and Line Chart to show Percent Cumulative Frequency. Two Chart examples that show many important charting tricks to create a chart that articulates an accurate and effective massage, including Custom Number Formatting to show “nothing”. Download Excel File Not: After clicking on link, Use Ctrl + F (Find) and search for “Highline BI 348 Class” or for the file name as seen at the beginning of the video.