Solutions

Solutions - Solutions to Excel Proficiency Exercises the...

Info iconThis preview shows pages 1–4. 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

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: Solutions to Excel Proficiency Exercises the Excel Proficiency Exercise problems. For more information see the document named "Excel Proficiency Exercises". This Solutions workbook contains suggested solutions to Solutions to Excel Proficiency Exercises Multiplication Table Problem: Mixed Addressing The Problem Create a multiplication table in the space below by entering a single formula in the upper left-hand cell and copying that one formula to fill the rest of the table. You will need to use mixed addressing in your formula. 1 2 3 4 5 6 7 8 9 10 1 2 3 4 5 6 7 8 9 10 The Solution The formula should multiply the cell in the row that holds "1" with the cell in the column that holds "1". But dollar signs in the formula must "fix" the row and column references without keeping the formula from changing where necessary. For example, in the formula =B$27*$A28 the B$27 reference fixes row 27 while letting the column adjust in a relative manner. $A28 fixes column A while letting the row adjust relatively. 1 2 3 4 5 6 7 8 9 10 1 1 2 3 4 5 6 7 8 9 10 2 2 4 6 8 10 12 14 16 18 20 3 3 6 9 12 15 18 21 24 27 30 4 4 8 12 16 20 24 28 32 36 40 5 5 10 15 20 25 30 35 40 45 50 6 6 12 18 24 30 36 42 48 54 60 7 7 14 21 28 35 42 49 56 63 70 8 8 16 24 32 40 48 56 64 72 80 9 9 18 27 36 45 54 63 72 81 90 10 10 20 30 40 50 60 70 80 90 100 Solutions to Excel Proficiency Exercises Olive Oil Pricing Problem: IF Statements & the SumProduct Function IF statement syntax: Calculate the cost of various quantities of olive oil. We start with a simpler price schedule than in the problem so you can see how this works. If the price schedule were: Cell Names first 500gallons at $23.00 First500 ='Olive Oil'!$C$10 additional gallons at $20.00 Price_for_500 ='Olive Oil'!$E$10 Price_for_Over500 ='Olive Oil'!$E$11 Quantity1 ='Olive Oil'!$C$15 Cost of 1600gallons is $33,500.00 Cost of 483gallons is $11,109.00 Price_for_500*Quantity1, Cost of 2001 gallons is $41,520.00 First500*Price_for_500 + (Quantity1-First500)*Price_for_Over500) For the price schedule in the problem: first 500gallons at...
View Full Document

Page1 / 6

Solutions - Solutions to Excel Proficiency Exercises the...

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