Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Increaseing the number of aurguments in a function
Hi all
I have written a Function to calculate a hypobolic function for a given range of data ( in this case 5 samples). Is there a way I can change this to accept any number of samples (I would like to accept at least 15)? I had thought that a function using arrays might do it, but unfortuanatly the theory behind arrays is doing my head in! here is a copy of the function code wich I have achieved by brute force ( in summary its a funtion to add the sums of various combinations of x and y) Thanks for any help Ian Mangelsdorf Function Sw_hyp(P1, P2, p3, p4, p5, sw1, sw2, sw3, sw4, sw5, Pc_hyp) y1 = P1 y2 = P2 y3 = p3 y4 = p4 y5 = p5 x1 = sw1 x2 = sw2 x3 = sw3 x4 = sw4 x5 = sw5 Sumx = x1 + x2 + x3 + x4 + x5 Sumy = y1 + y2 + y3 + y4 + y5 Sumx2 = x1 ^ 2 + x2 ^ 2 + x3 ^ 2 + x4 ^ 2 + x5 ^ 2 Sumy2 = y1 ^ 2 + y2 ^ 2 + y3 ^ 2 + y4 ^ 2 + y5 ^ 2 Sumxy = (x1 * y1) + (x2 * y2) + (x3 * y3) + (x4 * y4) + (x5 * y5) 'calculate x squared y's xSy1 = x1 ^ 2 * y1 xSy2 = x2 ^ 2 * y2 xSy3 = x3 ^ 2 * y3 xSy4 = x4 ^ 2 * y4 xSy5 = x5 ^ 2 * y5 Sumx2y = xSy1 + xSy2 + xSy3 + xSy4 + xSy5 'calculate x y squared xyS1 = y1 ^ 2 * x1 xyS2 = y2 ^ 2 * x2 xyS3 = y3 ^ 2 * x3 xyS4 = y4 ^ 2 * x4 xyS5 = y5 ^ 2 * x5 Sumxy2 = xyS1 + xyS2 + xyS3 + xyS4 + xyS5 'calculate x sq y sq xSyS1 = y1 ^ 2 * x1 ^ 2 xSyS2 = y2 ^ 2 * x2 ^ 2 xSyS3 = y3 ^ 2 * x3 ^ 2 xSyS4 = y4 ^ 2 * x4 ^ 2 xSyS5 = y5 ^ 2 * x5 ^ 2 Sumx2y2 = xSyS1 + xSyS2 + xSyS3 + xSyS4 + xSyS5 number1 = Sumx2 * (Sumxy * Sumxy2 - Sumy * Sumx2y2) + Sumxy * (Sumx * Sumx2y2 - Sumxy * Sumx2y) + Sumx2y * (Sumy * Sumx2y - Sumx * Sumxy2) number2 = samples * (Sumx2y * Sumxy2 - Sumxy * Sumx2y2) + Sumx * (Sumy * Sumx2y2 - Sumxy * Sumxy2) + Sumxy * (Sumxy ^ 2 - Sumy * Sumx2y) number3 = samples * (Sumx2 * Sumxy2 - Sumxy * Sumx2y) + Sumx * (Sumy * Sumx2y - Sumx * Sumxy2) + Sumxy * (Sumx * Sumxy - Sumy * Sumx2) denomin = samples * (Sumx2y ^ 2 - Sumx2 * Sumx2y2) + Sumx * (Sumx * Sumx2y2 - Sumxy * Sumx2y) + Sumxy * (Sumxy * Sumx2 - Sumx * Sumx2y) a = number1 / denomin b = number2 / denomin C = number3 / denomin Sw_hyp = (a - Pc_hyp) / (C * Pc_hyp - b) End Function |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Increaseing the number of aurguments in a function
Ian,
Look at 'Understanding Parameter Arrays' in VBA Help, I think this will help you. -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "Ian Mangelsdorf" wrote in message om... Hi all I have written a Function to calculate a hypobolic function for a given range of data ( in this case 5 samples). Is there a way I can change this to accept any number of samples (I would like to accept at least 15)? I had thought that a function using arrays might do it, but unfortuanatly the theory behind arrays is doing my head in! here is a copy of the function code wich I have achieved by brute force ( in summary its a funtion to add the sums of various combinations of x and y) Thanks for any help Ian Mangelsdorf Function Sw_hyp(P1, P2, p3, p4, p5, sw1, sw2, sw3, sw4, sw5, Pc_hyp) y1 = P1 y2 = P2 y3 = p3 y4 = p4 y5 = p5 x1 = sw1 x2 = sw2 x3 = sw3 x4 = sw4 x5 = sw5 Sumx = x1 + x2 + x3 + x4 + x5 Sumy = y1 + y2 + y3 + y4 + y5 Sumx2 = x1 ^ 2 + x2 ^ 2 + x3 ^ 2 + x4 ^ 2 + x5 ^ 2 Sumy2 = y1 ^ 2 + y2 ^ 2 + y3 ^ 2 + y4 ^ 2 + y5 ^ 2 Sumxy = (x1 * y1) + (x2 * y2) + (x3 * y3) + (x4 * y4) + (x5 * y5) 'calculate x squared y's xSy1 = x1 ^ 2 * y1 xSy2 = x2 ^ 2 * y2 xSy3 = x3 ^ 2 * y3 xSy4 = x4 ^ 2 * y4 xSy5 = x5 ^ 2 * y5 Sumx2y = xSy1 + xSy2 + xSy3 + xSy4 + xSy5 'calculate x y squared xyS1 = y1 ^ 2 * x1 xyS2 = y2 ^ 2 * x2 xyS3 = y3 ^ 2 * x3 xyS4 = y4 ^ 2 * x4 xyS5 = y5 ^ 2 * x5 Sumxy2 = xyS1 + xyS2 + xyS3 + xyS4 + xyS5 'calculate x sq y sq xSyS1 = y1 ^ 2 * x1 ^ 2 xSyS2 = y2 ^ 2 * x2 ^ 2 xSyS3 = y3 ^ 2 * x3 ^ 2 xSyS4 = y4 ^ 2 * x4 ^ 2 xSyS5 = y5 ^ 2 * x5 ^ 2 Sumx2y2 = xSyS1 + xSyS2 + xSyS3 + xSyS4 + xSyS5 number1 = Sumx2 * (Sumxy * Sumxy2 - Sumy * Sumx2y2) + Sumxy * (Sumx * Sumx2y2 - Sumxy * Sumx2y) + Sumx2y * (Sumy * Sumx2y - Sumx * Sumxy2) number2 = samples * (Sumx2y * Sumxy2 - Sumxy * Sumx2y2) + Sumx * (Sumy * Sumx2y2 - Sumxy * Sumxy2) + Sumxy * (Sumxy ^ 2 - Sumy * Sumx2y) number3 = samples * (Sumx2 * Sumxy2 - Sumxy * Sumx2y) + Sumx * (Sumy * Sumx2y - Sumx * Sumxy2) + Sumxy * (Sumx * Sumxy - Sumy * Sumx2) denomin = samples * (Sumx2y ^ 2 - Sumx2 * Sumx2y2) + Sumx * (Sumx * Sumx2y2 - Sumxy * Sumx2y) + Sumxy * (Sumxy * Sumx2 - Sumx * Sumx2y) a = number1 / denomin b = number2 / denomin C = number3 / denomin Sw_hyp = (a - Pc_hyp) / (C * Pc_hyp - b) End Function |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Increaseing the number of aurguments in a function
Don't know if this would be of interest. Just to add...If you break your
numbers into arrays, you might be able to do something along this line... ' perhaps like this... tx = Array(x1, x2, x3, x4, x5) ty = Array(y1, y2, y3, y4, y5) With WorksheetFunction sumx = .Sum(tx) Sumy = .Sum(ty) Sumx2 = .SumSq(tx) Sumy2 = .SumSq(ty) Sumxy = .SumProduct(tx, ty) '...etc End With instead of: Sumx = x1 + x2 + x3 + x4 + x5 Sumy = y1 + y2 + y3 + y4 + y5 Sumx2 = x1 ^ 2 + x2 ^ 2 + x3 ^ 2 + x4 ^ 2 + x5 ^ 2 Sumy2 = y1 ^ 2 + y2 ^ 2 + y3 ^ 2 + y4 ^ 2 + y5 ^ 2 Sumxy = (x1 * y1) + (x2 * y2) + (x3 * y3) + (x4 * y4) + (x5 * y5) (Which function is this? I don't recognize it ;O ) -- Dana DeLouis Using Windows XP & Office XP = = = = = = = = = = = = = = = = = "Ian Mangelsdorf" wrote in message om... Hi all I have written a Function to calculate a hypobolic function for a given range of data ( in this case 5 samples). Is there a way I can change this to accept any number of samples (I would like to accept at least 15)? I had thought that a function using arrays might do it, but unfortuanatly the theory behind arrays is doing my head in! here is a copy of the function code wich I have achieved by brute force ( in summary its a funtion to add the sums of various combinations of x and y) Thanks for any help Ian Mangelsdorf Function Sw_hyp(P1, P2, p3, p4, p5, sw1, sw2, sw3, sw4, sw5, Pc_hyp) y1 = P1 y2 = P2 y3 = p3 y4 = p4 y5 = p5 x1 = sw1 x2 = sw2 x3 = sw3 x4 = sw4 x5 = sw5 Sumx = x1 + x2 + x3 + x4 + x5 Sumy = y1 + y2 + y3 + y4 + y5 Sumx2 = x1 ^ 2 + x2 ^ 2 + x3 ^ 2 + x4 ^ 2 + x5 ^ 2 Sumy2 = y1 ^ 2 + y2 ^ 2 + y3 ^ 2 + y4 ^ 2 + y5 ^ 2 Sumxy = (x1 * y1) + (x2 * y2) + (x3 * y3) + (x4 * y4) + (x5 * y5) 'calculate x squared y's xSy1 = x1 ^ 2 * y1 xSy2 = x2 ^ 2 * y2 xSy3 = x3 ^ 2 * y3 xSy4 = x4 ^ 2 * y4 xSy5 = x5 ^ 2 * y5 Sumx2y = xSy1 + xSy2 + xSy3 + xSy4 + xSy5 'calculate x y squared xyS1 = y1 ^ 2 * x1 xyS2 = y2 ^ 2 * x2 xyS3 = y3 ^ 2 * x3 xyS4 = y4 ^ 2 * x4 xyS5 = y5 ^ 2 * x5 Sumxy2 = xyS1 + xyS2 + xyS3 + xyS4 + xyS5 'calculate x sq y sq xSyS1 = y1 ^ 2 * x1 ^ 2 xSyS2 = y2 ^ 2 * x2 ^ 2 xSyS3 = y3 ^ 2 * x3 ^ 2 xSyS4 = y4 ^ 2 * x4 ^ 2 xSyS5 = y5 ^ 2 * x5 ^ 2 Sumx2y2 = xSyS1 + xSyS2 + xSyS3 + xSyS4 + xSyS5 number1 = Sumx2 * (Sumxy * Sumxy2 - Sumy * Sumx2y2) + Sumxy * (Sumx * Sumx2y2 - Sumxy * Sumx2y) + Sumx2y * (Sumy * Sumx2y - Sumx * Sumxy2) number2 = samples * (Sumx2y * Sumxy2 - Sumxy * Sumx2y2) + Sumx * (Sumy * Sumx2y2 - Sumxy * Sumxy2) + Sumxy * (Sumxy ^ 2 - Sumy * Sumx2y) number3 = samples * (Sumx2 * Sumxy2 - Sumxy * Sumx2y) + Sumx * (Sumy * Sumx2y - Sumx * Sumxy2) + Sumxy * (Sumx * Sumxy - Sumy * Sumx2) denomin = samples * (Sumx2y ^ 2 - Sumx2 * Sumx2y2) + Sumx * (Sumx * Sumx2y2 - Sumxy * Sumx2y) + Sumxy * (Sumxy * Sumx2 - Sumx * Sumx2y) a = number1 / denomin b = number2 / denomin C = number3 / denomin Sw_hyp = (a - Pc_hyp) / (C * Pc_hyp - b) End Function |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Increaseing the number of aurguments in a function
Don't know if this would work for you, but here is just an idea.
Function Sw_hyp(tx, ty, Pc_hyp) With WorksheetFunction sumx = .Sum(tx) sumy = .Sum(ty) sumx2 = .SumProduct(tx, tx) sumy2 = .SumProduct(ty, ty) sumxy = .SumProduct(tx, ty) sumx2y = .SumProduct(tx, tx, ty) Sumxy2 = .SumProduct(tx, ty, ty) Sumx2y2 = .SumProduct(tx, tx, ty, ty) 'number1 = Your same function... 'number2 = Your same function... 'number3 = Your same function... 'denomin = Your same function... Sw_hyp = (number1 - denomin * Pc_hyp) / (-number2 + number3 * Pc_hyp) End With End Function Sub TestIt() Dim Xs, Ys Xs = Array(9, 8, 7, 6, 5) Ys = Array(1, 2, 3, 4, 5) Debug.Print Sw_hyp(Xs, Ys, 1) End Sub I removed the three variable (a,b,c) because dividing by donomin is mostly factored out in the final equation. a = number1 / denomin b = number2 / denomin C = number3 / denomin Sw_hyp = (a - Pc_hyp) / (C * Pc_hyp - b) I don't know what the underlying equation is for number1, number2..etc is. -- Dana DeLouis Using Windows XP & Office XP = = = = = = = = = = = = = = = = = "Dana DeLouis" wrote in message ... Don't know if this would be of interest. Just to add...If you break your numbers into arrays, you might be able to do something along this line... ' perhaps like this... tx = Array(x1, x2, x3, x4, x5) ty = Array(y1, y2, y3, y4, y5) With WorksheetFunction sumx = .Sum(tx) Sumy = .Sum(ty) Sumx2 = .SumSq(tx) Sumy2 = .SumSq(ty) Sumxy = .SumProduct(tx, ty) '...etc End With instead of: Sumx = x1 + x2 + x3 + x4 + x5 Sumy = y1 + y2 + y3 + y4 + y5 Sumx2 = x1 ^ 2 + x2 ^ 2 + x3 ^ 2 + x4 ^ 2 + x5 ^ 2 Sumy2 = y1 ^ 2 + y2 ^ 2 + y3 ^ 2 + y4 ^ 2 + y5 ^ 2 Sumxy = (x1 * y1) + (x2 * y2) + (x3 * y3) + (x4 * y4) + (x5 * y5) (Which function is this? I don't recognize it ;O ) -- Dana DeLouis Using Windows XP & Office XP = = = = = = = = = = = = = = = = = "Ian Mangelsdorf" wrote in message om... Hi all I have written a Function to calculate a hypobolic function for a given range of data ( in this case 5 samples). Is there a way I can change this to accept any number of samples (I would like to accept at least 15)? I had thought that a function using arrays might do it, but unfortuanatly the theory behind arrays is doing my head in! here is a copy of the function code wich I have achieved by brute force ( in summary its a funtion to add the sums of various combinations of x and y) Thanks for any help Ian Mangelsdorf Function Sw_hyp(P1, P2, p3, p4, p5, sw1, sw2, sw3, sw4, sw5, Pc_hyp) y1 = P1 y2 = P2 y3 = p3 y4 = p4 y5 = p5 x1 = sw1 x2 = sw2 x3 = sw3 x4 = sw4 x5 = sw5 Sumx = x1 + x2 + x3 + x4 + x5 Sumy = y1 + y2 + y3 + y4 + y5 Sumx2 = x1 ^ 2 + x2 ^ 2 + x3 ^ 2 + x4 ^ 2 + x5 ^ 2 Sumy2 = y1 ^ 2 + y2 ^ 2 + y3 ^ 2 + y4 ^ 2 + y5 ^ 2 Sumxy = (x1 * y1) + (x2 * y2) + (x3 * y3) + (x4 * y4) + (x5 * y5) 'calculate x squared y's xSy1 = x1 ^ 2 * y1 xSy2 = x2 ^ 2 * y2 xSy3 = x3 ^ 2 * y3 xSy4 = x4 ^ 2 * y4 xSy5 = x5 ^ 2 * y5 Sumx2y = xSy1 + xSy2 + xSy3 + xSy4 + xSy5 'calculate x y squared xyS1 = y1 ^ 2 * x1 xyS2 = y2 ^ 2 * x2 xyS3 = y3 ^ 2 * x3 xyS4 = y4 ^ 2 * x4 xyS5 = y5 ^ 2 * x5 Sumxy2 = xyS1 + xyS2 + xyS3 + xyS4 + xyS5 'calculate x sq y sq xSyS1 = y1 ^ 2 * x1 ^ 2 xSyS2 = y2 ^ 2 * x2 ^ 2 xSyS3 = y3 ^ 2 * x3 ^ 2 xSyS4 = y4 ^ 2 * x4 ^ 2 xSyS5 = y5 ^ 2 * x5 ^ 2 Sumx2y2 = xSyS1 + xSyS2 + xSyS3 + xSyS4 + xSyS5 number1 = Sumx2 * (Sumxy * Sumxy2 - Sumy * Sumx2y2) + Sumxy * (Sumx * Sumx2y2 - Sumxy * Sumx2y) + Sumx2y * (Sumy * Sumx2y - Sumx * Sumxy2) number2 = samples * (Sumx2y * Sumxy2 - Sumxy * Sumx2y2) + Sumx * (Sumy * Sumx2y2 - Sumxy * Sumxy2) + Sumxy * (Sumxy ^ 2 - Sumy * Sumx2y) number3 = samples * (Sumx2 * Sumxy2 - Sumxy * Sumx2y) + Sumx * (Sumy * Sumx2y - Sumx * Sumxy2) + Sumxy * (Sumx * Sumxy - Sumy * Sumx2) denomin = samples * (Sumx2y ^ 2 - Sumx2 * Sumx2y2) + Sumx * (Sumx * Sumx2y2 - Sumxy * Sumx2y) + Sumxy * (Sumxy * Sumx2 - Sumx * Sumx2y) a = number1 / denomin b = number2 / denomin C = number3 / denomin Sw_hyp = (a - Pc_hyp) / (C * Pc_hyp - b) End Function |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Increaseing the number of aurguments in a function
Tahnks Dana The underlying equation is for modeling the saturation of rock samples at various pressures. It lloks like a dogs breakfast but usually works quite well. Ill give your suggestions a go Cheers Ian *** Sent via Developersdex http://www.developersdex.com *** Don't just participate in USENET...get rewarded for it! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Next number function | Excel Worksheet Functions | |||
Excel Workbook file increaseing in size | Excel Discussion (Misc queries) | |||
Excel Workbook file increaseing in size | Excel Discussion (Misc queries) | |||
Increaseing Precision in polynomial trendline equations | Charts and Charting in Excel | |||
Using the autofill function but increment by certain number in function | Excel Worksheet Functions |