+ Reply to Thread
Results 1 to 3 of 3

Compute MTBF by locating value in rows above

  1. 01-09-2015, 02:23 PM #1
    Grasor
    Grasor is offline
    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
    Reply With QuoteRegister To Reply
  2. 01-09-2015, 02:46 PM #2
    XOR LX
    XOR LX is offline
    Forum Expert XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    7,742

    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/
    Reply With QuoteRegister To Reply
  3. 01-09-2015, 03:04 PM #3
    XOR LX
    XOR LX is offline
    Forum Expert XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    7,742

    Re: Compute MTBF by locating value in rows above

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

    Regards
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Help with locating recurring number combinations in different rows and colums
    By Eggsel in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 03-11-2013, 07:36 PM
  2. Count and compute rows of data with different catagories and description 2003 format
    By bsengineer in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 09-28-2009, 08:51 PM
  3. Locating which rows the first and last occurances of an entry fall on
    By Christian411 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 05-21-2009, 11:54 AM
  4. Any way to make SUMIF only compute visible rows?
    By gtzpower in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 06-27-2007, 11:54 AM
  5. Locating Row
    By jtwork in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 04-20-2007, 04:59 AM

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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1