KUAS Engineering

Week 04 — Number processing

Evaluation

Up to 10 points can be gained towards your final score by completing the in-class assignment on Friday.

Preparation

1. Complete the self-preparation assignment at home before next class

Watch at least videos 1 to 11 (inclusive). If any of the topics are not familiar to you, open a blank Excel workbook and try to reproduce the examples shown in the videos for yourself. (These videos are also embedded at the end of this page, in case you prefer to watch them without leaving your browser.)

2. Check your proficiency with Excel using the self-assessment questionnaire

1. Answer each question in the self-assessment questionnaire as honestly as you can. (Do not press 'submit' when you are finished.)
2. Check your scores.
3. Revise those topics having the lowest scores.
4. When you have made progress with understanding or skill, update your scores.
5. Repeat from step 2 until you feel comfortable with most (or all) of the topics.

On Thursday evening, press 'submit'. In class on Friday we will check which topics were difficult for everyone.

To succeed at the in-class assignment for this class you should know how to

• enter data and perform basic editing and formatting of cell content
• change the data type of a row or column
• use conditional formatting to change the appearance of a cell
• create charts
• use functions such as SUM() and conditional functions such as SUMIF()
• identify when an absolute reference is necessary, and use one
• import CSV files

You can also use Internet search engines to find online tutorials and other educational materials relating to PPT, or even check our library to see if they have a book on the subject. In other words, use any resources you can to achieve your learning goals.

What you will learn from this class

• how to create spreadsheets and use them to manage numeric data
• how to quickly enter series of data such as dates
• how to perform computations with the data in a spreadsheet
• how to visualise the data in the spreadsheet
• how to sort and filter the data in a spreadsheet
• how to manage scrolling in a large spreadsheet
• how to import and export numeric data
• how to export a chart from a spreadsheet to another application
• how to perform an engineering simulation using a spreadsheet

Notes

Excel is a program for creating tables of data, and performing computation and analysis on that data. It is based on the very old idea of a spreadsheet, which was a large piece of paper used by accountants. Known data was entered into spaces on the spreadsheet and then calculations were performed to calculate new values. The process continued for as many iterations as was required to calculate the final, useful information.

Excel's principles are exactly the same. You enter the data you know into cells, and use formulas to compute data you don't know in other cells. Excel takes care of figuring out the order in which the computations should be performed and the unknown data generated.

Excel calls its spreadsheets workbooks. A workbook consists of a number of rows and columns. At the intersection of every row and column there is a cell that can contain data. The cells inside a spreadsheet are therefore laid out in a square pattern. The columns are given letters and the rows are given numbers. Every cell therefore has a 'name' or 'coordinate' defining where it is located; the official term is reference. The first column is called A and the first row is called 1, so the top-left cell in the spreadsheet has the reference A1. The cell to its right is B1 and the cell below it is A2.

How many cells are in a spreadsheet? 17,179,869,184 arranged in 1,048,576 rows of 16,384 columns. Excel is very good at hiding the empty ones from you and so you'll never even see them unless you go looking.

What happens after the column names run out of letters? Like in a cinema, after column Z come columns AA, AB, and AC. After column AZ come columns BA, BB, and BC. After ZZ come columns AAA, AAB, and AAC. (There are not enough columns to ever reach ZZZ.)

The interface

The UI should be very recognisable to anyone having experience with MS Word and PowerPoint. Excel has the same search feature as PowerPoint and Word, so it is easy to look up tools by name or description. I shall dare even to not reproduce its ribbons here.

The least familiar part of Excel might be the way references work. I shall therefore use the space to explain them instead of describing pretty pictures of the user interface.

References

References work like map coordinates, with a letter for horizontal position and a number for vertical position. They come in two types: relative and absolute.

Relative references

Relative references are what most people (and almost all beginners) use almost all of the time. One or more letters (naming a column) and one or more digits (naming a row) make up a reference. Even though they are called “relative”, they still identify a cell by its absolute position in the array of cells. So what makes them relative?

The relativity comes from their behaviour when they are used in a formula inside a cell. Formulae can move, either because they are copied and pasted or because rows and cells are inserted or deleted. When a formula moves, Excel looks at the relative positions of (distance between) the original position and the new position. The difference is added to the column letter and row number in the reference. The effect is that the name of the referenced cell changes, so that the formula continues to reference a value stored at the same position relative to wherever the formula happens to be.

Copying the formula B3+D3 and pasting it two rows below the original position causes the references within it to change to B5+D5. Moving that new formula one column to the right causes the references within it to change to C5+E5. This is bad when many formulae need to refer to a single cell, such as an interest rate, no matter where they may be moved.

Absolute references

By placing a $ in front of any letter or digit in a reference, it becomes absolute. This does not change how it refers to a cell, only how it behaves when the formula that it is part of is moved. In the case of absolute parts of references, they do not have the “distance” between the original and new position of the formula added to them. No matter where the formula is moved to, the absolute parts of the reference will always remain the same.

Take our formula containing B3+D3 and change it to $B$3+D3, then perform the same two moves on it. Moving it down two rows changes it to $B$3+D5, and moving that new formula right one column changes it to $B$3+E5. The second relative cell referenced has moved to remain in the same relative position as the formula, whereas the first absolute reference has not.

Change our formula to $B3+D$3 and perform the same two moves on it. Moving it down two rows changes it to $B5+D$3, and moving that new formula right one column changes it to $B5+E$3.

Videos

These 12 videos cover most of the essentials of Excel (assuming you already know how to use MS Word and PowerPoint). Use them to understand what features are there, and then explore the full capabilities of the interesting or useful features in more depth on your own.