Excel 3 - CS 170 Spreadsheets Functions and Conditional...

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

View Full Document Right Arrow Icon

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

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

Unformatted text preview: CS 170: Spreadsheets Functions and Conditional Formatting Kristian Stout [email protected] Expectations • PMT ▫ =PMT(rate, NPER, PV, FV, type) ▫ Calculate a periodic payment • VLOOKUP ▫ VLOOKUP(lookup_value,table_range,col_index_ num,range_lookup) ▫ 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.

Page1 / 4

Excel 3 - CS 170 Spreadsheets Functions and Conditional...

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

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