+ Reply to Thread
Results 1 to 3 of 3

Compute MTBF by locating value in rows above

  1. #1
    Registered User
    Join Date
    05-05-2008
    Posts
    9

    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. #2
    Forum Guru XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    6,929

    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).
    Click * below if this answer helped

    Advanced Excel Techniques: http://excelxor.com/

  3. #3
    Forum Guru XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    6,929

    Re: Compute MTBF by locating value in rows above

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

    Regards

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Bookmarks

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