From jacobson@math-cs.cns.uni.edu Wed Oct 11 22:49:12 2006 Date: Wed, 11 Oct 2006 17:44:13 -0500 (CDT) From: Mark Jacobson To: 810-022-01-FALL@uni.edu Subject: [810-022-01-FALL] The Price Excel Label for CarLoan.xls Hi 022 students, http://www.cns.uni.edu/~jacobson/c022.html I changed my column label from Price to thePrice, because the newest versions of Microsoft Excel have PRICE as: (1,287,126,970.00), i.e. as a negative number greater than 1 billion -1,287,126,970.00 B K L -------- ------------------- ------- thePrice 91 Mercury Sable $10,500 <---- row 2 Down 88 Nissan Pulsar NX $6,350 Loan 90 Toyota Camry $8,950 Interest 88 Dodge Lancer ES $6,299 Years 87 BMW 325 $7,959 Payment 91 Chev Camaro $6,796 88 Mazda MX6 $8,500 Price is a BAD name unless you have a much older version of Microsoft Excel. If you use carPrice or Car Price or thePrice, you will be okay. Price gets changed to PRICE and is NO GOOD!!! ----- ----- In Excel 97, this was NOT a problem, but in Excel 2003 and after it is not possible to use Price as a name, apparently. The CarLoan.xls Excel spreadsheet you are using to go with the Lesson10 handout, which prepares you for the Precious Metals assignment with Gold, Silver, Diamonds, Tin, etc. http://www.cns.uni.edu/~jacobson/022/Metals/Metals.html Price per ounce Total ounces Total cost can be changed to Pricing per ounce Ounces Cost For column B and cells B3, B4 and B5 of the Metals.xls assignment. Pricing per ounce 22 Ounces 8 Cost 176 Pricing per ounce 22 Ounces 8 Cost =Pricing_per_ounce*Ounces Notice that the Insert menu, Define Name command will suggest the name with underscores under_scores to make the name one unit, without any blank spaces. So when I Insert menu, Name Define... command I do it to the RIGHT of the cell with Pricing per ounce, which is cell C3. Then I click the Add button to actually assign the suggested name Pricing_per_ounce to cell C3. Then I click OK and its done and the formula can say =Pricing_per_ounce * Ounces instead of = C3 * C4 (If I defined Ounces too). Car Price for CarLoan.xls would mean that Car_Price would be the suggested name, if you did Insert menu, Name Define... and clicked the Add button, and then OK. See you in lab for the hands-on class on Friday. IPO = Input Processing Output I I I O O O Function justOddCharacters( theWord As String ) As String str = "" P For i = 1 to Len( theWord ) Step 2 P str = str & Mid(theWord, i, 1) P Next i P justOddCharacters = str O End Function ----------------------------------------------------------- Sub demoFunction_justOddCharacters() Range("A1").Value = justOddCharacters("ABCDEFGHIJKL") inputData = "Ghostbusters" outputResult = justOddCharacters( inputData ) Range("A2").Value = outputResult Range("A3").Value = justOddCharacters( Range("C1").Value ) End Sub ----------------------------------------------------------- A B C 1 ACEGIK UNI Panthers rule!! 2 Gotutr 3 UIPnhr ue! - - - - - - - - - - - - - - - - I = INPUT which UNI Panthers rule!! is, along with ABCDEFGHIJKL - - - - - - - - - - - - - - - - - - - - - - and Ghostbusters - - - - - - What is the output of the function for each one of the INPUTS? What processing had to happen to get the OUTPUT from the given INPUT???? I The function takes a string like Ghostbusters as INPUT. O The function producs a string like Gotutr as OUTPUT. P Processing is HOW it gets from I to O. The statements INSIDE the function show the HOW to get from the starting point ABCDEFGHIJK to the ----------- desired result ACEGIK ------ I O Function descriptiveFunctionNameFocusOnWHATitDoes( input ) As output Type HOW details of getting I transformed to desired O descriptiveFunctionNameFocusOnWHATitDoes = O (desired RESULT is O as in Output) End Function - Mark