Months on Hand Inventory

[TG2812]

Hello All,

I'm currently looking for a way to calculate months on hand inventory. Looking around this forum I could not really find a simple way to get rid of my problem.
My dataset is as follows;

[TABLE="width: 500"]
<tbody>[TR]
[TD]Model[/TD]
[TD]Region[/TD]
[TD]Configuration[/TD]
[TD]Measure[/TD]
[TD]Jan-19[/TD]
[TD]Feb-19[/TD]
[TD]Mar-19[/TD]
[TD]Apr-19[/TD]
[TD]May-19[/TD]
[TD]Jun-19[/TD]
[TD]Jul-19[/TD]
[/TR]
[TR]
[TD]Model 1[/TD]
[TD]Region 1[/TD]
[TD]Configuration 1[/TD]
[TD]My sales forecast[/TD]
[TD]3[/TD]
[TD]2[/TD]
[TD]0[/TD]
[TD]2[/TD]
[TD]12[/TD]
[TD]8[/TD]
[TD]10[/TD]
[/TR]
[TR]
[TD]Model 1[/TD]
[TD]Region 1[/TD]
[TD]Configuration 1[/TD]
[TD]My inventory[/TD]
[TD]23[/TD]
[TD]20[/TD]
[TD]20[/TD]
[TD]18[/TD]
[TD]6[/TD]
[TD]-2[/TD]
[TD]-12[/TD]
[/TR]
[TR]
[TD]Model 1[/TD]
[TD]Region 1[/TD]
[TD]Configuration 1[/TD]
[TD]My Inventory cover (in mths)[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

If the returned results is greater than 9 mths of coverage, then formula should return "> 9 mths"

Thank you all in advance for your time and great help.

 

[marious]

	A	B	C	D	E	F	G	H	I	J	K	L	M

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]4[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]Model[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]Region[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]Configuration[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]Measure[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]19-Jan[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]19-Feb[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]19-Mar[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]19-Apr[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]19-May[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]19-Jun[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]19-Jul[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]Model 1[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]Region 1[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]Configuration 1[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]My sales forecast[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]3[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]2[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]0[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]2[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]12[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]8[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]10[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]Model 1[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]Region 1[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]Configuration 1[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]My inventory[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]23[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]20[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]20[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]18[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]6[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]-2[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]-12[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]Model 1[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]Region 1[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]Configuration 1[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]My Inventory cover (in mths)[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]FALSE[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

Sheet1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]F6[/TH]
[TD="align: left"]=E6-E5[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]E7[/TH]
[TD="align: left"]=IF(SUM(E5:M5)<E6,"> 9 mths",FALSE)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

 

[TG2812]

Hi

I tried your formula but it does not work..not sure I understood what you were trying to do here.
Could you please shed some more lights?

Thanks!

 

[Fluff]

Maybe

Excel 2013/2016
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	Model	Region	Configuration	Measure	Jan-19	Feb-19	Mar-19	Apr-19	May-19	Jun-19	Jul-19	Aug-19	Sep-19	Oct-19
2	Model 1	Region 1	Configuration 1	My sales forecast	3	2	0	2	12	8	10	1	3	3
3	Model 1	Region 1	Configuration 1	My inventory	40	37	35	35	33	21	13	3	2	-1
4	Model 1	Region 1	Configuration 1	My Inventory cover (in mths)	9	8	7	6	5	4	3	2	1	0
sheet2

Cell Formulas
Range	Formula
E4	{=MATCH(TRUE,E$3:N$3<0,0)-1}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[TG2812]

Hi ,

It unfortunately does not work..

 

[marious]

Hi

I tried your formula but it does not work..not sure I understood what you were trying to do here.
Could you please shed some more lights?

Thanks!

Sorry about that <e6, <e6="" ,<e6,<e6,<e6,"="">=IF(SUM(E5:M5) < E6 ,"> 9 mths",FALSE)

</e6,>

 

[Fluff]

Hi ,

It unfortunately does not work..

Are results shown in post#4 what you are looking for?

 

[TG2812]

Hi Fluff, Marious,

Thank you both for your time. Unfortunately, it still does not work.

However, I found a UDF on this forum which does the trick. What I'm trying to achieve here is to transform it into a native Excel formula where result >9 should be written "> 9 mths"
I did enter this function in cell E4 = MOH(E2,F3:N3)


Function MOH(Inv As Double, MOHArray)

MOH = 0
Dim i As Variant

For Each i In MOHArray
If Inv = 0 Then Exit Function
If Inv < i Then
MOH = MOH + Inv / i
Exit Function
Else
Inv = Inv - i
MOH = MOH + 1
End If
Next i

End Function

 

[Fluff]

Can you show us the results you would expect from your data & explain how you get that result.

With the data you supplied that function returns 0.15 for E4

 

[TG2812]

So here is the dataset i used for the example:

[TABLE="width: 1021"]
<colgroup><col><col><col><col><col><col span="3"><col span="4"></colgroup><tbody>[TR]
[TD]Model[/TD]
[TD]Region[/TD]
[TD]Configuration[/TD]
[TD]Measure[/TD]
[TD="align: right"]Jan-19[/TD]
[TD="align: right"]Feb-19[/TD]
[TD="align: right"]Mar-19[/TD]
[TD="align: right"]Apr-19[/TD]
[TD="align: right"]May-19[/TD]
[TD="align: right"]Jun-19[/TD]
[TD="align: right"]Jul-19[/TD]
[TD="align: right"]Aug-19[/TD]
[/TR]
[TR]
[TD]Model 1[/TD]
[TD]Region 1[/TD]
[TD]Configuration 1[/TD]
[TD]My sales forecast[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]12[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]Model 1[/TD]
[TD]Region 1[/TD]
[TD]Configuration 1[/TD]
[TD]My inventory[/TD]
[TD="align: right"]40[/TD]
[TD="align: right"]37[/TD]
[TD="align: right"]35[/TD]
[TD="align: right"]35[/TD]
[TD="align: right"]33[/TD]
[TD="align: right"]21[/TD]
[TD="align: right"]13[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD]Model 1[/TD]
[TD]Region 1[/TD]
[TD]Configuration 1[/TD]
[TD]My inventory cover (in mths)[/TD]
[TD="align: right"]8.67 (cell E5)[/TD]
[TD="align: right"]7.33[/TD]
[TD="align: right"]5.67[/TD]
[TD="align: right"]5.33[/TD]
[TD="align: right"]9.00[/TD]
[TD="align: right"]9.00[/TD]
[TD="align: right"]9.00[/TD]
[TD="align: right"]1.00[/TD]
[/TR]
</tbody>[/TABLE]


Cell E5 formula = MOH (E4,F3:N3). I then dragged this formula across the fifth row.

Hope this helps.