1 / 16

Excel in M.E. – part i

Keith A. Woodbury Mechanical Engineering University of Alabama. Excel in M.E. – part i. Using cell references can be difficult when working with formulas and spreadsheets References like “A1” and “G47” don’t mean much

viveka
Download Presentation

Excel in M.E. – part i

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. Keith A. WoodburyMechanical EngineeringUniversity of Alabama Excel in M.E. – part i

  2. Using cell references can be difficult when working with formulas and spreadsheets • References like “A1” and “G47” don’t mean much • By naming the cell it becomes easier to identify a parameter in a formula or function call • Assigning natural names to parameters makes it easier to find mistakes Naming Variables

  3. Naming Variables - Example Not a good way to code formulas. Although ‘F2’ reveals which cells are involved in the computation, a look at the formula does not immediately reveal what variables are involved.

  4. Named Ranges Instead, use names for the variables Choose names that are longer than two characters so Excel doesn’t confuse them with cell addresses Choose “P_1” instead of “P1” Or “Press” instead of “P”

  5. Naming Variables - Example Create “named cells” to use in formulas This is on the “Formulas” ribbon in Excel 2007 Highlight cells with names and values in adjacent cells

  6. Confirm location of labels

  7. Now the labels can be used as parameters in the formula

  8. Named Range - Example Named Cells can also be ranges (vectors); in this case the named cells are in columns or rows

  9. Named Range - Example Now the formula is readily human-readable Excel uses the entry in the vector on the same row as the formula

  10. It is easier when working with formulas in Excel to document them. • Formulas are shown next to computed result • Mistakes can be spotted quickly • Documentation should also make printed copies of the file understandable to knowledgeable person (teacher or colleague) • This process involves copying formulas into cells or using Excel’s Tools tab. Documenting Spreadsheets

  11. Suggested Convention… Use the first column for labels for the values Use the second column for the given value or formula to compute the result Put the units of the quantity in the third column Use the fourth column to cut-and-paste the text of the formula, or to provide additional explanatory information

  12. Documenting SS - Example First use ‘F2’ to highlight show the formula for the cell

  13. Now use keystroke “SHIFT+HOME” to highlight the cell entry

  14. Then use “CTRL+c” to cut the text to the clipboard buffer, followed by “ESC” to get out of “F2” mode

  15. Now move over to the fourth colum, put a leading tick-mark (‘) so Excel will treat as text (not formula), then use “CTRL-v” to paste the clipboard contents

  16. Highlight formula cell “F2” to get equation displayed “SHIFT-HOME” to highlight “CTRL-c” to copy “ESC” to get out of “F2” mode Move to fourth column Tick mark to treat paste as text “CTRL-v” to paste the formula Secret Jedi Keystroke sequence

More Related