v1pB.pdf

21 views1 pages
Published on 21 Apr 2013
School
York University
Department
Electrical Engineering and Computer Science
Course
EECS 1520
Part B – Construct Excel formulas [8 points]
Use only the data shown to answer the following questions.
All ranges have been named using the labels that appear in the worksheets.
The graphics below are taken from an Inventory/Sales system.
The left worksheet includes parameters:
Categories are used to select the Rate to apply to calculate Markup.
In the right worksheet:
Code and Cost hold data entered by the user.
Code includes the category of each item as the last character
and the Stockroom number as the last digit.
Category, Markup, Price, and Stockroom are calculated.
Categories Rate
B 100%
G 125%
M 110%
W 150%
Code Cost Category Markup Price Stockroom
123W $ 3.40 W $ 5.10 $ 8.50 3
254B $ 9.84 B $ 9.84 $ 19.68 4
256W $ 5.60 W $ 8.40 $ 14.00 6
255G $ 12.39 G $ 15.49 $ 27.88 5
274B $ 4.49 B $ 4.49 $ 8.98 4
358M $ 0.95 M $ 1.05 $ 2.00 8
568M $ 9.52 M $ 10.47 $ 19.99 8
Notes to Markers:
Each underline represents 1 point.
Each underline represents ½ point.
1) Write an Excel formula to calculate the values in the Price column. [2]
= Cost + Markup
2) Write an Excel formula (do not use IF or LOOKUP) that will produce the values in the
Stockroom column. [5]
There are many ways of selecting the character:
RIGHT (LEFT (Code ,3) ,1)
LEFT (RIGHT (Code ,2) ,1)
MID (Code, 3, 1) – for this alternative add 1 point for overall correctness
3) A column in an Excel worksheet named Letter Grade contains this formula:
=IF(Score<80,"B",IF(Score<70,"C",IF(Score<60,"D",IF(Score<50,"F","A"))))
What will appear in Letter Grade when Score is 45?
B
Unlock document

This preview shows half of the first page of the document.
Unlock all 1 pages and 3 million more documents.

Already have an account? Log in

Document Summary

Part b construct excel formulas [8 points] Use only the data shown to answer the following questions. All ranges have been named using the labels that appear in the worksheets. The graphics below are taken from an inventory/sales system. Categories are used to select the rate to apply to calculate markup. Code and cost hold data entered by the user. Code includes the category of each item as the last character and the stockroom number as the last digit. Each underline represents point: write an excel formula to calculate the values in the price column. = cost + markup: write an excel formula (do not use if or lookup) that will produce the values in the. There are many ways of selecting the character: Mid (code, 3, 1) for this alternative add 1 point for overall correctness. A column in an excel worksheet named letter grade contains this formula:

Get OneClass Grade+

Unlimited access to all notes and study guides.

YearlyMost Popular
75% OFF
$9.98/m
Monthly
$39.98/m
Single doc
$39.98

or

You will be charged $119.76 upfront and auto renewed at the end of each cycle. You may cancel anytime under Payment Settings. For more information, see our Terms and Privacy.
Payments are encrypted using 256-bit SSL. Powered by Stripe.