Wearout Determination

**Pauleyb** · 10-23-2012, 04:44 PM

I have this solved through some helper columns, but I was curious if anyone can deduce a single formula solution...

Basically, I have a piece of equipment that can make 4000 units before it breaks. I have a yearly forecast of units needed. I would like to determine at what time the equipment will break (assuming each annual build is evenly distributed by day).

Attached is a sample file with the answers 'hard coded'. It shows that the equipment should break on 8/7/2014.

Thanks,
Pauley

**shg** · 10-23-2012, 05:00 PM

Maybe ...

Please Login or Register  to view this content.

**Pauleyb** · 10-23-2012, 05:12 PM

Different approach with the helper columns (although you used a helper row), but I like the use of PERCENTILE and PERCENTRANK. I was going down a path of a SUMPRODUCT with some arrays, but could not figure out how to do the cumulative adding (or subtracting) without helper columns. Let me play around with your idea a bit.

Thanks,
Pauley

**shg** · 10-23-2012, 05:14 PM

You could skip the additional cells with a SUBTOTAL(INDIRECT()) construct, but it would be volatile. The cumulative production numbers also give a more intuitive sense that the answer is correct.

**Pauleyb** · 10-23-2012, 05:24 PM

Just noticed, it is a year (minus a day) off. Should be 8/7/2014. Per the example above, by the end of 2013, only 2200 have been made.

Pauley

**shg** · 10-23-2012, 05:42 PM

Then change the dates to reflect the day on which that number have been manufactured, rather than the plan for the coming year. That's how I interpreted the data.

If you plotted the cumulative production, wouldn't that be the more common interpretation?