No IFS ands or BUTs

[SJC Excel Junkie]

I am trying to combine these two formulas - basically if the machine didn't run the sum of BA12:BA14 will be 0 and the result in BG should be 0
If the machine did run then BG should be the average of AZ12:AZ14 in hours or if there is no errors during the run it should be 7.833

HELP!!

IF((SUM(BA12:BA14)),0,0)

IFERROR((AVERAGEIF(AZ12:AZ14,"<>0")/60),"7.833")

 

[Joe4]

I am finiding your question a bit confusing, and am guessing perhaps others are too.
I think it would be beneficial if you could post a small example of your data, and tell us what your expected result is based on that data.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

 

[SJC Excel Junkie]

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")

 

[SJC Excel Junkie]

thank you Joe - I have figured out how to fix it - and will repost

 

[Fluff]

Is this what you mean

=IF(SUM(F13:F15)=0,0,IFERROR(AVERAGEIF(E13:E15,"<>0")/60,7.833))

 

[SJC Excel Junkie]

worked perfectly Fluff

 

[Fluff]

Glad we could help & thanks for the feedback