SJC Excel Junkie
New Member
- Joined
- Feb 9, 2021
- Messages
- 11
- Office Version
- 2019
- Platform
- Windows
I have the data collection sheet below I need help for the formula in the Overall AVG MTBF (hrs) column.
So if there is Production time, but no downtime then the result should be 7.833
If there is NO Production time then the result needs to be 0
I have gotten to the 7.833 but can figure out how to add the extra formula to check for production time....please help
So if there is Production time, but no downtime then the result should be 7.833
If there is NO Production time then the result needs to be 0
I have gotten to the 7.833 but can figure out how to add the extra formula to check for production time....please help
Test1.xlsx | ||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
B | C | D | E | F | G | H | I | J | K | L | M | |||
2 | Daily MTTR/MTBF By Asset | Shift | MTTR | MTBF | Production Time (mins) | MTN Downtime (mins) | MTN Downtime | Asset Availability | Overall AVG MTTR (hrs) | Overall AVG MTBF (hrs) | Overall AVG MTN Downtime % | |||
3 | ||||||||||||||
4 | 1 | Midnights | 0.00 | 0.00 | 0 | 0.00 | 0.00% | N/A | 0.139 | 1.583 | 4.03% | |||
5 | Days | 8.33 | 95.00 | 310 | 25.00 | 8.06% | 92.54% | |||||||
6 | Afternoons | 0.00 | 0.00 | 310 | 0.00 | 0.00% | 100.00% | |||||||
7 | 2 | Midnights | 0.00 | 0.00 | 0 | 0.00 | 0.00% | N/A | 0 | 7.833 | 0.00% | |||
8 | Days | 0.00 | 0.00 | 425 | 0.00 | 0.00% | 100.00% | |||||||
9 | Afternoons | 0.00 | 0.00 | 425 | 0.00 | 0.00% | 100.00% | |||||||
10 | 3 | Midnights | 0.00 | 0.00 | 0 | 0.00 | 0.00% | N/A | 0 | 7.833 | 0.00% | |||
11 | Days | 0.00 | 0.00 | 425 | 0.00 | 0.00% | 100.00% | |||||||
12 | Afternoons | 0.00 | 0.00 | 425 | 0.00 | 0.00% | 100.00% | |||||||
13 | 4 | Midnights | 0.00 | 0.00 | 0 | 0.00 | 0.00% | N/A | 0 | 0 | 0 | |||
14 | Days | 0.00 | 0.00 | 0 | 0.00 | 0.00% | N/A | |||||||
15 | Afternoons | 0.00 | 0.00 | 0 | 0.00 | 0.00% | N/A | |||||||
18 |
Cell Formulas | ||
---|---|---|
Range | Formula | |
I4:I15 | I4 | =IFERROR((G4/F4),0) |
J4:J15 | J4 | =IFERROR(F4/(F4+G4),"N/A") |
K13:L13,K10,K7,K4:L4 | K4 | =IFERROR((AVERAGEIF(D4:D6,"<>0")/60),"0") |
M4,M13,M10,M7 | M4 | =IFERROR((SUM(I4:I6)/COUNTIF(F4:F6,"<>0")),"0") |
L7,L10 | L7 | =IFERROR((AVERAGEIF(E7:E9,"<>0")/60),"7.833") |