Difficult (for me) formula/UDF calculation
 

I need a formula and am finding it difficult. I created a UDF to avoid
hassles with #VALUE! etc (sometimes a "?" is entered so I can't divide
it), giving me trouble but am stuck on this.

Imagine I have to take samples from a number of containers. The number
of samples needed (every 4th container, every 20th, etc) varies
according to source. There must always be a minimujm of 2 samples if the
containers number more than 1.

Assuming every 3rd container is to be sampled,

In my UDF I have a select case with
case 1 sample = 1 (this covers 1st)
case 2 to number_of_containers+1 sample = 2 (this covers 1st & 4th)
else

It is the else bit that has me stumped. I had

= total_containers/skip_rate + 1 but she no work.

I find it hard because I am not mathematically inclined and the fact
that the samples required change from every 3rd, 4th and up to 60th
really throws me. Is there something staring me in the eye that I'm not
seeing? Probably.

So, given 44 containers, if I need every 3rd, how can I calculate it?
The same calculation has to work with every 7th, every 11th, etc.

TIA.

One way:

You can use the \ operator, which returns an integral result


Public Function NumSamples(total_containers As Long, _
skip_rate As Long) As Long
Dim nTemp As Long
If total_containers = 1 Then
nTemp = 1
Else
nTemp = Application.Max(2, _
(total_containers - 1) \ skip_rate + 1)
End If
NumSamples = nTemp
End Function




In article et.au,
Mike Echo wrote:

I need a formula and am finding it difficult. I created a UDF to avoid
hassles with #VALUE! etc (sometimes a "?" is entered so I can't divide
it), giving me trouble but am stuck on this.

Imagine I have to take samples from a number of containers. The number
of samples needed (every 4th container, every 20th, etc) varies
according to source. There must always be a minimujm of 2 samples if the
containers number more than 1.

Assuming every 3rd container is to be sampled,

In my UDF I have a select case with
case 1 sample = 1 (this covers 1st)
case 2 to number_of_containers+1 sample = 2 (this covers 1st & 4th)
else

It is the else bit that has me stumped. I had

= total_containers/skip_rate + 1 but she no work.

I find it hard because I am not mathematically inclined and the fact
that the samples required change from every 3rd, 4th and up to 60th
really throws me. Is there something staring me in the eye that I'm not
seeing? Probably.

So, given 44 containers, if I need every 3rd, how can I calculate it?
The same calculation has to work with every 7th, every 11th, etc.

TIA.

In article ,
says...

One way:

You can use the \ operator, which returns an integral result


Public Function NumSamples(total_containers As Long, _
skip_rate As Long) As Long
Dim nTemp As Long
If total_containers = 1 Then
nTemp = 1
Else
nTemp = Application.Max(2, _
(total_containers - 1) \ skip_rate + 1)
End If
NumSamples = nTemp
End Function

You are very clever, J. E.! I had never even heard of the \ operator.

This made me realise I forgot one condition - if the
total_containers/skip_rate has a remainder then one more container needs
to be sampled (as the "final" container). If skip=3 and containers = 11
or 12 then sample = 5 (1,4,7,10,last). I tried to add a line using MOD
but I messed it up. Any idea?

Thanks very much for your help on this, much appreciated.

One way:

Public Function NumSamples(total_containers As Long, _
skip_rate As Long) As Long
Dim nTemp As Long
If total_containers <= 2 Then
nTemp = total_containers
Else
nTemp = (total_containers - 1) \ skip_rate + 1 - _
((total_containers - 1) Mod skip_rate 0)
End If
NumSamples = nTemp
End Function




In article et.au,
Mike Echo wrote:

This made me realise I forgot one condition - if the
total_containers/skip_rate has a remainder then one more container needs
to be sampled (as the "final" container). If skip=3 and containers = 11
or 12 then sample = 5 (1,4,7,10,last). I tried to add a line using MOD
but I messed it up. Any idea?

In article ,
says...

Public Function NumSamples(total_containers As Long, _
skip_rate As Long) As Long
Dim nTemp As Long
If total_containers <= 2 Then
nTemp = total_containers
Else
nTemp = (total_containers - 1) \ skip_rate + 1 - _
((total_containers - 1) Mod skip_rate 0)
End If
NumSamples = nTemp
End Function

Thank you very much, J. E. I don't understand the thing but it works
perfectly. :-)