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!!
<colgroup><col><col><col><col></colgroup><tbody>
</tbody>
Name | Yield to Maturity | 12 Mo Yield | MAX |
ABF Pan Asia Bond Index | 2.77 | 2.77 | |
AdvisorShares Newfleet Mult-Sect Inc ETF | 2.95 | 2.52 | 2.95 |
Amundi ETF Floating Rate USD Corp USD | 0.00 | ||
Amundi ETF Floating Rate USD Corp USD | 0.00 | 0.00 | |
Amundi ETF Global Emerg Bd Markit iBoxx | 0.00 | ||
Amundi ETF Global Emerg Bd Markit iBoxx | 0.00 | 0.00 | |
Amundi IS Barclays US Corp BBB 1-5 ETFDR | 2.34 | 2.34 | |
BMO Aggregate Bond ETF | 2.39 | 3.04 | 3.04 |
BMO Discount Bond ETF | 2.27 | 2.08 | 2.27 |
BMO Emerging Markets Bd Hdgd to CAD ETF | 4.34 | 4.65 | 4.65 |
BMO Floating Rate High Yield ETF | 4.71 | 4.71 | |
BMO High Yield US Corporate Bond ETF | 0.00 | ||
BMO High Yld US Corp Bd Hdgd to CAD ETF | 5.66 | 5.55 | 5.66 |
BMO Laddered Preferred Share ETF | 4.11 | 4.11 | |
BMO Long Corporate Bond ETF | 3.93 | 4.21 | 4.21 |
BMO Long Federal Bond ETF | 2.35 | 3.05 | 3.05 |
BMO Long Provincial Bond ETF | 3.15 | 3.51 | 3.51 |
BMO Mid Corporate Bond ETF | 3.01 | 3.10 | 3.10 |
BMO Mid Federal Bond ETF | 2.04 | 2.12 | 2.12 |
BMO Mid Provincial Bond ETF | 2.43 | 2.99 | 2.99 |
AVERAGEIF NOT 0 | 3.43 |
<colgroup><col><col><col><col></colgroup><tbody>
</tbody>