formula to calculate MTBF and MTTR

You can't really get MTBF here because you only know when the repair started, not when the failure occurred. If you assume that repair start is equivalent to point of failure then you can use that.

Also MTTR is mean time to repair.

This might be possible with array formulas but it's easier to understand if you use a helper column that lists the time since the last failure, and the time to repair. I have used your data to create a file, attached. Your data also must be sorted first.

Next time, attach your file. It's easier than taking a screenshot first and then attaching that. A file is worth a thousand words. The paper clip icon does not work for attachments. To attach a file, under the text box where you type your reply click the Go Advanced button. On the next screen scroll down and click on Manage Attachments, which will show a pop-up window to Select and Upload a file. Then close the window.

A bit confusing, but does this array formula in AK2, copied down, do it??

=AVERAGE(IF([Equip No]=[@[Equip No]],[End Repair Date]-[Start Repair Date]))

Array Formulae are a little different from ordinary formulae in that they MUST be confirmed in the FIRST CELL ONLY by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. After that, the array can be dragged down as normal, to cover the desired range.

You will know the array is active when you see curly brackets { } - or "curly braces" for those of you in the USA, or "flower brackets" for those of you in India - appear around the outside of your formula. If you do not use CTRL+SHIFT+ENTER you will (almost always) get an error message or an incorrect answer. Press F2 on that cell and try again.

Don't type the curly brackets yourself - it won't work...

Fotmat as [h]:mm:ss

Hi there

I just looked at this and the last excel example provided. You need to understand how to calculate Mean Time Between Failure and Mean Time To Repair before you apply it in to your excel data.

MTBF is The Sum of (start of downtime - start uf uptime) all divided by no of failures.
This will need to be by asset or system type and not all failures.

MTTR is total maintenance time divided by number of repairs
Once again, needs to be by specific asset or system.

I hope that helps?

Hi there, as an engineering management consultant I can most definitely assist you.

What I need to understand is to what asset level are you trying to measure MTBF and MTTR?!

System, individual asset, sub asset, component ?

It appears you are measuring by Equip No in B:B
There is a problem with your data, which I take it is coming from an EAM or CMMS system?
The start date does not have a time configuration, just a date.

How do you intend to calculate the amount of time a piece of equipment is offline?

Is request date in X:X a system generated stamp when an issue is logged? AA:AA is when the asset is returned to service?

So, we could calculate 'downtime' from AA-X ?

yes but that isnt a true MTTR. When it is reported is when the item has failed? (X:X) I agree with AA:AA

OK I have done the MTTR calculation. (Total downtime / No of Incidents)

I added two columns in you RAW data table - Downtime and dt2 (Columns AO and AP). AO is for human readable time and AP is for calculation.

A new Tab "MTTR" uses SUMIF and COUNTIF to pull that info together.

I will work on the MTBF later

Hope this helps

Hi there

I have attached the new version of the document which includes both MTTR and MTBF.

I have created more columns than you would actually need if you wanted to nest forumlas together, but for the sake of you following how I achieve
it I thought it best to show you the working out.

Technically speaking I should have an IF statement in the MTBF calculation, if there is only one failure then you cannot yet calculate an MTBF!

Finally, on the last row of the MTBF table I left a TEST line in so you can play about with dates, times and incidents to prove it works.

For your dashboard, I would create charts from the individual tables, or create a new combined 'calculations' table, I can see you using sliders. I always like to keep my RAW
data untouched so I can cut and paste fresh data over it, then my results table is always 'clean' as it were.

One last point. The equipment number in the results table was a cut/paste/remove duplicates from your master data. If new equipment has failures then their number will need to be
included in the results table. You could easily record a macro to automate the process.

Hope that helps?
We would be happy to assist you

Good luck with your project!

MIN(IF and MAX(IF have a completely different syntax and are array formulas.

Array Formulae are a little different from ordinary formulae in that they MUST be confirmed in the FIRST CELL ONLY by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. After that, the array can be dragged down as normal, to cover the desired range.

You will know the array is active when you see curly brackets { } - or "curly braces" for those of you in the USA, or "flower brackets" for those of you in India - appear around the outside of your formula. If you do not use CTRL+SHIFT+ENTER you will (almost always) get an error message or an incorrect answer. Press F2 on that cell and try again.

Don't type the curly brackets yourself - it won't work...

Here is the same file as posted by EdRooney, but with a 2007 -friendly formula

EdRooney - see our rule regarding the promotion of paid services:

11. Do not put links to personal sites or services as your sole contribution here on the forum. (B)

12. Do not solicit payments in your posts or signatures. (B)

We value your contributions here, however your last post overstepped the mark and has been edited accordingly. You should not advertise any paid professional services. Thank you for your co-operation.

You're welcome.

---

If that takes care of your original question, please select "Thread Tools" from the menu link above and mark this thread as SOLVED.

It'd also be appreciated if you were to click the Add Reputation button at the foot of any of the posts of all members who helped you reach a solution.

OOPS! Sorry. Thank you for the edit. Sorry for breaking the rules.

Thanks for sorting Glenn

Originally Posted by Osama73

Hi Ed rooney,
Actually I fully appressiate for your Excell sheet
When we want to calculate MTBF for equipment and component
different authors but different type for calculation as follows

Administrative Note:

Welcome to the forum.

We are happy to help, however while you feel your request is similar to this thread, experience has shown that things soon get confusing when answers refer to particular cells/ranges/sheets which are unique to your post and not relevant to the original. Please start a new thread - See Forum rule #4

If you are not familiar with how to start a new thread see the FAQ: How to start a new thread

Administrative Note:

Welcome to the forum.

We are happy to help, however whilst you feel your request is similar to this thread, experience has shown that things soon get confusing when answers refer to particular cells/ranges/sheets which are unique to your post and not relevant to the original. Please start a new thread - See Forum rule #4

If you are not familiar with how to start a new thread see the FAQ: How to start a new thread

@barreraj1 What are you looking to learn to do?

Is it Excel and Formula etc? If so there is plenty of assistance on this great forum.
If it is the understanding of MTTR, MTBF, OEE etc then that comes from studying etc.

If you have a specific request, post it to the forum and I know from my own experience of reaching out, there is always as least one person who is happy to help.

Administrative Note:

Welcome to the forum.

We are happy to help, however whilst you feel your request is similar to this thread, experience has shown that things soon get confusing when answers refer to particular cells/ranges/sheets which are unique to your post and not relevant to the original.

Please see Forum Rule #4 about hijacking and start a new thread for your query.

If you are not familiar with how to start a new thread see the FAQ: How to start a new thread