Finding coverage of the range

[darius_s]

Hi all,

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

	A	B	C	D	E	F	G	H	I
1	1	2	4	4	1	2	2	3	4
2	13
3	5.5

<tbody>
</tbody>

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]

Place this in module 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...

=Coverdays(A1:I1,A2)

HTH. Dave

 

[darius_s]

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]

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]

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]

Maybe something like this...

	A​	B​	C​	D​	E​	F​	G​	H​	I​
1​	1​	3​	7​	11​	12​	14​	16​	19​	23​
2​	1​	2​	4​	4​	1​	2​	2​	3​	4​
3​	Target​	Result​
4​	4​	2,25​
5​	13​	5,5​
6​	6​	2,75​
7​	7​	3​
8​	14​	6​
9​	2​	1,5​
10​	1​	1​
11​	19​	8​
12​	20​	8,25​
13​	23​	9​
14​	25​	NA​
15​	3​	2​

Data in A2:I2

Helper row (gray area)
Formula in A1 copied across
-SUM($A2:A2)

Target in A4:A...

Formula in B4 copied down
=IF(A4>I$1,"NA",MATCH(A4,A$1:I$1)+(A4-INDEX(A$1:I$1,MATCH(A4,A$1:I$1)))/IFERROR(INDEX(A$2:I$2,MATCH(A4,A$1:I$1)+1),I$2))

Hope this helps

M.

 

[Marcelo Branco]

oops... typo

Formula in A1 copied across
=SUM($A2:A2)

M.