Finding coverage of the range

darius_s · Oct 1, 2019

Hi all,

I have got an interesting problem . I have got a sample Data below:

[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[TD]I[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]1[/TD]
[TD]2[/TD]
[TD]4[/TD]
[TD]4[/TD]
[TD]1[/TD]
[TD]2[/TD]
[TD]2[/TD]
[TD]3[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]13[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]5.5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

I have got a target number in A2. I need to find how much of the range A1:I1 that number would cover. Each cell in the range is a day, and the value is sales per that day.

In the example, the coverage is 5.5 days (A3 cell) as I need A1:E1 and half of F1 to match the number.

I would have a few thousand calculations in the sheet, finding coverage for different products for stock levels in various points of time.

Currently I am using IFS function, that checks all of the scenarios (will stock cover 1 day -> what about 2 -> and 3 ->and 4 -> and so on...).

The current approach is working, but makes the entire calculation massive as I have more than 15 if conditions.

Multiply all that by a thousand or so of formula instances and we bordering unacceptable performance levels.

I was wondering if anyone has encountered something similar and has found a more elegant solution.

NdNoviceHlp · Oct 1, 2019

Place this in module code...

Code:

Public Function CoverDays(Rng As Range, Result As Integer) As Double
'=Coverdays(A1:I1,A2)
Dim R As Range, Tot As Double, Temp As Double, Cnt As Integer
For Each R In Rng
Tot = Tot + R.Value
If Tot > Result Then
CoverDays = (Result - Temp) / R.Value + Cnt
Exit Function
End If
Temp = Tot
Cnt = Cnt + 1
Next R
End Function

To operate with the data as shown. Place this formula in A3...

Code:

=Coverdays(A1:I1,A2)

HTH. Dave

darius_s · Oct 2, 2019

Hi Dave,

Thanks for pointing to a direction of custom formulas.

Neat for sure. I will see what are the results performance wise.

darius_s · Oct 2, 2019

I did some testing. I had to declare results as "double", as other vise i was getting an error. Potentially integer overflow.

Performance wise, custom function has a slight performance penalty. Unfortunately I will have to live with massive formula.

But it was a very good try and definitely something learned :D

Fluff · Oct 2, 2019

If you are looking for performance, then ditch the IFS formula & replace it with a nested IF formula instead.
The new IFS function will evaluate the entire formula, whereas the old style IF function will only evaluate the true or false

Marcelo Branco · Oct 2, 2019

Maybe something like this...

[Table="class: grid"][tr][td="bgcolor: #DCE6F1"][/td][td="bgcolor: #DCE6F1"]

A

[/td][td="bgcolor: #DCE6F1"]

B

[/td][td="bgcolor: #DCE6F1"]

C

[/td][td="bgcolor: #DCE6F1"]

D

[/td][td="bgcolor: #DCE6F1"]

E

[/td][td="bgcolor: #DCE6F1"]

F

[/td][td="bgcolor: #DCE6F1"]

G

[/td][td="bgcolor: #DCE6F1"]

H

[/td][td="bgcolor: #DCE6F1"]

I

[/td][/tr]
[tr][td="bgcolor: #DCE6F1"]

1

[/td][td="bgcolor:#D9D9D9"]

1

[/td][td="bgcolor:#D9D9D9"]

3

[/td][td="bgcolor:#D9D9D9"]

7

[/td][td="bgcolor:#D9D9D9"]

11

[/td][td="bgcolor:#D9D9D9"]

12

[/td][td="bgcolor:#D9D9D9"]

14

[/td][td="bgcolor:#D9D9D9"]

16

[/td][td="bgcolor:#D9D9D9"]

19

[/td][td="bgcolor:#D9D9D9"]

23

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

2

[/td][td]

1

[/td][td]

2

[/td][td]

4

[/td][td]

4

[/td][td]

1

[/td][td]

2

[/td][td]

2

[/td][td]

3

[/td][td]

4

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

3

[/td][td]

Target

[/td][td]

Result