7mUV88/=pF)+F)0!+F)?0+F)+' '' ' ' 44']'e8&',@@ 'et',@@ ',@@ 'e 'e 444'e8&',@@ ',@@ 'e'et44V'Z  'A 'L'G@'  'F@'  'E@' 'D@F'08&'04'05',@@ '2@@''2@@  ',@@ '2@@'2@@'t ',@@ '2@@'2@@' q ',@@ '2@@'2@@' '2@@'2@@' '2@@'   '2@@'   4444'$''''''''' f' ' 4' '' ' 44' '' ''44E' ' 'q' ' ' '' ''444V''@'@ V''@'@ V''@'@ V''@'@ V''@'@ V''@'x@ :''@ V''@'@ V''@'m@ :''@ V''@'@ :''@ V''@'@ :''@ '''\ ''\ ''\ ''\ ''\' ') @@+) @@+'@@  6' j' &&' ' 4j'&&'' 4j'&&'@' 4f'&&'  4f'&&'  4f'&&'  4'&&',@@ '2@''2@  ',@@ '2@'2@'t ',@@ '2@'2@' q ',@@ '2@'2@' '2@'2@' '2@'   '2@'   44444f'&&'  4f'&&'  4f'&&'  4!~EQUATIONFINDER by David Hollis"Arial "Arial  "Arial "Arial "ArialUNo warranty is implied or given in the use of this spreadsheet. The user must satisfy themselves of the suitability of the spreadsheet calculations and the methods used. The sheet is divided into two parts the top portion for use and the lower portion explaining and undertaking the calculations.%If amending the sheet it is imortant to note that there are tables with formulae in the cells which sometimes produce blank entries.Step 1"Arial$Insert your data below as x and y values for each point that you have. If you have less than 16 points it is important that the cells are left blank by deleting any data in the cells.,6 Point Number"Arial,X value"Arial,Y value"Arial004(4$4$8(8$8$<(<$<$@(@$@$D(D$D@H(H$H$L(L$L$P(P$ P$T( T$ T@X( X$ X$X\( \$\$`( ``d( ddh(hhl(llp(ppxStep 2"ArialChoose an equation type from below and enter the reference number in cell b40 to derive the coefficient of determination and the coefficients for the equation.BReference Number"Arial6TYPE OF CURVE"Arial"Arial Formula"Arial"Arial*Min Points"Arialr(as required mathematically)0 "ArialLinear"Arial y=a+bx "Arial@ "Arial0 "Arial&Parabolic"Arial *y=a+bx+cx "Arial@ "Arial0 "ArialCubic"Arial :y=a+bx+cx+dx "Arial@ "Arial0 "ArialInverse"Arial y=a+b/x "Arial@ "Arial0 "Arial.Exponential"Arial"Arial >y= a * EXP(b*x) "Arial@ "Arial0 "Arial.Logarithmic"Arial .y=a+b*LN(x) "Arial@ "Arial0 "ArialPower"Arial y=a*x^b "Arial@ "Arial Input Ref number in cell >>>>>>>>>>>>>>>"Arial4*Note: due to the taking of logarithms and dividing by the x values it is not possible to test for certain types of equation where the x and/or y values contain zeros or -ve numbers. Standard EPOC error values will then be shown for the coordinates. "ArialStep 3"Arial   Coefficients >>>>>>>>>>>>>>>>>>>>>>>>>>"Ariala"Arialb"Arial c"Ariald"ArialfSee eqn above for format.Wҭ 8+@: #Ӯ<,+x?>Coefficient of Determination >>>>>>>>>>>>>>"ArialMMޅe?d "ArialStep 4"ArialView the graph of the original points against the curve as determined. The table from which the graph is constructed is below. Ctrl-Q will switch view. Input values are crosses and the equation as determined is shown by a line.GRAPHSFORINVERSEEQNS If x=0 then y=infinity for inverse equations. The graph does not show the discontinuity at x=0. "Arial "y as now"Arialnew x"Arialnew y"ArialFThe data >>>>>>>>"Arial*x as input"Arial*y as input"Arial *calculated"Arialrange"Arialrange"ArialPoint(V(X(X E?(l E?T(V(X(X Ê>, q@?r. @T(V(X(X B @@) @T(V(X(X ̨q{@ @[>@T(V(X@X i?@@gԏvr@T(V(X(X @VUUUUU@81?@T (V (X (X K l@ @ FC@T(V( X(X @T$( V$X$X $$&@$HҸh@T((V(X(X (((@(Dq8!@T,(V,X,X ,,GDDDDD*@,%@T0(V0X0X 00(j0G),@T8 8 8  8 8 8 8 8  8 $8 (8 ,8 08 48 88 <8 @8 D8 H8 L8 P8 T8 X8 \8 `8 d8 <JThe Working Method"Arial@The general method is thanks to J Bulat (the magazine A&B Computing Oct 1984 published by Argu). The specific method for resolving the matrix calculations is thanks to L W T Stafford (Business Mathematics M & E Handbooks 1969 ISBN 0 7121 0282 5).HmFor each type of curve the equation is reduced to a linear form to allow normal linear regression to be used.PBThe linear form.P:y=a+bx+cx+dx "ArialXTo illustrate let us consider the exponential equation.X6y= a*exp(b*x)`jTaking logs on both sides.`NLN(y) = LN(a) + b*yhIf we let Y=LN(y) and A=LN(a) then. h.Y = A + b*x h^This is in linear form.pThe nescessary conversions are in the table below.ttt ttttt t$t(t,t0t4t8ty= a * EXP(b*x)"Arialx "ArialLN(y) "Arial @ "Arial@ "Arial:Logarithmic ok "Arial.y=a+b*LN(x)"ArialLN(x) "Arialy "Arial @ "Arial@ "Arial"Power ok "Arialy=a*x^b"ArialLN(x) "ArialLN(y) "Arial @ "Arial "Arial "Arial  "Arial "Arial "Arial "Arial "Arial "Arial  "Arial "Arial "Arial "ArialTo deterine the best fit for a linear equation the method of least squares is used which is not described here but is available from many books on mathematics. "Arial "Arial  "Arial "Arial "Arial "Arial "Arial "Arial  "Arial "Arial "Arial "ArialIt requires the calculation of the sum of a series of values identivied in the headings to the table below in columns b to k. "Arial "Arial  "Arial "Arial "Arial "ArialThe first column is to create the sum of y^2 required to determine the coef of determination. The remainder of the table is necessary to provide sums of values for each point to be able to undertake regression anaysis. "Arialy^2"Arialy"Arialx"Arial x^2"Arialx^3"Arialx^4"Arialx^5"Arialx^6"Arial  yx"Arial$yx^2"Arial(yx^3"Arial,"Arial0"Arial "Arial "Arial  "Arial "Arial "Arial "Arial88 "Arial`8 "Arialb 8 "Arial8 "Arial8 "Arial8 "Arial 8 "Arial 8 "Arial$8 "Arial(8 "Arial, "Arial0 "Arial88 "Arial`8 "Arialb 8  "Arial;@ "Arial@T@ "Arial`n@ "Arial Ȇ@ "Arial 8 "Arial$8- "Arial(`@ "Arial, "Arial0 "Arial8$8 "Arial`8 "Arialb /@ "ArialO@ "Arialo@ "Arial@ "Arial @ "Arial 8 "Arial$W@ "Arial(w@ "Arial, "Arial0 "ArialO@8 "Arial`8 "Arialb 8 "Arial8} "Arial8q "Arial85  "Arial 8 = "Arial 8( "Arial$8 "Arial(8 "Arial, "Arial0 "Arial L@@ "Arial`8 "Arialb 8$ "Arialk@ "Arial@@ "Arial`@ "Arial @ "Arial 8- "Arial$8 "Arial(P@ "Arial, "Arial0 "Arial818 "Arial`8 "Arialb 81 "Arial8W "Arial8a  "Arial8A "Arial 8 "Arial 81 "Arial$8W "Arial(8a  "Arial, "Arial0 "Arial88 "Arial`8 "Arialb O@ "Arial@ "Arial@ "Arial@ "Arial A "Arial 8( "Arial$s@ "Arial(@ "Arial, "Arial0 "Arial88 "Arial`8  "Arialb 8Q "Arial8 "Arial@ "Arial @ "Arial 7 A "Arial 8- "Arial$8 "Arial(8= "Arial, "Arial0 "Arial E@@ "Arial`8  "Arialb 8d "Arial8 "Arial8' "Arial8 "Arial 8@B "Arial 8A "Arial$8 "Arial(8d "Arial, "Arial0 "Arial818 "Arial`8  "Arialb 8y "Arial83 "Arial@ "ArialبA "Arial );A "Arial 8M "Arial$8O "Arial(8e$ "Arial, "Arial0 "Arial88 "Arial`8 "Arialb 8 "Arial8  "Arial8 "Arial84 "Arial 8@r "Arial 8 "Arial$8  "Arial(8 "Arial, "Arial0 "Arial "Arial` "Arialb  "Arial "Arial "Arial "Arial  "Arial  "Arial$ "Arial( "Arial, "Arial0 "Arial "Arial` "Arialb  "Arial "Arial "Arial "Arial  "Arial  "Arial$ "Arial( "Arial, "Arial0 "Arial "Arial` "Arialb  "Arial "Arial "Arial "Arial  "Arial  "Arial$ "Arial( "Arial, "Arial0 "Arial "Arial` "Arialb  "Arial "Arial "Arial "Arial  "Arial  "Arial$ "Arial( "Arial, "Arial0 "Arial "Arial` "Arialb  "Arial "Arial "Arial "Arial  "Arial  "Arial$ "Arial( "Arial, "Arial0 "Arial "Arial "Arial "Courier"Courier"Courier"Courier"Courier, "Arial  "Arial  "Arial "Courier "Courier "Courier "Courier "Courierā@ "Arialn8H "Arialn8N "Arialn 8 "Arialn8 "Arialn8&2 "Arialn8t "Arialn8N "Arialn 8U "Arialn$8! "Arialn(8 "Arialn, "Arial0 "Arial "Arial "Arial  "Arial "Arial "Arial "Arial "Arial "Arial  "Arial "Arial "Arial "ArialThe regression eqns to solve the above from a set of n data points. "Arial "Arial "Arial  "Arial "Arial "Arial "Arial  "Courier  "Arial  "Arial  "Arial  "Arial  "Arial$ "Arial$ "Arial $ "Arial$ "Arial$ "Arial$ "Arial0$(a n + "Arial(b {x + "Arial (c{x^2 + "Arial(d{x^3 = "Arial( {y "Arial( "Arial,a {x + "Arial,b{x^2 + "Arial ,c{x^3 + "Arial,d{x^4 = "Arial,{yx "Arial, "Arial0a{x^2 + "Arial0b{x^3 + "Arial 0c{x^4 + "Arial0d{x^5 = "Arial0{yx^2 "Arial0 "Arial4a{x^3 + "Arial4b{x^4 + "Arial 4c{x^5 + "Arial4d{x^6 = "Arial4{yx^3 "Arial4 "Arial8 "Arial8 "Arial 8 "Arial8 "Arial8 "Arial8 "Arial8 "Arial<MThese are in linear format with four quadratic equations to be solved which can be achieved using matrix mathematics. (S is used instead of the summation sign of sigma).< "Arial< "Arial < "Arial< "Arial< "Arial< "Arial< "Arial@n @ Sx @Sx^2@Sx^3 @a@ '= @ SyDx DSx^2 DSx^3DSx^4 DbD '= DSxyHx^2 HSx^3 HSx^4HSx^5 HxHcH '= HSx^2 yLx^3 "ArialLSx^4 "Arial LSx^5 "ArialLSx^6 "ArialLdL '= LSx^3 y "ArialPP "ArialP "Arial P "ArialP "ArialP "ArialP "ArialP "ArialTThis can be solved using a partitioned matrix (see Stafford). By finding the inverse of the left hand side with the same actions applied to the right then the required coeficients will appear on the right hand side.T "ArialT "Arial T "ArialT "ArialT "ArialT "ArialXn X Sx XSx^2XSx^3 X Sy\x \Sx^2 \Sx^3\Sx^4 \Sxy`x^2 `Sx^3 `Sx^4`Sx^5 `Sx^2 ydx^3 "ArialdSx^4 "Arial dSx^5 "ArialdSx^6 "ArialdSx^3 y "AriallThe actual figures transposed into the matrix are shown below.p "Arialp "Arial p "Arialp "Arialp "Arialp "Arialt8  "Arialht8N "ArialB t8 "ArialBt8 "ArialBt8H "ArialDt "Arialtx8N "ArialFx8 "ArialF x8 "ArialFx8&2 "ArialFx8U "ArialHx "Arial|8 "ArialJ|8 "ArialJ |8&2 "ArialJ|8t "ArialJ|8! "ArialL| "Arial8 "ArialN8&2 "ArialN 8t "ArialN8N "ArialN8 "ArialP "ArialThe format of the cells for the calculations. Desriptions of the actions taken appear to the right of the matrix. a1 "Arial b1 "Arial  c1 "Arial d1 "Arial e1 "Arial>Cell references "Arial a2 "Arial b2 "Arial  c2 "Arial d2 "Arial e2 "Arial a3 "Arial b3 "Arial  c3 "Arial d3 "Arial e3 "Arial a4 "Arial b4 "Arial  c4 "Arial d4 "Arial e4 "Arial8  "Arial@8N "Arial@ 8 "Arial@8 "Arial@8H "Arial@ZData entry into matrix "Arial8N "Arial@8 "Arial@ 8 "Arial@8&2 "Arial@8U "Arial@8 "Arial@8 "Arial@ 8&2 "Arial@8t "Arial@8! "Arial@8 "Arial@8&2 "Arial@ 8t "Arial@8N "Arial@8 "Arial@ "Arial8 "Arial]tE]@ "Arial 袋.O@ "ArialF]t%@ "Arial/袋.@ "ArialJDivide Row 1 by a1 "Arial,6Turns a1 to 1 "Arial8N "Arial8 "Arial 8 "Arial8&2 "Arial8U "Arial8 "Arial8 "Arial 8&2 "Arial8t "Arial8! "Arial8 "Arial8&2 "Arial 8t "Arial8N "Arial8 "Arial8 "Arial]tE]@ "Arial 袋.O@ "ArialF]t%@ "Arial/袋.@ "Arial8 "Arial\tEb@ "Arial ^tE@ "ArialF]tQm@ "Arial\tEU@ "ArialzSubtract a2 x Row 1 from Row 2 "Arial,6Turns a2 to 0 "Arial8 "Arial^tE@ "Arial ]tEW@ "ArialF]tAA "Arial\tE!@ "ArialzSubtract a3 x Row 1 from Row 3 "Arial,6Turns a3 to 0 "Arial8 "ArialF]tQm@ "Arial F]tAA "Arial ֕YA "Arial.@ "ArialzSubtract a4 x Row 1 from Row 4 "Arial,6Turns a4 to 0 "Arial8 "Arial]tE]@ "Arial 袋.O@ "ArialF]t%@ "Arial/袋.@ "Arial8 "Arial 8 "Arial 20%-@ "Arial m2:h@ "Arial :r? "Arial JDivide Row 2 by b2 "Arial,6Turns b2 to 1 "Arial8 "Arial^tE@ "Arial ]tEW@ "ArialF]tAA "Arial\tE!@ "Arial8 "ArialF]tQm@ "Arial F]tAA "Arial ֕YA "Arial.@ "Arial8 "Arial"8 "Arial" &GD "Arial"bN|̆ "Arial"x1@ "Arial"zSubtract b1 x Row 2 from Row 1 "Arial,6Turns b1 to 0 "Arial8 "Arial8 "Arial 20%-@ "Arialm2:h@ "Arial:r? "Arial8 "Arial"8 "Arial" zȢ@ "Arial"prc@ "Arial"+ث=R@ "Arial"zSubtract b3 x Row 2 from Row 3 "Arial,6Turns b3 to 0 "Arial8 "Arial"8 "Arial" prc@ "Arial"+{H3A "Arial"߳<@ "Arial"zSubtract b4 x Row 2 from Row 4 "Arial(,6Turns b4 to 0 "Arial8 "Arial8 "Arial &GD "ArialbN|̆ "Arialx1@ "Arial 8 "Arial 8 "Arial 20%-@ "Arial m2:h@ "Arial :r? "Arial8 "Arial$8 "Arial$ 8 "Arial$lF6@ "Arial$a5L? "Arial$JDivide Row 3 by c3 "Arial,6Turns c3 to 1 "Arial8 "Arial8 "Arial prc@ "Arial+{H3A "Arial߳<@ "Arial "Arial "Arial "Arial  "Arial "Arial "Arial "Arial8 "Arial(8 "Arial( 8 "Arial(Tm_i@ "Arial(t @ "Arial(zSubtract c1 x Row 3 from Row 1 "Arial,6Turns c1 to 0 "Arial 8 "Arial( 8 "Arial( 8 "Arial(  |a "Arial( жB? "Arial( zSubtract c2 x Row 3 from Row 1 "Arial, 6Turns c2 to 0 "Arial$8 "Arial$8 "Arial $8 "Arial$lF6@ "Arial$a5L? "Arial(8 "Arial((8 "Arial( (8 "Arial((`0}!%@ "Arial((: :@ "Arial((zSubtract c4 x Row 3 from Row 1 "Arial,(6Turns c4 to 0 "Arial08 "Arial08 "Arial 08 "Arial0Tm_i@ "Arial0t @ "Arial48 "Arial48 "Arial 48 "Arial4 |a "Arial4жB? "Arial88 "Arial88 "Arial 88 "Arial8lF6@ "Arial8a5L? "Arial<8 "Arial*<8 "Arial* <8 "Arial*<8 "Arial*<,+x? "Arial*<JDivide Row 4 by d4 "Arial,<6Turns d4 to 1 "ArialD8 "Arial.D8 "Arial. D8 "Arial.D8 "Arial.DWҭ  "Arial.DzSubtract d1 x Row 4 from Row 1 "Arial,D6Turns d1 to 0 "ArialH8 "Arial.H8 "Arial. H8 "Arial.H8 "Arial.H+@ "Arial.HzSubtract d2 x Row 4 from Row 2 "Arial,H6Turns d2 to 0 "ArialL8 "Arial.L8 "Arial. L8 "Arial.L8 "Arial.L#Ӯ "Arial.LzSubtract d3 x Row 4 from Row 3 "Arial,L6Turns d3 to 0 "ArialP8 "ArialP8 "Arial P8 "ArialP8 "ArialP,+x? "Ariald^Coef of Determination =daS(y) + bS(xy) + cS(x^2y) + dS(x^3y) - (((Sy)^2) /n)  d  d d hbS(y^2) - (((Sy)^2) /n)ta =tWҭ fx6((Sy)^2) /n =xtE]t}@^^Coef of determination =MMޅe???8   x 8-/7@98PR V\^_`abcdefghij8kl n@pqrstuvwxyz{|}~t8, ``````````````` `!`"`#`$` "ArialLNPJm\`,,: @,@6; @0@=L6;Range 1=L=L6;Range 2=LiArial Graph 1&Sheet.app )!AH