Excel Features - Simple Formulas Unit cost = Units = Total...

Info iconThis preview shows pages 1–4. Sign up to view the full content.

View Full Document Right Arrow Icon
Simple Formulas Unit cost = $10 Units = 20 Total cost = Copying Formulas Item Cost Volume Total Cost Hammers $5.00 5 Nails $0.02 100 Screws $0.03 200 Relative and Absolute Cell References Item Cost Volume 1 Volume 2 Volume 3 Total Cost 1 Total Cost 2 Total Cost 3 Hammers $5.00 5 10 8 Nails $0.02 100 150 125 Screws $0.03 200 50 150 Range Names Revenue $100 Cost $90 Profit Enter formula for the total cost of hammers and copy it for nails and screws Define range names for Revenue and Cost and enter formula for Profit in terms of these range names
Background image of page 1

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full DocumentRight Arrow Icon
Enter formula for Total Cost 1for hammers, copy down for nails and screws, then copy across for Total Cost 2 and Total Cost 3
Background image of page 2
Excel Functions Project Time 1 12 2 7 3 23 4 4 5 14 6 18 Minimum Maximum Total Average 7 9 8 13 9 4 10 21 The IF, AND and OR Functions Critical time (CT) for projects = 15 Project Time Time > CT? 1 12 2 7 3 23 4 4 5 14 6 18 7 9 8 13 9 4 10 21 The SUMPRODUCT function Project Time Cost/Day Proj. Cost 1 12 5 60 2 7 7 49 3 23 2 46 4 4 5 20 5 14 8 112 6 18 6 108 7 9 1 9 8 13 4 52 9 4 6 24 10 21 7 147 Total Cost 627 Table lookup using the VLOOKUP function Volume Discount Order Discount =VLOOKUP(lookup_value,table_a 0 0.0% 17 10 2.0% 5 20 4.0% 10 30 5.0% 23 34 9 Use Excel functions to calculate the minimum project time (MIN), the maximum project time (MAX), the total project time (SUM) and the average project time (AVERAGE). Hint: First define a range name for the array of times. Use the status bar on the bottom of the sheet to check your results. Use the status bar to find the average time for the first five projects and then for the even numbered projects. Use the IF function as follows:
Background image of page 3

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full DocumentRight Arrow Icon
Image of page 4
This is the end of the preview. Sign up to access the rest of the document.

Page1 / 15

Excel Features - Simple Formulas Unit cost = Units = Total...

This preview shows document pages 1 - 4. Sign up to view the full document.

View Full Document Right Arrow Icon
Ask a homework question - tutors are online