Simple Function

[spcalan]

I call this function to calculate current months of coverage.

It compares 1 column (Inventory ) to a range of cells ( future forecast ) and give me a current month's of coverage:

Function coverage(inventory, forecast)
Application.Volatile
For Each c In forecast
inventory = inventory - c.Value
Select Case inventory
Case Is > 0
coverage = coverage + 1
Case Is = 0
coverage = coverage + 1
Exit Function
Case Is < 0
If c.Value <> 0 Then
coverage = coverage + (1 - (Abs(inventory) / c.Value))
Else
coverage = coverage
End If
Exit Function
End Select
Next c
End Function



it works beautiful...
So I call it like this:
=coverage(C1,CE:CM)

I want to be able to do 1 small adjustment:
=coverage(C1,(ce*.23):CM )

So I am only taking a portion of the first month

any ideas ?

 

[Joe4]

=coverage(C1,(ce*.23):CM )

What exactly is the ".23" supposed to represent?
How do we translate that into a real range?

 

[spcalan]

Column CE represents the first month of forecast, but since we only have 23% of the month left - I want to take the Column CE and multiply by .23.

So instead of =coverage(C1,CE:CM)

=coverage(C1,(CE*.23):CM )

 

[Joe4]

I want to take the Column CE and multiply by .23.

I am not sure what that means.
Multiple the TOTAL in the column by that amount?
Or take 23% of the rows?
What about all the other columns (out to CM)?

Perhaps you can post a small data sample and walk us through an actual example of how this should all work?

 

[spcalan]

Let me start over:

Function has 2 parts.
1. Beginning Inventory Cell
2. Range of monthly forecast cells

then you call it:

=coverage(C7,E7:J7)

C7 = beginning Inventory
E7 : J7 represents 6 months of forecast ( April through September )

But...
For E7.... I need only 23% of that value, and 100% of the remainder

So broken out

=coverage(Inventory, Forecast Range )

Inventory = C7
Forecast Range: E7*.23,F7:J7)

=coverage(C7,E7*.23,F7:J7)

 

[Joe4]

But it won't always be .23, right?
It could be another decimal number, yes?

If so, I recommend adding a third argument to your function so you can feed in whatever fractional amount you want, i.e.

Function coverage(inventory As Range, forecast As Range, [COLOR=#ff0000]frac As Double[/COLOR]) As Double

By the way, in creating your functions, you should always declare the types of variables that you can feed in. It will help reduce errors, and also helps tell people what kind of data it is looking for.

 

[spcalan]

No, the percentage will be from another cell reference.

The first month will be the only one with a multiplier..

It's a percentage of the first month, but the full values of the other months.

 

[Joe4]

No, the percentage will be from another cell reference.

That is fine, as you can feed that cell reference into the equation.

Since that number isn't always the same (could be .23, could be 1, could be something else) and it depends on the situation, you probably want to want make it part of your parameters into the function, unless you can determine exactly which multiplier value to use based solely on the first two parameters you are passing in.