Attempting to use Average / Subtotal in a MMULT Array Formula

I'm having trouble finding the Average of a filtered Array. Sum works correctly (only summing the matched values), but average doesn't produce the average of the desired matched range. I've read that subtotal(1, can help but I get an error when trying to prefix the array with that function instead of the SUM or Average. Here is the ArrayFormula working as a SUM.

{=SUM(MMULT(ISNUMBER(MATCH(TableAircraftCostData[[#Headers],[50th Percentile]:[80th Percentile]],SelectedAssetUtilization,0))+0,TRANSPOSE((TableAircraftCostData[Asset]=[@[Aircraft Type]])*(ISNUMBER(MATCH(TableAircraftCostData[FY],SelectedFY,0)))*(ISNUMBER(MATCH(TableAircraftCostData[Site],TableFHAnalysisEngine[OpLocationCode],0)))*TableAircraftCostData[[50th Percentile]:[80th Percentile]])))}
LVL 1
-PolakAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

-PolakAuthor Commented:
If I write it as an IF Statement ala:
=IF(SelectedAssetUtilization="50th Percentile",AVERAGE(IF((TableAircraftCostData[Asset]=[@[Aircraft Type]])*ISNUMBER(MATCH(TableAircraftCostData[Site],TableFHAnalysisEngine[OpLocationCode],0))*ISNUMBER(MATCH(TableAircraftCostData[FY],SelectedFY,0)),TableAircraftCostData[50th Percentile])), IF(SelectedAssetUtilization="80th Percentile",AVERAGE(IF((TableAircraftCostData[Asset]=[@[Aircraft Type]])*ISNUMBER(MATCH(TableAircraftCostData[Site],TableFHAnalysisEngine[OpLocationCode],0))*ISNUMBER(MATCH(TableAircraftCostData[FY],SelectedFY,0)),TableAircraftCostData[80th Percentile]))))

It works just fine. I like the MMULT array though if someone can make it do the average.
Rgonzo1971Commented:
HI,

have you  tried

Sum(...)/CountIf(..., ">0")

or

Averageif(..., ">0")

Regards
-PolakAuthor Commented:
Couldn't seem to get those to work with the MMULT/Transpose.... for now I've settled on this gigantic nested IF Statement Array that is working but causing my workbook to calculate awefully slow.
{=IFERROR(IF(NeworUsed="Used", IF(SelectedCONCATPercentileMethod="Global 50th Percentile", AVERAGE(IF((TableGlobalFH[Asset]=[@[Aircraft Type]])*ISNUMBER(MATCH(TableGlobalFH[FY],SelectedFY,0)),TableGlobalFH[Global 50th Percentile])), IF(SelectedCONCATPercentileMethod="Global 80th Percentile", AVERAGE(IF((TableGlobalFH[Asset]=[@[Aircraft Type]])*ISNUMBER(MATCH(TableGlobalFH[FY],SelectedFY,0)),TableGlobalFH[Global 80th Percentile])), IF(SelectedCONCATPercentileMethod="Region 50th Percentile", AVERAGE(IF((TableRegionFH[Asset]=[@[Aircraft Type]])*ISNUMBER(MATCH(TableRegionFH[FY],SelectedFY,0))*ISNUMBER(MATCH(TableRegionFH[Region],TableFHAnalysisEngine[RegionCode],0)),TableRegionFH[Region 50th Percentile])),IF(SelectedCONCATPercentileMethod="Region 80th Percentile", AVERAGE(IF((TableRegionFH[Asset]=[@[Aircraft Type]])*ISNUMBER(MATCH(TableRegionFH[FY],SelectedFY,0))*ISNUMBER(MATCH(TableRegionFH[Region],TableFHAnalysisEngine[RegionCode],0)),TableRegionFH[Region 80th Percentile])),IF(SelectedCONCATPercentileMethod="Branch 50th Percentile", AVERAGE(IF((TableBranchFH[Asset]=[@[Aircraft Type]])*ISNUMBER(MATCH(TableBranchFH[FY],SelectedFY,0))*ISNUMBER(MATCH(TableBranchFH[Branch],TableFHAnalysisEngine[OpBranch],0)),TableBranchFH[Branch 50th Percentile])),IF(SelectedCONCATPercentileMethod="Branch 80th Percentile", AVERAGE(IF((TableBranchFH[Asset]=[@[Aircraft Type]])*ISNUMBER(MATCH(TableBranchFH[FY],SelectedFY,0))*ISNUMBER(MATCH(TableBranchFH[Branch],TableFHAnalysisEngine[OpBranch],0)),TableBranchFH[Branch 80th Percentile])),IF(SelectedCONCATPercentileMethod="Local 50th Percentile",AVERAGE(IF((TableAircraftCostData[Asset]=[@[Aircraft Type]])*ISNUMBER(MATCH(TableAircraftCostData[Site],TableFHAnalysisEngine[OpLocationCode],0))*ISNUMBER(MATCH(TableAircraftCostData[FY],SelectedFY,0)),TableAircraftCostData[Local 50th Percentile])), IF(SelectedCONCATPercentileMethod="Local 80th Percentile",AVERAGE(IF((TableAircraftCostData[Asset]=[@[Aircraft Type]])*ISNUMBER(MATCH(TableAircraftCostData[Site],TableFHAnalysisEngine[OpLocationCode],0))*ISNUMBER(MATCH(TableAircraftCostData[FY],SelectedFY,0)),TableAircraftCostData[Local 80th Percentile])), IF(SelectedCONCATPercentileMethod="",""))))))))),""),"")}

Open in new window

And yes, recreating all of the tables in that formula in a dummy workbook would be quite painful. But I'll do it if necessary.
Become a Certified Penetration Testing Engineer

This CPTE Certified Penetration Testing Engineer course covers everything you need to know about becoming a Certified Penetration Testing Engineer. Career Path: Professional roles include Ethical Hackers, Security Consultants, System Administrators, and Chief Security Officers.

Rgonzo1971Commented:
doesn't that work

{=AVERAGEIF(MMULT(ISNUMBER(MATCH(TableAircraftCostData[[#Headers],[50th Percentile]:[80th Percentile]],SelectedAssetUtilization,0))+0,TRANSPOSE((TableAircraftCostData[Asset]=[@[Aircraft Type]])*(ISNUMBER(MATCH(TableAircraftCostData[FY],SelectedFY,0)))*(ISNUMBER(MATCH(TableAircraftCostData[Site],TableFHAnalysisEngine[OpLocationCode],0)))*TableAircraftCostData[[50th Percentile]:[80th Percentile]])),">0")}
-PolakAuthor Commented:
No Sir.... Capture.JPGI've change around some of my table / column names to facilitate that long IF statement here's an updated version of what you were trying to do:

=AVERAGEIF(MMULT(ISNUMBER(MATCH(TableAircraftCostData[[#Headers],[Local 50th Percentile]:[Local 80th Percentile]],SelectedCONCATPercentileMethod,0))+0,TRANSPOSE((TableAircraftCostData[Asset]=[@[Aircraft Type]])*(ISNUMBER(MATCH(TableAircraftCostData[FY],SelectedFY,0)))*(ISNUMBER(MATCH(TableAircraftCostData[Site],TableFHAnalysisEngine[OpLocationCode],0)))*TableAircraftCostData[[Local 50th Percentile]:[Local 80th Percentile]])),">0")
Rgonzo1971Commented:
Sorry without seeing the data I can't help further

EDIT What I need first to know is what is the result before the sum

Goto Formulas / Formula Auditing / Evaluate Formula
and Evaluate just before resolving the sum
-PolakAuthor Commented:
I'll work on a dummy workbook tomorrow, thank you!
Rgonzo1971Commented:
HI,

You could try

=AVERAGE(IF(MMULT(...);MMULT(...);""))
-PolakAuthor Commented:
I just saw your edit. Right before the SUM: captureforumla3.JPG
I've always hated formula evaluater  because you can't copy and paste the result. A handy trick i learned was to highlight the section of the formula i wanted to evaluate in the formula bar and press F9. That will give you the result of that part of the formula. Plus you have the added benefit of being able to copy and paste it. Anyway, here is the formula separate into its independent parts;

=SUM(MMULT({TRUE,FALSE}+0,TRANSPOSE({TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE}*{FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE}*{FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;TRUE}*{26.8,32.4;20.2,34.0;17.3,54.9;18.6,31.1;19.4,31.3;32.5,40.5;11.1,21.6;24.2,28.1;20.8,26.1;12.0,31.2;9.8,15.5;7.1,30.1;21.2,42.7;23.5,28.9;6.1,9.5;26.8,50.0;17.0,18.3;22.7,36.8;11.6,15.7;20.4,22.4;16.7,19.9;34.3,50.7;19.3,29.3;20.0,44.2;16.6,24.6;14.1,16.2;5.2,8.2;32.5,40.7;12.5,17.6;1.0,1.7;42.6,45.0;15.3,27.6;21.4,35.0;14.7,18.3;0.0,0.0;5.6,20.4;0.0,0.0;5.2,10.3;1.6,7.4;6.4,6.4;9.0,19.6;15.0,15.0;24.7,40.6;0.0,0.0;30.8,40.2;0.0,0.0;2.1,9.3;2.1,6.8;3.5,11.1;3.1,3.5;0.0,0.2;6.7,9.5;0.0,1.4;5.2,10.9;7.4,13.2;2.9,7.2;3.9,5.3;8.1,16.3;0.0,15.0;13.1,36.3;1.9,3.0;2.9,6.1;2.7,4.4;13.2,38.1;1.2,1.2;12.5,19.8;12.1,22.2;11.7,14.9;26.6,34.1;11.2,31.9;6.9,16.3;55.0,66.3;4.7,4.7;4.2,6.6;26.4,42.9;5.6,9.6;11.3,23.1;3.5,13.0;22.2,27.2;10.2,13.4;3.0,3.0;5.1,12.9;4.9,10.3;5.2,9.6;7.2,26.7;6.2,13.9;2.8,5.8;4.4,7.7;3.8,9.2;9.0,15.8;9.5,13.6;1.7,3.3;4.8,8.0;3.8,8.0;0.0,0.3;0.0,2.5;0.0,0.1;8.8,16.4;52.2,62.9;31.8,54.0;48.2,60.7;9.6,11.6;44.4,53.6;34.0,35.7;7.8,11.6;1.6,2.7;29.1,46.3;41.2,45.4;65.2,83.6;24.1,44.0;24.8,35.7;63.6,110.1;2.0,3.2;16.2,43.1;10.6,35.0;10.5,12.9;0.0,1.0;28.4,88.8;43.1,51.2;52.4,57.6;26.5,45.7;19.5,28.6;3.4,7.9;3.8,6.1;0.0,3.1;3.3,5.2;7.8,16.6;6.4,7.9;8.1,17.8;3.9,6.6;0.0,0.0;9.7,19.9;0.0,15.4;2.0,10.0;0.0,0.0;16.3,22.7;10.1,13.1;6.9,9.6;10.5,15.8;12.4,14.1;19.1,29.5;30.7,47.0;32.3,38.7;49.3,69.9;17.0,29.5;0.5,0.5;23.4,39.5;36.1,43.2;23.5,37.3;23.5,29.0;32.8,37.9;25.1,70.1;8.9,11.6;23.0,47.3;36.7,45.9;27.9,38.9;13.6,30.5;46.5,61.9;15.2,19.3;28.3,32.4;17.5,26.0;25.1,30.1;20.2,23.1;34.5,60.2;18.3,22.4;49.8,67.9;22.2,39.1;15.7,30.5;6.9,7.3;46.3,68.4;27.6,54.6;1.5,1.9;49.3,67.3;35.0,41.9;34.3,47.1;17.0,19.9;0.0,0.0;13.2,18.9;14.1,24.0;44.7,44.7;29.1,38.8;21.2,25.5;51.9,68.9;0.0,6.9;6.6,12.8;0.0,0.0;5.2,12.9;5.5,18.2;0.0,0.0;6.9,11.7;12.9,16.4;12.9,35.4;0.9,1.6;1.1,3.5;0.0,0.0;17.7,21.4;6.6,8.6;3.1,4.5;5.9,19.1;1.2,7.9;1.6,1.7;4.4,5.2;8.6,26.0;30.3,54.0;18.4,34.6;0.0,0.0;36.2,40.3;0.5,0.9;7.3,10.8;54.2,74.6;11.9,17.0;24.9,37.1;16.7,20.7;17.6,28.9;0.2,2.2;0.5,15.9;16.8,35.8;5.2,8.6;10.4,32.3;3.6,14.3;0.0,0.0;0.0,0.0;0.0,0.0;0.0,0.0;0.0,14.0;0.0,0.0;10.7,13.7;2.3,2.3;0.0,5.0;0.0,0.2;8.6,12.6;53.6,74.2;20.2,37.6;50.2,56.9;0.0,2.8;34.7,39.4;0.0,3.0;6.9,10.9;1.7,4.8;29.7,34.5;31.2,35.2;55.0,78.8;37.3,51.5;38.0,57.6;46.1,60.0;4.3,4.3;27.8,49.2;30.3,30.3;13.8,22.4;22.1,28.3;5.6,13.6;40.1,57.4;26.9,38.2;54.4,80.0;43.4,70.7;8.0,20.3;7.6,13.5;0.0,0.6;7.9,10.0;8.6,8.6;10.5,12.4;0.0,9.4;0.0,0.0;0.0,0.0;0.0,0.0;0.0,0.0;0.0,0.0;12.7,19.8;7.8,12.0;0.0,0.0;4.7,10.2;17.4,20.1;12.5,16.6;15.1,19.5;2.3,6.4;8.2,12.9;35.6,47.5;0.0,0.0;23.4,30.8;25.1,34.5;26.5,35.0;30.3,63.4;17.2,28.1;0.5,0.5;20.9,31.1;35.5,39.8;17.1,30.3;23.9,27.8;26.3,29.9;20.3,45.4;9.3,11.7;16.1,36.3;31.2,41.3;24.3,30.5;12.0,14.6;44.2,53.1;15.9,18.2;27.7,33.9;16.2,22.6;19.8,27.0;17.4,20.7;33.2,49.8;15.3,24.3;29.5,58.7;18.2,31.7;15.8,21.4;5.2,7.9;38.6,52.8;16.4,31.2;1.1,1.8;46.8,51.9;24.1,27.5;29.6,34.9;15.1,16.7;0.0,0.0;9.1,18.9;0.0,0.0;5.2,10.3;1.6,7.4;6.4,6.4;12.2,18.5;15.0,15.0;44.7,44.7;29.2,37.7;10.6,22.8;39.9,49.1;0.0,5.2;5.7,10.6;1.4,6.6;6.2,11.0;3.7,9.1;0.0,0.2;6.1,7.5;0.0,1.4;6.9,8.9;10.6,13.0;10.8,17.7;0.9,1.6;2.7,4.9;6.9,10.3;17.7,21.4;9.2,22.5;1.9,3.0;2.8,5.4;2.7,4.4;14.0,25.3;1.2,1.2;12.5,19.8;1.2,7.9;1.6,1.7;11.6,21.6;11.1,18.7;28.2,43.2;18.7,31.9;5.2,10.3;48.8,51.1;4.7,4.7;0.5,0.9;6.0,7.8;38.2,57.0;10.1,12.6;14.6,31.0;10.1,16.5;19.6,25.5;8.9,12.3;0.5,10.9;11.0,19.1;5.3,7.0;5.2,9.6;6.0,28.0;4.9,13.7;2.8,4.8;2.8,7.7;2.7,5.3;6.8,10.8;9.3,11.0;0.2,1.7;7.2,9.2;3.5,8.0;0.0,0.3;0.0,5.0;0.0,2.5;0.0,0.1;8.7,14.3;51.0,65.3;26.0,41.3;51.8,56.4;9.6,11.6;0.0,2.8;38.6,42.9;27.0,34.5;7.7,8.7;1.6,3.7;27.8,37.9;36.9,40.5;56.4,76.9;32.9,44.0;31.7,38.6;55.0,72.3;2.0,3.2;4.3,4.3;23.3,35.9;30.3,30.3;15.5,30.9;15.9,20.9;0.0,2.8;36.6,70.5;34.7,38.1;53.3,65.3;38.2,49.3;17.3,26.9;4.6,10.1;0.0,0.6;6.3,8.3;0.0,3.1;6.5,7.8;9.6,12.1;5.2,9.3;8.1,17.8;3.9,6.6;0.0,0.0;0.0,0.0;0.0,0.0;0.0,0.0;0.0,0.0;0.0,0.0;10.4,18.5;9.2,14.2;2.0,10.0;0.0,0.0;16.3,22.7;4.7,10.2;12.9,14.1;9.4,12.5;12.1,17.8;2.3,6.4;10.0,14.0;35.6,47.5;0.0,0.0;21.9,24.6})))
Rgonzo1971Commented:
Have you tried

 =AVERAGE(IF(MMULT(...);MMULT(...);""))
-PolakAuthor Commented:
No I haven't; my experience with MMULT is amateur at best... can you place my formula string into the structure you're talking about, not sure what to do with the transpose....

Regarding the long formula evaluate I posted, your limited to 8100 or so characters in the formula bar so I had to do the last part manually by exporting the range to a csv file then pasting them into a word document and and doing a find & replace on "^p" to ";". Apologizes if the array size is off for some reason.

But I think the results of this should tell you is that all the False's that end up being 0's are getting factored into the Average and it ends up being an Average of all of the cells in the range rather than just the ones that fit the matching criteria. Makes sense, but how do we get around it....
Rgonzo1971Commented:
then try

{=AVERAGE(IF(MMULT(ISNUMBER(MATCH(TableAircraftCostData[[#Headers],[50th Percentile]:[80th Percentile]],SelectedAssetUtilization,0))+0,TRANSPOSE((TableAircraftCostData[Asset]=[@[Aircraft Type]])*(ISNUMBER(MATCH(TableAircraftCostData[FY],SelectedFY,0)))*(ISNUMBER(MATCH(TableAircraftCostData[Site],TableFHAnalysisEngine[OpLocationCode],0)))*TableAircraftCostData[[50th Percentile]:[80th Percentile]])),(MMULT(ISNUMBER(MATCH(TableAircraftCostData[[#Headers],[50th Percentile]:[80th Percentile]],SelectedAssetUtilization,0))+0,TRANSPOSE((TableAircraftCostData[Asset]=[@[Aircraft Type]])*(ISNUMBER(MATCH(TableAircraftCostData[FY],SelectedFY,0)))*(ISNUMBER(MATCH(TableAircraftCostData[Site],TableFHAnalysisEngine[OpLocationCode],0)))*TableAircraftCostData[[50th Percentile]:[80th Percentile]]));"")}
-PolakAuthor Commented:
:( Capture.JPGLet me know if I should make that dummy workbook for you later.
-PolakAuthor Commented:
PROGRESS!
This evaluates the average correctly.... but I have no idea why.
{=SUM(MMULT(ISNUMBER(MATCH(TableAircraftCostData[[#Headers],[Local 50th Percentile]:[Local 80th Percentile]],SelectedCONCATPercentileMethod,0))+0,TRANSPOSE((TableAircraftCostData[Asset]=[@[Aircraft Type]])*(ISNUMBER(MATCH(TableAircraftCostData[FY],SelectedFY,0)))*(ISNUMBER(MATCH(TableAircraftCostData[Site],TableFHAnalysisEngine[OpLocationCode],0)))*TableAircraftCostData[[Local 50th Percentile]:[Local 80th Percentile]]))) / (COUNT(IF(MMULT(ISNUMBER(MATCH(TableAircraftCostData[[#Headers],[Local 50th Percentile]:[Local 80th Percentile]],SelectedCONCATPercentileMethod,0))+0,TRANSPOSE((TableAircraftCostData[Asset]=[@[Aircraft Type]])*(ISNUMBER(MATCH(TableAircraftCostData[FY],SelectedFY,0)))*(ISNUMBER(MATCH(TableAircraftCostData[Site],TableFHAnalysisEngine[OpLocationCode],0)))*TableAircraftCostData[[Local 50th Percentile]:[Local 80th Percentile]]))>0,1)))}
Rgonzo1971Commented:
What is average

Average is the sum of numbers divided by the count of your numbers

let's try this formula the one before isn't right what I had in mind

=AVERAGE(IF(MMULT(ISNUMBER(MATCH(TableAircraftCostData[[#Headers],[50th Percentile]:[80th Percentile]],SelectedAssetUtilization,0))+0,TRANSPOSE((TableAircraftCostData[Asset]=[@[Aircraft Type]])*(ISNUMBER(MATCH(TableAircraftCostData[FY],SelectedFY,0)))*(ISNUMBER(MATCH(TableAircraftCostData[Site],TableFHAnalysisEngine[OpLocationCode],0)))*TableAircraftCostData[[50th Percentile]:[80th Percentile]])),MMULT(ISNUMBER(MATCH(TableAircraftCostData[[#Headers],[50th Percentile]:[80th Percentile]],SelectedAssetUtilization,0))+0,TRANSPOSE((TableAircraftCostData[Asset]=[@[Aircraft Type]])*(ISNUMBER(MATCH(TableAircraftCostData[FY],SelectedFY,0)))*(ISNUMBER(MATCH(TableAircraftCostData[Site],TableFHAnalysisEngine[OpLocationCode],0)))*TableAircraftCostData[[50th Percentile]:[80th Percentile]])),""))

Open in new window

-PolakAuthor Commented:
Yes... but this is the first time I've been able to get it to Count just the targeted numbers and not the whole range of 440 rows.  Your formula also evaluates to the same end result as mine. However, we have an Issue: AveragingProblem.JPG
The left most column evaluates using that long if statement that I posted earlier, here it is again, because I'm sure I changed something:
=IFERROR(IF(NeworUsed="Used", IF(SelectedCONCATPercentileMethod="Global 50th Percentile", AVERAGE(IF((TableGlobalFH[Asset]=[@[Aircraft Type]])*ISNUMBER(MATCH(TableGlobalFH[FY],SelectedFY,0)),TableGlobalFH[Global 50th Percentile])), IF(SelectedCONCATPercentileMethod="Global 80th Percentile", AVERAGE(IF((TableGlobalFH[Asset]=[@[Aircraft Type]])*ISNUMBER(MATCH(TableGlobalFH[FY],SelectedFY,0)),TableGlobalFH[Global 80th Percentile])), IF(SelectedCONCATPercentileMethod="Region 50th Percentile", AVERAGE(IF((TableRegionFH[Asset]=[@[Aircraft Type]])*ISNUMBER(MATCH(TableRegionFH[FY],SelectedFY,0))*ISNUMBER(MATCH(TableRegionFH[Region],TableFHAnalysisEngine[RegionCode],0)),TableRegionFH[Region 50th Percentile])),IF(SelectedCONCATPercentileMethod="Region 80th Percentile", AVERAGE(IF((TableRegionFH[Asset]=[@[Aircraft Type]])*ISNUMBER(MATCH(TableRegionFH[FY],SelectedFY,0))*ISNUMBER(MATCH(TableRegionFH[Region],TableFHAnalysisEngine[RegionCode],0)),TableRegionFH[Region 80th Percentile])),IF(SelectedCONCATPercentileMethod="Branch 50th Percentile", AVERAGE(IF((TableBranchFH[Asset]=[@[Aircraft Type]])*ISNUMBER(MATCH(TableBranchFH[FY],SelectedFY,0))*ISNUMBER(MATCH(TableBranchFH[Branch],TableFHAnalysisEngine[OpBranch],0)),TableBranchFH[Branch 50th Percentile])),IF(SelectedCONCATPercentileMethod="Branch 80th Percentile", AVERAGE(IF((TableBranchFH[Asset]=[@[Aircraft Type]])*ISNUMBER(MATCH(TableBranchFH[FY],SelectedFY,0))*ISNUMBER(MATCH(TableBranchFH[Branch],TableFHAnalysisEngine[OpBranch],0)),TableBranchFH[Branch 80th Percentile])),IF(SelectedCONCATPercentileMethod="Local 50th Percentile",AVERAGE(IF((TableAircraftCostData[Asset]=[@[Aircraft Type]])*ISNUMBER(MATCH(TableAircraftCostData[Site],TableFHAnalysisEngine[OpLocationCode],0))*ISNUMBER(MATCH(TableAircraftCostData[FY],SelectedFY,0)),TableAircraftCostData[Local 50th Percentile])), IF(SelectedCONCATPercentileMethod="Local 80th Percentile",AVERAGE(IF((TableAircraftCostData[Asset]=[@[Aircraft Type]])*ISNUMBER(MATCH(TableAircraftCostData[Site],TableFHAnalysisEngine[OpLocationCode],0))*ISNUMBER(MATCH(TableAircraftCostData[FY],SelectedFY,0)),TableAircraftCostData[Local 80th Percentile])), IF(SelectedCONCATPercentileMethod="",""))))))))),""),"")

Open in new window

(to keep it simple we are currently writing a formula for the If "Local 50th Percentile" or "Local 80th Percentile" logic statements, the other IFs point to different data tables with Percentile information)

The next two columns evaluate using my sum/count(if formula and your average(if. You'll notice that the numbers are off in the cases of some rows. That's because in those rows the actual value of TableAircraftCostData[[50th Percentile]:[80th Percentile]] is = 0 and our formulas are excluding those rows. So basically we need to set the IF = 0 to only apply to the targeting criteria and not to the values to average.... How do we do that.
Rgonzo1971Commented:
Sorry the dummy is indispensable
-PolakAuthor Commented:
the dummy? do you mean you are busy, or that the zero value we are excluding can't be included?
Rgonzo1971Commented:
I mean Without the dummy workbook is pretty difficult to understand the problem
-PolakAuthor Commented:
Got it, I'm a  little slow today. Sure that'll take me an hour or two. In the meantime to help you understand and maybe save me that work of making the dummy....
 
Mo Utilization is a measure How many hours an aircraft was flown in a month. The rows in the image are different aircraft types. We are matching on:
1. The Percentile the user is selecting (header columns)
2. The Aircraft type (rows in the table image i posted)
3. The Fiscal Year of the TableAircraftCostData
4. The Site Locations of the Table AircraftCostData
__________________________________________________________________
5.then MMULT'ing by the percentile values under the appropriate header row

If the data in selected Header row has a 0 value then our formulas exclude it from the average (unlike my monster IF statement) so when an aircraft didn't fly in a given month that value of 0 is not being averaged.

My assumption is that we'll have to nest an Offset statement into the MMULT to average the values after matching on items 1-4 and not excluding 0. If you think that's a good idea i can provide you the column positions in the table of items 1-5.

If this still doesn't make sense, let me know, and i'll get to work on that dummy workbook once i get out of the shower. Thanks for the prompt responses.
Rgonzo1971Commented:
Going offline until tomorrow
-PolakAuthor Commented:
10-4 making the dummy now.
-PolakAuthor Commented:
That wasn't soooo bad, here you go this illustrates what I'm talking about.
DifferentAveragesbecauseof0.xlsx
Rgonzo1971Commented:
Just add a very little number to the result

1E-200

=AVERAGE(IF(MMULT(ISNUMBER(MATCH(TableAircraftCostData2[[#Headers],[Local 50th Percentile]:[Local 80th Percentile]],SelectedCONCATPercentileMethod,0))+0,TRANSPOSE((TableAircraftCostData2[Asset]=[@[Aircraft Type]])*(ISNUMBER(MATCH(TableAircraftCostData2[FY],SelectedFY,0)))*(ISNUMBER(MATCH(TableAircraftCostData2[Site],Table3[Site],0)))*((1*10^-200)+TableAircraftCostData2[[Local 50th Percentile]:[Local 80th Percentile]]))),MMULT(ISNUMBER(MATCH(TableAircraftCostData2[[#Headers],[Local 50th Percentile]:[Local 80th Percentile]],SelectedCONCATPercentileMethod,0))+0,TRANSPOSE((TableAircraftCostData2[Asset]=[@[Aircraft Type]])*(ISNUMBER(MATCH(TableAircraftCostData2[FY],SelectedFY,0)))*(ISNUMBER(MATCH(TableAircraftCostData2[Site],Table3[Site],0)))*((1*10^-200)+TableAircraftCostData2[[Local 50th Percentile]:[Local 80th Percentile]]))),""))-(1*10^-200)

Open in new window

and subtract it at the end
EDITED code

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
-PolakAuthor Commented:
Beautiful, nothing like waking up at noon to a problems that were keeping you up until 3am the night prior figured out. (not to mention learning a thing or two) Thanks!
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.