How to AVERAGEIF Max of 2 columns values per row

[gcefaloni]

Hi, I'm trying to find a way of doing an average of the last column (assuming the max column isn't there). So averageif (max 2 values per row) for the entire range assuming that the result of the max isn't 0. So averageif NOT 0 and the max of the 2 values of Yield to maturity column and 12 month yield column for the entire range in a single cell. How can I do this? I'm trying to replicate the 3.43 figure at the bottom right of the array with the data I attached. THank you!!
[TABLE="width: 550"]
<colgroup><col><col><col><col></colgroup><tbody>[TR]
[TD]Name[/TD]
[TD]Yield to Maturity[/TD]
[TD]12 Mo Yield[/TD]
[TD]MAX[/TD]
[/TR]
[TR]
[TD]ABF Pan Asia Bond Index[/TD]
[TD][/TD]
[TD="align: right"]2.77[/TD]
[TD="align: right"]2.77[/TD]
[/TR]
[TR]
[TD]AdvisorShares Newfleet Mult-Sect Inc ETF[/TD]
[TD="align: right"]2.95[/TD]
[TD="align: right"]2.52[/TD]
[TD="align: right"]2.95[/TD]
[/TR]
[TR]
[TD]Amundi ETF Floating Rate USD Corp USD[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]0.00[/TD]
[/TR]
[TR]
[TD]Amundi ETF Floating Rate USD Corp USD[/TD]
[TD][/TD]
[TD="align: right"]0.00[/TD]
[TD="align: right"]0.00[/TD]
[/TR]
[TR]
[TD]Amundi ETF Global Emerg Bd Markit iBoxx[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]0.00[/TD]
[/TR]
[TR]
[TD]Amundi ETF Global Emerg Bd Markit iBoxx[/TD]
[TD][/TD]
[TD="align: right"]0.00[/TD]
[TD="align: right"]0.00[/TD]
[/TR]
[TR]
[TD]Amundi IS Barclays US Corp BBB 1-5 ETFDR[/TD]
[TD="align: right"]2.34[/TD]
[TD][/TD]
[TD="align: right"]2.34[/TD]
[/TR]
[TR]
[TD]BMO Aggregate Bond ETF[/TD]
[TD="align: right"]2.39[/TD]
[TD="align: right"]3.04[/TD]
[TD="align: right"]3.04[/TD]
[/TR]
[TR]
[TD]BMO Discount Bond ETF[/TD]
[TD="align: right"]2.27[/TD]
[TD="align: right"]2.08[/TD]
[TD="align: right"]2.27[/TD]
[/TR]
[TR]
[TD]BMO Emerging Markets Bd Hdgd to CAD ETF[/TD]
[TD="align: right"]4.34[/TD]
[TD="align: right"]4.65[/TD]
[TD="align: right"]4.65[/TD]
[/TR]
[TR]
[TD]BMO Floating Rate High Yield ETF[/TD]
[TD][/TD]
[TD="align: right"]4.71[/TD]
[TD="align: right"]4.71[/TD]
[/TR]
[TR]
[TD]BMO High Yield US Corporate Bond ETF[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]0.00[/TD]
[/TR]
[TR]
[TD]BMO High Yld US Corp Bd Hdgd to CAD ETF[/TD]
[TD="align: right"]5.66[/TD]
[TD="align: right"]5.55[/TD]
[TD="align: right"]5.66[/TD]
[/TR]
[TR]
[TD]BMO Laddered Preferred Share ETF[/TD]
[TD][/TD]
[TD="align: right"]4.11[/TD]
[TD="align: right"]4.11[/TD]
[/TR]
[TR]
[TD]BMO Long Corporate Bond ETF[/TD]
[TD="align: right"]3.93[/TD]
[TD="align: right"]4.21[/TD]
[TD="align: right"]4.21[/TD]
[/TR]
[TR]
[TD]BMO Long Federal Bond ETF[/TD]
[TD="align: right"]2.35[/TD]
[TD="align: right"]3.05[/TD]
[TD="align: right"]3.05[/TD]
[/TR]
[TR]
[TD]BMO Long Provincial Bond ETF[/TD]
[TD="align: right"]3.15[/TD]
[TD="align: right"]3.51[/TD]
[TD="align: right"]3.51[/TD]
[/TR]
[TR]
[TD]BMO Mid Corporate Bond ETF[/TD]
[TD="align: right"]3.01[/TD]
[TD="align: right"]3.10[/TD]
[TD="align: right"]3.10[/TD]
[/TR]
[TR]
[TD]BMO Mid Federal Bond ETF[/TD]
[TD="align: right"]2.04[/TD]
[TD="align: right"]2.12[/TD]
[TD="align: right"]2.12[/TD]
[/TR]
[TR]
[TD]BMO Mid Provincial Bond ETF[/TD]
[TD="align: right"]2.43[/TD]
[TD="align: right"]2.99[/TD]
[TD="align: right"]2.99[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]AVERAGEIF NOT 0[/TD]
[TD="align: right"]3.43[/TD]
[/TR]
</tbody>[/TABLE]

 

[barry houdini]

If the two columns are B2:B21 and C2:C21 you can use this array formula

=AVERAGE(IF(B2:B21>C2:C21,B2:B21,IF(C2:C21<>0,C2:C21)))

confirm with CTRL+SHIFT+ENTER

I get 3.43 with your data

 

[FDibbins]

Another approach might be to use LARGE() in Barry's suggestion. You would probably still need to make it an array formula...=large(if(range=name) etc

 

[gcefaloni]

Thank you guys, is it possible to do that formula within a Pivot Table? As in a calculated field.