• 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​
 
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.
 
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.
 
Back
Top