# Compute MTBF by locating value in rows above

1. ## Compute MTBF by locating value in rows above

Hi all,

Hard to describe this question so sorry for the vague subject line...

I was recently asked to compute the mean time between failures (MBTF) for a large data table for many different items we are tracking. Simply put, I have columns of items with a daily entry and either a 0 or 1 for if the device failed. Looks something like this:

 A B C D 1 Date Item 1 Item 2 Item 3 2 Jan 1 1 0 0 3 Jan 2 0 1 1 4 Jan 3 1 0 0 5 Jan 4 0 1 0 6 Jan 5 1 0 0

This goes on for years. I'd like to use VBA code or a function, if possible, to give me the average number of days between failures. For instance, Item 1 would be 2 days; etc.

Anyone have any thoughts on this? I'm a moderate -> advanced (but not expert) Excel user and can adapt VBA code but not necessarily program from scratch.

Thanks.
G

2. ## Re: Compute MTBF by locating value in rows above

Hi.

Perhaps it would help if you gave some slightly larger examples together with your expected results? It's quite evident that the first case has an average of 2, though perhaps not so clear what the answers should be for other datasets.

Anyway, as a shot in the dark, and assuming the data in question is in A2:A6, this array formula**:

=MMULT(AGGREGATE({14,15},,MODE.MULT(IF(B2:B6=1,ROW(B2:B6)),ROW(B2:B6)),1),{1;-1})/(COUNTIF(B2:B6,1)-1)

Regards

**Array formulas are not entered in the same way as 'standard' formulas. Instead of pressing just ENTER, you first hold down CTRL and SHIFT, and only then press ENTER. If you've done it correctly, you'll notice Excel puts curly brackets {} around the formula (though do not attempt to manually insert these yourself).

3. ## Re: Compute MTBF by locating value in rows above

Edit: sorry, meant B2:B6, not A2:A6.

Regards

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1