This preview has intentionally blurred sections. Sign up to view the full version.View Full Document
Unformatted text preview: CS 170: Spreadsheets Functions and Conditional Formatting Kristian Stout
[email protected] Expectations
▫ =PMT(rate, NPER, PV, FV, type) ▫ Calculate a periodic payment • VLOOKUP
▫ Use a lookup table to find a value • Looking up to the “left”
▫ Uses index and match functions
▫ MATCH will find a row # for a value
▫ INDEX( TABLE_RANGE, ROW, COLUMN) Planning Spreadsheets
1) What is our scenario ? ▫ Database or Data Analysis ? 2) What inputs and outputs will we need ?
▫ What is being “grouped” or examined ? What is our subject ? 3) What tasks need to be performed ?
4) What built in functions will be necessary to perform this task ? Problem / Solution 1
For this problem you will use a single spreadsheet to help you keep track of your current inventory at different warehouses. Your sheet will need to track the Warehouse Name (a text input), Qty on Hand (a numeric input), Cost Per Unit (a currency input), Freight Costs (a currency input, total costs for the entire warehouse), Damages (a currency input, total damaged for the entire warehouse), Salaries (a currency input) and finally whether that warehouse needs to reorder soon called Reorder? (a calculated field that display “Yes” if the Qty On Hand is less than 1000. Otherwise it displays “No” ) Finally, have summaries of the above data as follows: ( sum of Qty On Hand, average of Cost Per Unit, sum of Freight Cost, sum of Damages and sum of Salaries ) ...
View Full Document
This note was uploaded on 02/21/2012 for the course 198 170 taught by Professor Kristianstout during the Fall '10 term at Rutgers.
- Fall '10