• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Months of inventory count

tdl

New Member
I have a bunch of estimated monthly sales and an existing inventory, I'm trying to calculate how many months of inventory I have. Here is a simple example:

InventoryJan SalesFeb SalesMar SalesApr Sales
10090103010

So the answer would be 2.

I've tried a bunch of ways, any help would be great!

This was as close as I could get, but I'm getting an incorrect answer
=MATCH(B2,INDEX(SUBTOTAL(9,OFFSET(A2,,,,COLUMN($A$2:$BF$2))),0))

This was the sample set I used and it doesn't work.
Units on hand: 3230
Sales #
125125125125125241241
241​
241
147​
147​
147​
147​
147​
180​
180​
180​
180​
130​
130​
130​
 

vletm

Excel Ninja
tdl
Please, reread Forum Rules
There are many good advices - How to get the Best Results at Chandoo.org
eg without Your sample Excel file
... You would need to do a lot of changes to You Excel file, if someone will give an answer.
 

tdl

New Member
Thanks. I've spent hours on this must have goofed. Any idea why A2 has to be blank? I tried extending this function to skip the first month and it blows up whenever A2 isn't blank.
 
Top