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 ?