1 / 17

Using Spreadsheet Text Functions and Data Validation to Create Algorithmic Questions

Using Spreadsheet Text Functions and Data Validation to Create Algorithmic Questions. Mary Geddie & Richard Griffin University of Tennessee Martin. Open Word and type the following:.

gzifa
Download Presentation

Using Spreadsheet Text Functions and Data Validation to Create Algorithmic Questions

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Using Spreadsheet Text Functions and Data Validation to Create Algorithmic Questions Mary Geddie & Richard Griffin University of Tennessee Martin

  2. Open Word and type the following: A company purchased equipment for $325,000 on January 2, 2010. The company expects the equipment to last for ten years or 80,000 hours of operation, with an estimated salvage value of $10,000. During 2010, the equipment was in operation for 7,000 hours. In 2011 the equipment was in operation for 8,800 hours.

  3. Open Word and type the following: RequirementsCompute the depreciation expense relating to the equipment for 2010 and 2011 using the following depreciation methods: a. Straight-line b. Double-declining-balance c. Units-of-production

  4. Step 1: separate text from variables • Begin with a Word paragraph. • With the paragraph highlighted, set a Right tab at 6 inches (Home / Paragraph dialog box / Indents and Spacing tab / Tabs). • Read through problem, identifying each variable that may be changed in alternate versions. • Immediately after each variable, hit the Enter key. Insert a Tab immediately before each variable. Keep any spaces with the text so that the variable is stripped. • Any characters that Excel recognizes as number formatting, such as $’s or commas, are kept with the variable

  5. Result of step 1, in Word

  6. Open Excel and Copy Paragraph into Cell A2

  7. Create variable names • Insert a new column B • Type variable names (useful to create range names) Cost Purchase year Life in years Life in hours Salvage (leave 2nd use of Purchase year blank) Purchase year hours Second year Second year hours

  8. Step 2: Express numeric variables as numbers and text. • For calculations, replace any text variables with numbers. • Grammarians will want the final result to follow the rule that numbers <= 10 are spelled out (first, five, etc.) • Use column D for the text version of values • Nested IF statement with a blank as the final “False” =IF(B4=10,"ten",IF(B4=9,"nine",IF(B4=8,"eight",IF(B4=7,"seven",IF(B4=6,"six",IF(B4=5,"five",IF(B4=4,"four",IF(B4=3,"three",IF(B4=2,"two","")))))))))

  9. Result of step 2:

  10. Step 3: Add range names • Highlight Variables in Column C. (only highlight the first instance) • Formulas/ Create from Selection (in the Defined Names group) / Left Column

  11. Step 4: express relations between variables as calculations or data validation limitations. • Replace all the years in column C (except Purchase year in cell C3) with formulas • Create data validation rules to maintain the internal consistency of the problem • Can’t have more than 365 x 24 hrs of operation in any year • Can’t have salvage in excess of cost • (view example sheet)

  12. Step 4 • I use cell color to distinguish cells • require data entry cell uses style “Note” • Formula result cell style “Bad” • All cells except data entry cells s/b locked (after completion of spreadsheet) • I only used data validation per se for data entry cells • Try entering a 1-year life in cell C4 • Min / Max columns provide for internal consistency for formula cells. • Additional data in rows 20 & 21 helps determine min/max values -- another way to change the problem

  13. Step 4 • I used Rand and RandBetween functions in the formula cells • Which you might not want to do! • Set spreadsheet to limit recalculation if using • Formulas / Calculation options Analysis of formula for Life_in_Hours: • RandBetween(min,max) • Round(result of RandBetween) • Min(Rounded result, max value)

  14. Step 5: Concatenate the text strings • I use Concatenate function as shown in A28 of example spreadsheet (some people use & ) • Cells in columns C and D are not text strings, and must be converted to text using the TEXT function when they appear in the list of cells • Syntax is =TEXT(value,format_text). • format_textargument must be enclosed in quotation marks. • Column D variables are converted to text strings with “aaaa” formatting, • Column C are all dollar values and use “$0,000” formatting. • Excel Help can help! • To proof-read the CONCATENATE formula, notice that the variables from Column A begin with A2 and are listed in order by row, interspersed with either the cell from column C or the cell from column Dfor that row, but never both column C and D from the same row.

  15. Step 6: • Copy into test pool as a question! • Hit Calculate Now on formula bar and enter that result as a question, too!

  16. Step 7: Solutions • Use the Range Names in your formulas • Easy and fun! • Type = and start typing the word, then select the Range Name from the popup menu • Type: =(Cost-Salvage)/Life_in_years • Range Name advantage is automatic absolute referencing (great for MC version!)

  17. MC Questions • See 2nd tab “multiple choice” • First part of question stem is in cell A1 • Tied to previous spreadsheet • Requirement statement is with MC answers • Also tied to previous spreadsheet • Example: A3 uses Purchase_year variable • Column E documents why the MC answers are good distractors, and are for the question writers, not for students!

More Related