The problem is with regards to analysing the remaining life of my inventory based on the quantity available and the monthly consumption. Its as follows :
Assuming today's date is 1st of September. I have 20 packets of cheese and the average monthly consumption is 4 packets. Ideally if the shelf life of those 20 packets is 6 months or more, then I can safely say that the total quantity will last for 5 months (20 divided by 4).However, with a slight twist in the data, assume that out of those 20 packets, 6 packets expire on 15 Oct, 7 packets expire on 10 Nov, and the remaining 6 on 15 December. Now I know that the total inventory is not gonna last for 5 months due to various expiry dates. If I calculate this manually, I come to know that the quantity will last for 3 and a half months.
How can i put all this in a formula to get the right answer ?
Array formula in cell E5:
This formula returns months, rounded down. It can calculate up to 99 months. So how did the formula come up with the value 2? Take a look at this table.
Today is 2012-09-01, a month later (2012-10-01) 4 items are consumed and 0 items have expired, total quantity 16.
2012-11-01 a total of 8 items are consumed and 6 items have expired, total quantity 6.
2012-12-01 a total of 12 items are consumed and 13 items have expired, total quantity -5.
All the inventory items are consumed/expired somewhere between 2012-11-01 and 2012-12-01.
2012-11-01 - 2012-09-01 = 2 months