+1(978)310-4246 credencewriters@gmail.com
Select Page

I’m working on a data analytics exercise and need an explanation and answer to help me learn.

Calculate frequency and create a frequency distribution for the products in the database using the input range and bin range steps demonstrated in Chapter 4.

What does this chart tell us about our data?

Next, calculate relative frequency using the instructions in Chapter 4 and create a second chart.

What can we conclude? Why is relative frequency important to help understand your data?

Sales Units
Month
January
February
March
April
May
June
July
August
September
October
November
December
A
7.792
7.268
7.049
7.560
8.233
8.629
8.702
9.215
8.986
8.654
8.315
7.978
B
5.554
3.024
5.543
5.232
5.450
3.943
5.991
3.920
4.753
4.746
3.566
5.670
Product
C
3.105
3.228
2.147
2.636
2.726
2.705
2.891
2.782
2.524
3.258
2.144
3.071
D
3.168
3.751
3.319
4.057
3.837
4.664
5.418
4.085
5.575
5.333
4.924
6.563
E
10.350
8.965
6.827
8.544
7.535
9.070
8.389
7.367
5.377
7.645
8.173
6.088
Using VLOOKUP + IF
Sales Units
Month
January
February
March
April
May
June
July
August
September
October
November
December
A
7.792
7.268
7.049
7.560
8.233
8.629
8.702
9.215
8.986
8.654
8.315
7.978
B
5.554
3.024
5.543
5.232
5.450
3.943
5.991
3.920
4.753
4.746
3.566
5.670
Product
C
3.105
3.228
2.147
2.636
2.726
2.705
2.891
2.782
2.524
3.258
2.144
3.071
D
3.168
3.751
3.319
4.057
3.837
4.664
5.418
4.085
5.575
5.333
4.924
6.563
E
10.350
8.965
6.827
8.544
7.535
9.070
8.389
7.367
5.377
7.645
8.173
6.088
Sales Lookup
Month
Product
April
E
Sales
8.544
Sales Units
Month
January
February
March
April
May
June
July
August
September
October
November
December
A
7.792
7.268
7.049
7.560
8.233
8.629
8.702
9.215
8.986
8.654
8.315
7.978
B
5.554
3.024
5.543
5.232
5.450
3.943
5.991
3.920
4.753
4.746
3.566
5.670
Product
C
3.105
3.228
2.147
2.636
2.726
2.705
2.891
2.782
2.524
3.258
2.144
3.071
D
3.168
3.751
3.319
4.057
3.837
4.664
5.418
4.085
5.575
5.333
4.924
6.563
E
10.350
8.965
6.827
8.544
7.535
9.070
8.389
7.367
5.377
7.645
8.173
6.088
Using VLOOKUP + MATCH
Sales Lookup
Month
Product
Sales
September
A
8.986
Using INDEX + MATCH
Sales Units
Month
January
February
March
April
May
June
July
August
September
October
November
December
A
7.792
7.268
7.049
7.560
8.233
8.629
8.702
9.215
8.986
8.654
8.315
7.978
B
5.554
3.024
5.543
5.232
5.450
3.943
5.991
3.920
4.753
4.746
3.566
5.670
Product
C
3.105
3.228
2.147
2.636
2.726
2.705
2.891
2.782
2.524
3.258
2.144
3.071
D
3.168
3.751
3.319
4.057
3.837
4.664
5.418
4.085
5.575
5.333
4.924
6.563
E
10.350
8.965
6.827
8.544
7.535
9.070
8.389
7.367
5.377
7.645
8.173
6.088
Sales Lookup
Month
Product
Sales
August
B
3.920
Models, and Decisions
Third Edition
Chapter A1
Basic Excel Skills
Slide – 1
Basic Excel Skills
â€¢ Opening, saving, and printing files
â€¢ Using workbooks and worksheets
â€¢ Selecting cells and ranges
â€¢ Inserting/deleting rows and columns
â€¢ Entering and editing text, data, and formulas
â€¢ Formatting data (number, currency, decimal)
â€¢ Working with text strings
â€¢ Formatting data and text
â€¢ Modifying the appearance of a spreadsheet
Slide – 2
Excel 2016 Ribbons
Windows
Mac
Slide – 3
â€¢ Analysis Toolpak (Data Analysis), which contains
a variety of tools for statistical computation
â€¢ Solver, which is used for optimization
â€“ If they do not appear in the Data tab, see the
instructions in Appendix A1.
Slide – 4
Excel Formulas
â€¢ Common mathematical operators are used.
â€“ subtraction (âˆ’)
â€“ multiplication (*)
â€“ division
(/)
â€“ exponentiation ( ïƒ™ )
Slide – 5
Relative and Absolute References
â€¢ Cell references can be relative or absolute. Using a dollar sign before
a row and/or column label creates an absolute reference.
â€“ Relative references: A2, C5, D10
â€“ Absolute references: \$A\$2, \$C5, D\$10
â€¢ Using a \$ sign before a row label (for example, B\$4) keeps the
reference fixed to row 4 but allows the column reference to change if
the formula is copied to another cell.
â€¢ Using a \$ sign before a column label (for example, \$B4) keeps the
reference to column B fixed but allows the row reference to change.
â€¢ Using a \$ sign before both the row and column labels (for example,
\$B\$4) keeps the reference to cell B4 fixed no matter where the formula
is copied.
Slide – 6
Example A1.1: Implementing PriceDemand Models in Excel
Two models for predicting demand as a function of price
Linear
D = a âˆ’ bP
Formula in cell B8:
= \$ B\$4 âˆ’ \$ B\$5*\$ A8
Nonlinear
D = cP âˆ’ d
Formula in cell E8:
= \$ E \$4* D8 ^ âˆ’\$ E \$5
Note how the absolute addresses are used so that as these formulas are
copied down, the demand is computed correctly.
Slide – 7
Copying Formulas
Formulas in cells can be copied in many ways.
â€¢ Use the Copy button in the Home tab, then use the
Paste button
â€¢ Use Ctrl – C, then Ctrl – V
â€¢ Drag the bottom right corner of a cell (the fill handle)
across a row or column
Slide – 8
Other Useful Excel Tips
â€¢ Split Screen
â€¢ Column and Row Widths
â€¢ Displaying Formulas in Worksheets
â€¢ Displaying Grid Lines and Column Headers for
Printing
â€¢ Filling a Range with a Series of Numbers
Slide – 9
Basic Excel Functions
â€¢
=MIN(range)
â€¢
=MAX(range)
â€¢
=SUM(range)
â€¢
=AVERAGE(range)
â€¢
=COUNT(range)
â€¢
=COUNTIF(range,criteria)
â€“ Excel has other useful COUNT-type functions: COUNTA counts the number of
nonblank cells in a range, and COUNTBLANK counts the number of blank cells in
COUNTIFS(range1,criterion1,range2,criterion2,…range_n,criterion_n)
finds the number of cells within multiple ranges that meet specific criteria for each
range.
Slide – 10
Example A1.2: Using Basic Excel
Functions
Slide – 11
Other IF-Type Functions
â€¢ SUMIF, AVERAGEIF, SUMIFS, and AVERAGE
IFS can be used to embed IF logic within
mathematical functions.
â€¢ For instance, the syntax of SUMIF is
â€“ SUMIF(range, criterion, [sumrange]). â€œSum rangeâ€ is
an optional argument that allows you to add cells in a
different range.
Slide – 12
SUMIF Example
â€¢ In the Purchase Orders database, to find the total
cost of all airframe fasteners, use
=SUMIF(D4:D97,”Airframe fasteners”, G4:G97)
Slide – 13
Functions for Specific Applications
â€¢ Net Present Value (or discounted cash flow) measures the worth of a
stream of cash flows, taking into account the time value of money.
n
NPV = ïƒ¥
t =0
â€¢ Excel function:
ft
(1 + i )
t
=NPV(rate,value1,value2,…)
â€“ F is the cash flow (\$).
â€“ Rate (i) is the discount rate.
â€“
value1,value2,… are equally-spaced payments or
income values.
â€“ t is a time period.
Slide – 14
Example A1.3: Using the NPV
Function
Cell B8:
=NPV(B6,C4:H4)-B5
Slide – 15
Insert Function
â€¢ Click the Insert
function button f x .
â€¢ You may type in a
description or
search.
Example for
COUNTIF
function:
Slide – 16
Date and Time Functions
â€¢ Excel can display a date in a variety of formats, such
as2/14/17 or 14-Feb-17. Choose the standard date
format ( for example, 2/14/17 ) by selecting Date in
the Number formatting box or select a custom format by
selecting Custom in the Number box.
â€¢
DATEDIF(startdate, enddate, time unit)
â€“ Time unit can be â€œyâ€, â€œmâ€, or â€œdâ€.
â€“
DATEDIF(4/26/89, 2/14/17, â€œyâ€) will return 27
(years), while DATEDIF ( 4/26/89, 2/14/17, â€œmâ€ )
will return 333 (months).
Slide – 17
Range Names
â€¢ A range name is a descriptive label assigned to a
cell or range of cells. There are several ways to
create range names in Excel.
â€“ Name box
â€“ Create from Selection
â€“ Define Name
Slide – 18
Example A1.5: Using the Name Box
â€¢ Define range names for each of the numerical cells
that correspond to the labels on the left. That is, we
will name cell B3 Fixed cost, cell B4 Unit variable cost,
and so on. Click on cell B3; in the Name box,
type the name Fixed_cost (note the underscore;
blanks are not permitted), and then press Enter.
Slide – 19
Example A1.6: Using Create from
Selection
â€¢ Use the text labels to the left of the numerical
inputs as the range names. First, highlight the
range A3:B5. Then, on the Formulas tab, choose
Create from Selection. The box for the left column
will automatically be checked. Click OK.
Slide – 20
Example A1.7: Using Define Name
â€¢ Select cell B3. Click Define Name on the
Formulas tab. This will bring up a dialog that
allows you to enter a range name. Click OK.
Slide – 21
Name Manager
â€¢ Displays a summary of range names for editing. (Note:
The Name Manager button in the Formulas tab is only
available in Windows. On a Mac, click Define Name to see
a list of range names.)
Slide – 22
Applying Range Names
â€¢ This replaces the cell references by the names. Click on
the drop-down arrow next to Define Name and select
Apply Names . . .. Select all the names you wish to use
and click OK. In the figure, the original formula for
cell B7(=B3+B4*B5) now displays the names.
Slide – 23
VALUE Function
looks numerical but is actually expressed as text; this
is often true of data from U.S. government Web sites.
â€¢ VALUE ( text ) converts text data to numerical values.
Slide – 24
Paste Special
â€¢ Copy the range of cells of
interest and click on the cell
where you want to paste the
results. Examples:
â€“ To paste only the values in
cells (not the formulas),
select Values and then click
OK.
â€“ To transpose data in
columns to rows and vice
versa, use Transpose.
Slide – 25
Example A1.8: Currency Conversion
â€¢ Assume that 1 euro = \$1.117.
To convert euros into U.S.
dollars, first copy the data to
column C. Select cell C3
corresponding to the
conversion factor. Next, select
the range of data in column C
and open the Paste Special
dialog. Select Multiply.
Slide – 26
Concatenation
â€¢ To concatenate means to join. In many applications, you
might wish to take text data that are in different columns
and join them together (for example, first and last
names). The Excel function
CONCATENATE(text1, text 2, . . . , text 30) can be
used to join up to 30 text strings into a single string.
â€“ Suppose that cell A1 contains the last name Smith, and
cell B1 contains the first name John. Then
CONCATENATE(B1,” “,A1) will result in the text
string John Smith.
Slide – 27
Error Values
â€¢
#DIV/0! -A formula is trying to divide by zero.
â€¢
#N/A -â€œNot available,â€ meaning that the formula could not return
a result.
â€¢
#NAME? -An invalid name is used in a formula.
â€¢
#NUM! -An invalid argument is used in a function, such as a negative
number in SQRT.
â€¢
#REF!
â€¢
#VALUE! -Excel cannot compute a function because of an invalid
-A formula contains an invalid cell reference.
argument.
Slide – 28
IFERROR Function
â€¢ IFERROR(value, value_if_error) displays a
specific value if an error is present.
â€¢ For example, if you are computing A1/ B1, then if
B1 is zero or blank, then
= IFERROR(A1/B1, â€œ â€)
will display a blank cell instead of #DIV/0!;
Slide – 29