Increaseing the number of aurguments in a function

  • Thread starter Thread starter Ian Mangelsdorf
  • Start date Start date
I

Ian Mangelsdorf

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
 
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)
 
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 )
 
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.
 
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
 
Back
Top