Countnumberofdays while function = TRUE
 

Dear list,

I've got a rather complicated problem:
I need to calculate the number of days an animal stays in an age-group,
based on its growth (depends on the weight of the animal and the day of the
year).

The weight of an animal on day x van be calculated as Wx = W(x-1) +
(0.1*W(x-1)^(2/3)-0.05*W(x-1))*f(Temp) With f(Temp) is a temperature
dependent function (ranges from 0-1).

In column A I calculated f(Temp) for each day (365 rows). In column B I want
the number of days before the animal reaches a certain weight (let's say, 10
grams). The number of days depends on the day of the year the animal enters
the simulation (Temp dependence).
A fictive example below:

A B
f(Temp) # days
0.30 30
0.31 29.3
0.32 29
0.33 ...
0.34
....
0.99
1.00
0.99
0.98
....
0.30

So, B2 (and the other cells in column B) should be something like (e.g.
start weight = 1 gram):
countnumberofdays while Wx=W(x-1)+(0.1*W(x-1)^(2/3)-0.05*W(x-1))*f(Temp) <=
10 grams.
I think it should be something with a Do ... Loop function, but I'm a
complete VBA nitwit.

Does anyone knows if this is possible?

Maarten

Countnumberofdays while function = TRUE
 

If each row is a day, then it should be as simple as:
=countif(B:B,"<=10")

No macro required.

As a further example, If you want to count 10 and <=20

=Countif(B:B,"10")-Countif(B:B,"20")



--
Regards,
Tom Ogilvy



"Maarten" wrote in message
...
Dear list,

I've got a rather complicated problem:
I need to calculate the number of days an animal stays in an age-group,
based on its growth (depends on the weight of the animal and the day of
the
year).

The weight of an animal on day x van be calculated as Wx = W(x-1) +
(0.1*W(x-1)^(2/3)-0.05*W(x-1))*f(Temp) With f(Temp) is a temperature
dependent function (ranges from 0-1).

In column A I calculated f(Temp) for each day (365 rows). In column B I
want
the number of days before the animal reaches a certain weight (let's say,
10
grams). The number of days depends on the day of the year the animal
enters
the simulation (Temp dependence).
A fictive example below:

A B
f(Temp) # days
0.30 30
0.31 29.3
0.32 29
0.33 ...
0.34
...
0.99
1.00
0.99
0.98
...
0.30

So, B2 (and the other cells in column B) should be something like (e.g.
start weight = 1 gram):
countnumberofdays while Wx=W(x-1)+(0.1*W(x-1)^(2/3)-0.05*W(x-1))*f(Temp)
<=
10 grams.
I think it should be something with a Do ... Loop function, but I'm a
complete VBA nitwit.

Does anyone knows if this is possible?

Maarten

Countnumberofdays while function = TRUE
 

But B doesn't contain the weights of the animals. The weights should be
calculated invisible, behind each cell in B.
B1 may only contain the number of days the animal needs to reach a weight of
10 grams, assuming that it enters the simulation on the day that corresponds
to cell B1

B2 may only contain the number of days the animal needs to reach a weight of
10 grams, assuming that it enters the simulation on the day that corresponds
to cell B2

B3 may only contain the number of days the animal needs to reach a weight of
10 grams, assuming that it enters the simulation on the day that corresponds
to cell B3

etc...




"Tom Ogilvy" wrote:

If each row is a day, then it should be as simple as:
=countif(B:B,"<=10")

No macro required.

As a further example, If you want to count 10 and <=20

=Countif(B:B,"10")-Countif(B:B,"20")



--
Regards,
Tom Ogilvy

Countnumberofdays while function = TRUE
 

x = 1
x = 0
do
x = x + 1
res = W(x-1) + _
(0.1*W(x-1)^(2/3)-0.05*W(x-1))*f(Temp)
Loop while res <= 10

--
Regards,
Tom Ogilvy


"Maarten" wrote in message
...
But B doesn't contain the weights of the animals. The weights should be
calculated invisible, behind each cell in B.
B1 may only contain the number of days the animal needs to reach a weight
of
10 grams, assuming that it enters the simulation on the day that
corresponds
to cell B1

B2 may only contain the number of days the animal needs to reach a weight
of
10 grams, assuming that it enters the simulation on the day that
corresponds
to cell B2

B3 may only contain the number of days the animal needs to reach a weight
of
10 grams, assuming that it enters the simulation on the day that
corresponds
to cell B3

etc...




"Tom Ogilvy" wrote:

If each row is a day, then it should be as simple as:
=countif(B:B,"<=10")

No macro required.

As a further example, If you want to count 10 and <=20

=Countif(B:B,"10")-Countif(B:B,"20")



--
Regards,
Tom Ogilvy

Countnumberofdays while function = TRUE
 

had a stray x = 1

x = 0
do
x = x + 1
res = W(x-1) + _
(0.1*W(x-1)^(2/3)-0.05*W(x-1))*f(Temp)
Loop while res <= 10

If reaching exactly 10 is a stop point then change the condition to

Loop while res < 10
--
Regards,
Tom Ogilvy

"Tom Ogilvy" wrote in message
...
x = 1
x = 0
do
x = x + 1
res = W(x-1) + _
(0.1*W(x-1)^(2/3)-0.05*W(x-1))*f(Temp)
Loop while res <= 10

--
Regards,
Tom Ogilvy


"Maarten" wrote in message
...
But B doesn't contain the weights of the animals. The weights should be
calculated invisible, behind each cell in B.
B1 may only contain the number of days the animal needs to reach a
weight
of
10 grams, assuming that it enters the simulation on the day that
corresponds
to cell B1

B2 may only contain the number of days the animal needs to reach a
weight
of
10 grams, assuming that it enters the simulation on the day that
corresponds
to cell B2

B3 may only contain the number of days the animal needs to reach a
weight
of
10 grams, assuming that it enters the simulation on the day that
corresponds
to cell B3

etc...




"Tom Ogilvy" wrote:

If each row is a day, then it should be as simple as:
=countif(B:B,"<=10")

No macro required.

As a further example, If you want to count 10 and <=20

=Countif(B:B,"10")-Countif(B:B,"20")



--
Regards,
Tom Ogilvy

Countnumberofdays while function = TRUE
 

How do I enter this in Excel? Is it possible to write a function with it?
Something like (I don't know how I have to write a correct function):
Function GrowDay(Wstart, Wstop)
x = 1
x = 0
W = Wstart
Do
x = x + 1
res = W(x - 1) + _
(0.1 * W(x - 1) ^ (2 / 3) - 0.05 * W(x - 1)) * f(Temp)
Loop While res <= Wstop
End Function

Maarten

"Tom Ogilvy" wrote:

x = 1
x = 0
do
x = x + 1
res = W(x-1) + _
(0.1*W(x-1)^(2/3)-0.05*W(x-1))*f(Temp)
Loop while res <= 10

--
Regards,
Tom Ogilvy


"Maarten" wrote in message
...
But B doesn't contain the weights of the animals. The weights should be
calculated invisible, behind each cell in B.
B1 may only contain the number of days the animal needs to reach a weight
of
10 grams, assuming that it enters the simulation on the day that
corresponds
to cell B1

B2 may only contain the number of days the animal needs to reach a weight
of
10 grams, assuming that it enters the simulation on the day that
corresponds
to cell B2

B3 may only contain the number of days the animal needs to reach a weight
of
10 grams, assuming that it enters the simulation on the day that
corresponds
to cell B3

etc...




"Tom Ogilvy" wrote:

If each row is a day, then it should be as simple as:
=countif(B:B,"<=10")

No macro required.

As a further example, If you want to count 10 and <=20

=Countif(B:B,"10")-Countif(B:B,"20")



--
Regards,
Tom Ogilvy

Countnumberofdays while function = TRUE
 

Public Function GrowDay(Wstart As Double, Wstop As Double, fTemp As Range)
Dim x As Long, Wold As Double, res As Double
x = 0
Wold = Wstart
Do
x = x + 1
res = Wold + _
(0.1 * Wold ^ (2 / 3) - 0.05 * Wold) * fTemp
Wold = res
If x 30 Then
GrowDay = "at day 31, weight = " & res
Exit Function
End If
Loop While res <= Wstop
GrowDay = x
End Function


Put this in a general module (in the VBE, Insert=Module). Then in b1 put
in

=GrowDay(8,10,A1)

You might need to check your formula (or my implementation of your formula).
I put in a safety so it jumps out after 30 days

--
Regards,
Tom Ogilvy

"Maarten" wrote in message
...
How do I enter this in Excel? Is it possible to write a function with it?
Something like (I don't know how I have to write a correct function):
Function GrowDay(Wstart, Wstop)
x = 1
x = 0
W = Wstart
Do
x = x + 1
res = W(x - 1) + _
(0.1 * W(x - 1) ^ (2 / 3) - 0.05 * W(x - 1)) * f(Temp)
Loop While res <= Wstop
End Function

Maarten

"Tom Ogilvy" wrote:

x = 1
x = 0
do
x = x + 1
res = W(x-1) + _
(0.1*W(x-1)^(2/3)-0.05*W(x-1))*f(Temp)
Loop while res <= 10

--
Regards,
Tom Ogilvy


"Maarten" wrote in message
...
But B doesn't contain the weights of the animals. The weights should
be
calculated invisible, behind each cell in B.
B1 may only contain the number of days the animal needs to reach a
weight
of
10 grams, assuming that it enters the simulation on the day that
corresponds
to cell B1

B2 may only contain the number of days the animal needs to reach a
weight
of
10 grams, assuming that it enters the simulation on the day that
corresponds
to cell B2

B3 may only contain the number of days the animal needs to reach a
weight
of
10 grams, assuming that it enters the simulation on the day that
corresponds
to cell B3

etc...




"Tom Ogilvy" wrote:

If each row is a day, then it should be as simple as:
=countif(B:B,"<=10")

No macro required.

As a further example, If you want to count 10 and <=20

=Countif(B:B,"10")-Countif(B:B,"20")



--
Regards,
Tom Ogilvy

Countnumberofdays while function = TRUE
 

Hi Tom,

This already works fine! With Wstart = 0.0082 and Wstop = 0.1, the
simulation gives a correct result. But ...
The function takes for fTemp a constant (say A1 = 0.5), but the temperature
changes throughout the year and so does fTemp. On day 1 fTemp = 0.30, on day
2 fTemp = 0.31, etc.... So if it takes the animal 30 days to reach 0.1gram,
it is subject to fTemp ranging from 0.30 to 0.60 and not 30 times 0.50.
Is there a way to account for this changing fTemp?

Greets,
Maarten

"Tom Ogilvy" wrote:

Public Function GrowDay(Wstart As Double, Wstop As Double, fTemp As Range)
Dim x As Long, Wold As Double, res As Double
x = 0
Wold = Wstart
Do
x = x + 1
res = Wold + _
(0.1 * Wold ^ (2 / 3) - 0.05 * Wold) * fTemp
Wold = res
If x 30 Then
GrowDay = "at day 31, weight = " & res
Exit Function
End If
Loop While res <= Wstop
GrowDay = x
End Function


Put this in a general module (in the VBE, Insert=Module). Then in b1 put
in

=GrowDay(8,10,A1)

You might need to check your formula (or my implementation of your formula).
I put in a safety so it jumps out after 30 days

--
Regards,
Tom Ogilvy

"Maarten" wrote in message
...
How do I enter this in Excel? Is it possible to write a function with it?
Something like (I don't know how I have to write a correct function):
Function GrowDay(Wstart, Wstop)
x = 1
x = 0
W = Wstart
Do
x = x + 1
res = W(x - 1) + _
(0.1 * W(x - 1) ^ (2 / 3) - 0.05 * W(x - 1)) * f(Temp)
Loop While res <= Wstop
End Function

Maarten

"Tom Ogilvy" wrote:

x = 1
x = 0
do
x = x + 1
res = W(x-1) + _
(0.1*W(x-1)^(2/3)-0.05*W(x-1))*f(Temp)
Loop while res <= 10

--
Regards,
Tom Ogilvy


"Maarten" wrote in message
...
But B doesn't contain the weights of the animals. The weights should
be
calculated invisible, behind each cell in B.
B1 may only contain the number of days the animal needs to reach a
weight
of
10 grams, assuming that it enters the simulation on the day that
corresponds
to cell B1

B2 may only contain the number of days the animal needs to reach a
weight
of
10 grams, assuming that it enters the simulation on the day that
corresponds
to cell B2

B3 may only contain the number of days the animal needs to reach a
weight
of
10 grams, assuming that it enters the simulation on the day that
corresponds
to cell B3

etc...




"Tom Ogilvy" wrote:

If each row is a day, then it should be as simple as:
=countif(B:B,"<=10")

No macro required.

As a further example, If you want to count 10 and <=20

=Countif(B:B,"10")-Countif(B:B,"20")



--
Regards,
Tom Ogilvy

Countnumberofdays while function = TRUE
 

Public Function GrowDay(Wstart As Double, Wstop As Double, fTemp As Range)
Dim x As Long, Wold As Double, res As Double
x = 0
Wold = Wstart
Do
x = x + 1
res = Wold + _
(0.1 * Wold ^ (2 / 3) - 0.05 * Wold) * fTemp(x)
Wold = res
If x 30 Then
GrowDay = "at day 31, weight = " & res
Exit Function
End If
Loop While res <= Wstop
GrowDay = x
End Function

I have modified it to look ahead by day from the start cell you pass in for
ftemp. so the argument list remains the same.

--
Regards,
Tom Ogilvy


"Maarten" wrote in message
...
Hi Tom,

This already works fine! With Wstart = 0.0082 and Wstop = 0.1, the
simulation gives a correct result. But ...
The function takes for fTemp a constant (say A1 = 0.5), but the
temperature
changes throughout the year and so does fTemp. On day 1 fTemp = 0.30, on
day
2 fTemp = 0.31, etc.... So if it takes the animal 30 days to reach
0.1gram,
it is subject to fTemp ranging from 0.30 to 0.60 and not 30 times 0.50.
Is there a way to account for this changing fTemp?

Greets,
Maarten

"Tom Ogilvy" wrote:

Public Function GrowDay(Wstart As Double, Wstop As Double, fTemp As
Range)
Dim x As Long, Wold As Double, res As Double
x = 0
Wold = Wstart
Do
x = x + 1
res = Wold + _
(0.1 * Wold ^ (2 / 3) - 0.05 * Wold) * fTemp
Wold = res
If x 30 Then
GrowDay = "at day 31, weight = " & res
Exit Function
End If
Loop While res <= Wstop
GrowDay = x
End Function


Put this in a general module (in the VBE, Insert=Module). Then in b1
put
in

=GrowDay(8,10,A1)

You might need to check your formula (or my implementation of your
formula).
I put in a safety so it jumps out after 30 days

--
Regards,
Tom Ogilvy

"Maarten" wrote in message
...
How do I enter this in Excel? Is it possible to write a function with
it?
Something like (I don't know how I have to write a correct function):
Function GrowDay(Wstart, Wstop)
x = 1
x = 0
W = Wstart
Do
x = x + 1
res = W(x - 1) + _
(0.1 * W(x - 1) ^ (2 / 3) - 0.05 * W(x - 1)) * f(Temp)
Loop While res <= Wstop
End Function

Maarten

"Tom Ogilvy" wrote:

x = 1
x = 0
do
x = x + 1
res = W(x-1) + _
(0.1*W(x-1)^(2/3)-0.05*W(x-1))*f(Temp)
Loop while res <= 10

--
Regards,
Tom Ogilvy


"Maarten" wrote in message
...
But B doesn't contain the weights of the animals. The weights
should
be
calculated invisible, behind each cell in B.
B1 may only contain the number of days the animal needs to reach a
weight
of
10 grams, assuming that it enters the simulation on the day that
corresponds
to cell B1

B2 may only contain the number of days the animal needs to reach a
weight
of
10 grams, assuming that it enters the simulation on the day that
corresponds
to cell B2

B3 may only contain the number of days the animal needs to reach a
weight
of
10 grams, assuming that it enters the simulation on the day that
corresponds
to cell B3

etc...




"Tom Ogilvy" wrote:

If each row is a day, then it should be as simple as:
=countif(B:B,"<=10")

No macro required.

As a further example, If you want to count 10 and <=20

=Countif(B:B,"10")-Countif(B:B,"20")



--
Regards,
Tom Ogilvy

Countnumberofdays while function = TRUE
 

Great, it works!
Thanks a lot!
Maarten

"Tom Ogilvy" wrote:

Public Function GrowDay(Wstart As Double, Wstop As Double, fTemp As Range)
Dim x As Long, Wold As Double, res As Double
x = 0
Wold = Wstart
Do
x = x + 1
res = Wold + _
(0.1 * Wold ^ (2 / 3) - 0.05 * Wold) * fTemp(x)
Wold = res
If x 30 Then
GrowDay = "at day 31, weight = " & res
Exit Function
End If
Loop While res <= Wstop
GrowDay = x
End Function

I have modified it to look ahead by day from the start cell you pass in for
ftemp. so the argument list remains the same.

--
Regards,
Tom Ogilvy


"Maarten" wrote in message
...
Hi Tom,

This already works fine! With Wstart = 0.0082 and Wstop = 0.1, the
simulation gives a correct result. But ...
The function takes for fTemp a constant (say A1 = 0.5), but the
temperature
changes throughout the year and so does fTemp. On day 1 fTemp = 0.30, on
day
2 fTemp = 0.31, etc.... So if it takes the animal 30 days to reach
0.1gram,
it is subject to fTemp ranging from 0.30 to 0.60 and not 30 times 0.50.
Is there a way to account for this changing fTemp?

Greets,
Maarten

"Tom Ogilvy" wrote:

Public Function GrowDay(Wstart As Double, Wstop As Double, fTemp As
Range)
Dim x As Long, Wold As Double, res As Double
x = 0
Wold = Wstart
Do
x = x + 1
res = Wold + _
(0.1 * Wold ^ (2 / 3) - 0.05 * Wold) * fTemp
Wold = res
If x 30 Then
GrowDay = "at day 31, weight = " & res
Exit Function
End If
Loop While res <= Wstop
GrowDay = x
End Function


Put this in a general module (in the VBE, Insert=Module). Then in b1
put
in

=GrowDay(8,10,A1)

You might need to check your formula (or my implementation of your
formula).
I put in a safety so it jumps out after 30 days

--
Regards,
Tom Ogilvy

"Maarten" wrote in message
...
How do I enter this in Excel? Is it possible to write a function with
it?
Something like (I don't know how I have to write a correct function):
Function GrowDay(Wstart, Wstop)
x = 1
x = 0
W = Wstart
Do
x = x + 1
res = W(x - 1) + _
(0.1 * W(x - 1) ^ (2 / 3) - 0.05 * W(x - 1)) * f(Temp)
Loop While res <= Wstop
End Function

Maarten

"Tom Ogilvy" wrote:

x = 1
x = 0
do
x = x + 1
res = W(x-1) + _
(0.1*W(x-1)^(2/3)-0.05*W(x-1))*f(Temp)
Loop while res <= 10

--
Regards,
Tom Ogilvy


"Maarten" wrote in message
...
But B doesn't contain the weights of the animals. The weights
should
be
calculated invisible, behind each cell in B.
B1 may only contain the number of days the animal needs to reach a
weight
of
10 grams, assuming that it enters the simulation on the day that
corresponds
to cell B1

B2 may only contain the number of days the animal needs to reach a
weight
of
10 grams, assuming that it enters the simulation on the day that
corresponds
to cell B2

B3 may only contain the number of days the animal needs to reach a
weight
of
10 grams, assuming that it enters the simulation on the day that
corresponds
to cell B3

etc...




"Tom Ogilvy" wrote:

If each row is a day, then it should be as simple as:
=countif(B:B,"<=10")

No macro required.

As a further example, If you want to count 10 and <=20

=Countif(B:B,"10")-Countif(B:B,"20")



--
Regards,
Tom Ogilvy