# Best shape for gutter and further alegbra - using Excel to solve some mathematical problems

Assignment 1

1. A length of guttering is to be made by taking a rectangular piece of plastic and folding up the edges.

The dotted lines symbolise the folding of the edges.

Let the measurements be units (as no specific measurements are given)

Volume max = Area of cross section max x length

Since the length is just a multiplicative constant, maximizing the volume is the same as maximizing the cross sectional area.

Therefore the area of cross section max will determine the maximum volume of the gutter.

Using a width of 10 units, I will investigate the different types of cross sections that could be used. The first obvious shape to construct would be a square cross section.

Square Cross section

Area = height x width

Area =) x )

=

Trapezium cross section

To find the maximum volume I need to establish a relationship between the three variables ?, width of base and Volume. We can do that from the following diagram:

Area = (1/2) * h * (a + b)

where

h = (w – a) cos (?) (height)

a = a (first parallel side)

b = a + 2(w – a) sin (?) (second parallel side)

To calculate angle ?, I have taken the vertical perpendicular to the base as the starting point at ? = 0,

Firstly, due to the 3 variables I fixed a and w-a to be equal length and varied ? by 1 degree.

A snapshot of the values obtained using excel is below.

?ï¿½ with horizontal

a

b

Area

28

3.333

3.333

14.41629

29

3.333

3.333

14.42938

30

3.333

3.333

14.43376

31

3.333

3.333

14.42935

32

3.333

3.333

14.41606

Highlighted in red using conditional formatting is the greatest area using these values and varying ?

Then I varied base a which also varied w-a. Base a was varied by 1 unit each time.

Below is a snapshot of the excel spreadsheet showing the optimum values obtained with varying a and ?. Highlighted in red is the maximum area which would give the maximum volume for this cross section.

?ï¿½ with horizontal

a

w-a

Area

3

9

0.5

4.506899

7

8

1

8.06133

11

7

1.5

10.7285178

20

6

2

12.5949789

21

5

2.5

13.76079

27

4

3

14.3326548

32

3

3.5

14.4096185

36

2

4

14.08059

41

1

4.5

13.4226573

29

3.33333

3.33333

14.42938

30

3.33333

3.33333

14.43376

31

3.33333

3.33333

14.42935

The formulae to calculate the area of this cross section are given in Appendix 1.

Triangular cross section(V shape)

Area = x () x () sin?

A =w2 sin?

By varying ? by 1ï¿½, where 0ï¿½ is at the point of the two sides touching each other to make a straight line. Below is a snapshot of the excel spreadsheet showing optimum results.

?ï¿½

w/2

w/2

Area

88

5

5

12.49239

89

5

5

12.4981

90

5

5

12.5

91

5

5

12.4981

92

5

5

12.49239

The formulae used to calculate the area of this cross section is given in appendix 2.

By using algebra I can find ? at which the optimum capacity exists.

Angle to give Optimum capacity;

=w2 sin ? = 0

Cos ? = 0

? = 90ï¿½

Semi-Circular cross-section

l = ? x r

l = ? x r

r =

? r =

Area of semi circle = 1/2 x pi x r2

?ï¿½

Area

180

3.183099

31.83099

Formulae used in excel for this calculation is given in Appendix 2.

By the use of conditional formatting of the entire spreadsheet I have found that the best cross section to use for a gutter to carry the maximum water capacity is a semi circle.

2)

Using Excel; it will take 21 months to clear the debt. Of which that last month is a small payment under ï¿½70.

Total interest paid in this case = ï¿½201.89

To clear the debt off in half the time the payments will have to increase to approximately ï¿½130. This is an increase of approximately 85% of the original monthly payment.

To clear off the debt in double the time the payments will be lower, decreasing to around ï¿½40. Approximately 40% lower.

A copy of the spreadsheet is given overleaf followed by the formulae used to create this spread sheet. As a little extra this spreadsheet allows a user to enter a period of time, in months, to clear the debt and the monthly payments required will be calculated.

N.B.

The other additional extra which uses the monthly payment will calculate the time, in months; it takes to clear the debt and displays the total interest payable. This is created by using a different formula and because of that it displays the total interest to be 2 pence lower that the other method. The reason for this is that the formula used has calculated a part month interest, where the initial method takes the interest as a full month.

3) Excel has a variety of uses. One benefit of using spreadsheets for both question 1 and 2 is speed. For instance question 1 is to determine best possible design of a gutter. By the use of formula the volume of a design is quickly calculated. Replication of formulae enables the effortless generation of a large number of calculated values across a chosen range of values (Mathematical Association, 2002, p39). By calculating the formula for the first two rows in Appendix 1 then dragging these formulae down the cells uses the formulae with the corresponding values to enable fast calculation of the area.

By manipulating the formulae the volume of the other designs can be generated. This is a lot quicker in excel compared to using pen, paper and calculator. It also allows the recording of all calculations. A spreadsheet is instantly responsive to changed input values which enables exploration of the effect of variables within a process (Mathematical Association, 2002, p39)

With the conditional formatting function in excel it is easy to locate which cross section gives the highest value. This saves time comparing manually as the spreadsheet finds the greatest value automatically. The Mathematical Association (2002 p39) state “value sensitive formatting allows the appearance of text, values, and background, to change according to the intentions of the designer, and so give high value feedback or other information, to the user”

Using a spreadsheet in question 2 allows interactivity. Linked values are a defining feature of the spreadsheet. This capability to link and respond enables the spreadsheet to offer interactivity (Mathematical Association, 2002, p39).

The benefit of the replication of formulae is also a benefit to using a spreadsheet in question 2. The first two rows were calculated first using the formula given in the table on the previous page. By filling down with this formula, the other rows contain the same calculation for the corresponding cells which calculate a monthly figure reducing the debt and taking into account the interest rate.

4) Activity for key stage 3.

Question 1) The bar chart below shows the amount of money collected by 100 pupils on a sponsored walk. (Perks and Prestage, 2001b, p94)

a) Estimate the mean amount collected per child.

b) What is the mode?

Now using the table in figure 1, that created the above graph, change the frequencies so that the mean is 6.

Variable (x)

frequency (f)

x X f

3

12

36

4

13

52

5

23

115

6

27

162

7

13

91

8

12

96

Total

100

552

mean =

5.5200

Figure 1: Excel spreadsheet of a frequency table

Strategies used by pupils will vary. Some may use symmetry, some just trial and improvement, others may think about patterns of numbers.

Extension work: Can the mean be 5? Can you get the mean to be between 6 and 7?

* Obtain a mean of 5 and mode of 4

* Obtain a mean of 6 and mode of 3.

Pupils will firstly learn to read frequency from a bar chart and to calculate the mean themselves. They will have to know the formula for the mean to answer the question. This will make the pupil think which formula needs to be used.

The mode can be seen from the bar chart. As the numbers in the frequency column change the pupils will be able to see the mean being recalculated and the bar chart adjusting itself to the new values. This allows the pupils to see the effect of manipulating data has on the reproduction of bar charts. Prestage and Perks (2001a, p60) suggest as the solutions can be found by trial and improvement, there needs to be an expectation that pupils explain their results; otherwise the mathematics of the task will be diminished.

Question 2)

(Perks and Prestage, 2001b, p95)

a) Change the raw data in the spreadsheet to give a mean of 6.

b) Get a mean of 5 and a mode of 4.

c) Get a mean, median and mode of 5.

d) Get a mean of 4, a median of 3 and mode of 5

Question 3) (. (Perks and Prestage, 2001a, p62)

An article in the Evening Post reads ‘The average sponsorship money collected by each child was ï¿½6.00; what an effort!’ The Evening News stated ‘An average of ï¿½5.52 – what a collection!’ Both papers showed the same diagram. Which paper got its sums right?

In this question the pupil would use the original bar chart from the 1st question to find the averages. There is however no indication of whether mean, median or mode is expected. Both newspapers could have the correct information but choose to display it in a different way. The mathematics focuses on understanding the connection between raw data and the smoothing of the data shown in representation. (Prestage and Perks, 2001a, p62)

#### New Essays

×

Hi there, would you like to get such a paper? How about receiving a customized one? Check it out