Sample Excel Exercises

Exercise 1

You have a monthly income of €1100. Your monthly outgoings are Rent, €500, Food, €300, Electricity, €40, Phone, €60, and Cable TV, €30. Make out a clear worksheet with the Monthly Income, the Monthly Expenses listed and summed, the remainder (what’s left over each month) calculated, and the amount left over per day (assuming 30 days in a month).

Exercise 2

Create the worksheet below. Type in the labels, the student numbers, the test results (which are out of 100), and the test weights. The total marks and the class averages for each test must be calculated. The class average for test 1 is simply the average of the marks obtained in that test. Be careful calculating the total marks - you must use the test weights. The first test is worth a quarter of the marks, the second is worth a quarter, while the third is worth a half. Changing the weights in cells C13 to D13 should change the Total values.

The black borders around cells can be added using the Borders toolbar button . Select the cells you want a border around and the select  from the drop-down menu.

Now format the test weights so that they are shown as percentages. Simply select the cells, choose FORMAT|CELLS, and format them as “Percentage”.

Select the cells from A4 to E9 and choose DATA|SORT. Select that you wish to sort according to the ‘Total’ column and that you want the results sorted in Descending order. You should end up with

Exercise 3

You are looking to buy a car. You are considering two options: to buy a second hand car and keep it for 10 years, or to buy a new car and keep it for 3. The details of the costs of each option are given below. The calculated values are shown in Bold. The depreciation per year is simply the difference between the purchase price and the resale price divided by the number of years. The total running costs per year is the sum of the service/repair costs, the fuel costs, the tax, and the insurance. The total cost per year is the sum of the depreciation and the running costs.

Note that the underlining can be done with one of the options under the Borders toolbar button .

A third option is to purchase the new car for €17,000 and run it for 14 years after which it will be worth €1000. The average service/repairs cost per year will be about €320. The average fuel cost per mile over the 14 years will be about €0.18. The tax and insurance will be the same. Calculate the Total cost per year for Option 3. 

Exercise 4

You have a company producing units of Product A. Your fixed costs for a year are €100,000. The cost of making one unit is €3.40. You can sell a unit for €5.00. Assume you can sell as many units as you produce. If you make 100,000 units per year how much profit will you make? Create a worksheet to answer this question. Have the total cost and the total sales (and the difference – the profit) included.

NOTE THAT YOU CAN TYPE COMMAS IN NUMBERS TO INDICATE THE BREAKS AT THOUSANDS AND MILLIONS – FOR EXAMPLE, TYPE ‘1,000,000’ IN A CELL AND EXCEL WILL DISPLAY THE COMMAS BUT WILL IGNORE THEM WHEN DOING CALCULATIONS. THIS MAKES LARGE NUMBERS EASIER TO READ AND REDUCES THE CHANCES OF ERRORS BEING MADE.

Use Goal Seek to determine how many units you need to produce in order to break even (i.e. to have zero profit).

Use Goal Seek to determine what the lowest selling price is for you to break even if you sell 100,000 units per year.

Exercise 5

A car was accelerated up to 120 kmph and the speed was measured at two second intervals. The results are

Time (s)

Speed (kmph)

0

0

2

35

4

60

6

78

8

95

10

110

12

120

 

You will use this data to calculate the acceleration of the car and how it changes over time. Acceleration is the rate of change of velocity (or speed if you’re not considering the direction of the motion). You can estimate it from the values in the table. The speed changed from 0 kmph to 35 kmph in the first 2 seconds so we can assume that the acceleration at time 0 s was approximately (35-0)/2 kmph per second. Use Excel to calculate the acceleration at the other points in time – first enter the values above and then add a new column for the acceleration. You do not have to calculate the acceleration at time 12 seconds. Graph the acceleration values against time. Label the graph correctly.

Exercise 6

Given the speed data from the table in Exercise 5 above, calculate how far the car has travelled at each point in time.

How will you do this?

If a car is going at 80 kmph for 2 hours, how far has it travelled? – 80 kmph * 2 hours = 160 km.

If a car starts off at 40 kmph and ends up at 80kmph after 2 hours, how far has it travelled in the two hours? The answer is that we don’t know. A reasonable way of estimating the answer, however, is to take the average speed ( (40 + 80) / 2 = 60 kmph) and multiply it by 2 hours.

In this example you can estimate how far the car has travelled in each two second interval. Note that there are 3600 seconds in an hour. Add these values up to find out how far it has travelled at any point in time. The solution is shown below.

Plot the distance travelled against time.

Exercise 6

Someone offers you the choice of getting ten million Euro or getting one cent on the first day of January, two cent the next day, four cent the next, and so on – the amount doubling each day until the end of the month.  Use a Worksheet to decide which you should take.

Exercise 7

Plot the function

over the range x between -5 and 5 and y between -5 and 5. The result will be a surface. The step-size in the x and y directions (the amount x or y change from one point to the next) should be 0.5. Start with the following:

The x values are across the top and the y values are down the side. Add an equation in cell B2 to calculate x2+y2 where the x value is in B1 and the y value is in A2. Put in the equation in such a form so that it can be dragged down and across to fill all the cells indicated in yellow above. The value in cell L12, for example, should be 0 since x is zero and y is zero at that point. The value in V22 should be 50.

Now select cells A1 to V22 and using the Chart Wizard create a surface plot like that shown below. The values in row 1 will be taken as the x values and those in column A will be taken as the y values for the graph.

Select the cells again and using Chart Wizard create a filled contour plot – choose the  option in the Surface category. The result should be like that below. The legend (to the right of the graph) indicates what ranges of values are indicated by each colour. The two contour lines around the yellow band, for example, represent f(x,y) = 20 and f(x,y) = 30.

It is clear that the contours are circles – is that what you expected?

What if you wanted more contours – e.g. ones for 0, 5, 10, etc.? What you need to change is the scale of the z-axis for the plot. There is no z-axis shown in a contour plot but the values in the z-direction are indicated by the contours. If you wanted to adjust the x-axis then you would double-click on the x-axis on the graph – and the same for the y-axis. In this case double-click on the legend. A dialog box will appear. Select the Scale tab. Change the Major Unit value to 5 and press OK.

Exercise 8

Use Goal seek to find any two roots of the equation

Note that Goal Seek will not find the roots exactly – there are limitations due the accuracy with which a computer can store numbers.

Exercise 9

The temperature in an industrial oven over time can be approximated (using Euler’s Method’) using the iterative equation


where W (in °C/s) is related to the power of the oven (the rate at which energy goes into it) and the size of the oven and
a (in s−1) is related to the rate at which heat is lost through the oven walls. T1, the initial temperature, is 20°C. The value of Dt is the “time step” – for example, if Dt is 1 second then the value T2 represents the temperature after 1 second, T3 is the temperature after 2 seconds, and so on. Similarly if Dt is 2 seconds then the value T2 is the temperature after 2 seconds, T3 is the temperature after 4 seconds, and so on.

Calculate the temperature over the first 100 seconds with Dt = 1 and plot the temperature against time. The values of W and a are, respectively, 5 °C/s and 0.01 s−1. These should be entered in separate cells so that they can be easily adjusted.

If you want the temperature after 50 seconds to be 300°C, what value should W have? Calculate and record this value.

The accuracy of the approximation given above and used in the worksheet depends on the length of the time step (Dt) used in the calculation. The smaller the time step the more accurate the result – the downside being that more calculations are required. Change the step size to 0.1 and recalculate what value of W is required for the temperature after 50 seconds to be 300°C. Note that the value should only differ slightly from that obtained with Dt = 1.

Exercise 10

Hooke’s law says that the extension of a spring is proportional to the force across it (i.e. to the force stretching the string). This can be written as

where k is the spring stiffness. If the force, F, is measured in Newtons (N), and the extension, E, is measured in metres (m), then k is measured in m/N. A negative force compresses the spring leading to a negative extension.

We can write that the length of the spring, L (a function of the force), is

where L0 is the free length of the spring (i.e. the length of the spring when there is no force across it). Note that this is the equation of a straight line. If L is plotted against F then to the slope of the line will be equal to k, the spring stiffness.

If you want to calculate k then you can put weights on the spring and measure the resulting lengths. Plot each point (length plotted against force) and measure the slope of the resulting straight line.

What is the minimum number of measurements (points) needed?

The answer is two. If you want to measure the slope of a line then the minimum number of points is two. In this case you could measure the free length (i.e. F = 0) and the length with a single weight (e.g. 1 Kg). If the first length value is 0.5 m and the second is 0.75 m then the results would be as shown below (note that a 1 kg mass produces a force due to gravity of 1×9.81 N). The stiffness is (0.75-0.5)/(9.81-0) = 0.025484 m/N.

In practice it is not possible to measure a force or a length completely accurately. Any point you plot on a graph is not, therefore, certain– there is some “uncertainty” in its horizontal and vertical position. Instead of exact points as illustrated below left, each point plotted represents an actual value that lies somewhere within a rectangle (as illustrated below right – note that the size of the rectangles is exaggerated). For example, a measured length of 0.5 metres may mean a value between 0.49 and 0.51 if you can only measure with an accuracy of within 0.01 m – and similarly with the force.

If the points are measured perfectly accurately then the slope of the line through the points can also be calculated perfectly accurately (see below left). If there is uncertainty in the two points then there will be uncertainty in the calculated slope. In the diagram below right the two measured points represent actual values that lie somewhere within the two rectangles. Putting a line through the points will give you a slope value but in reality the slope may be anywhere between that of the two lines shown.

Is it possible to improve the estimate of the slope if there is uncertainty in your measurements?

The answer is ‘yes’ – by making more measurements and by finding the slope of the line that best fits the resulting data.

 

The following measurements were taken for a spring. Plot them and calculate the spring stiffness using a best-fit line.

F (N)

L (m)

0

0.506

0.981

0.566

1.962

0.55

2.943

0.557

3.924

0.605

4.905

0.625

5.886

0.692

6.867

0.686

7.848

0.692

8.829

0.734

9.81

0.773

 

Why does this work?

Consider this example. Someone gives you a coin and asks you to find out the probability of it, when thrown, turning up ‘heads’. What would you do? You would have to throw it many times and count the number of ‘heads’ and the number of ‘tails’. The answer you give will have some uncertainty in it but this will decrease with the number of throws. For example, if you throw it 10 times and it comes up heads 3 times then you might say that it has a 3 in 10 chance of coming up heads but you know that there is a lot of uncertainty in that – if you repeat it you may get a very different result. If you throw it a million times and it comes up ‘heads’ 500,000 times then you can say with far more certainty that the chance on a ‘head’ is 1 in 2. Why? – because you are ‘averaging’ the results of many experiments.

 

When calculating the spring stiffness each measured value may be above or below the actual value. If there is an equal chance of any measurement being above as there is of it being below then you can reduce the uncertainty by averaging over many points – the more points the better.