Formula Help - XLookup + average

geraldvassallo 

Hello,

In our to assist, required clarification, regarding missing value in DATA 2.

If DATA 2 value is not available, would you like it to consider for taking average i.e. for Denominator in Average function would you like to consider DATA 2 count also...

If YES, in that case Denominator in Average function will be 3

If No, than Denominator in Average function will be 2

The formula will differ in above scenario

If you are considering OPTION YES - Denominator as 3, then you may use following formula

=MIN(100%,(AVERAGE(IFERROR((Target!$C$3/(XLOOKUP($A4,'Data 3'!$K:$K,'Data 3'!C:C))),0),IFERROR((Target!$C$3/(XLOOKUP($A4,'Data 2'!$K:$K,'Data 2'!C:C))),0),IFERROR((Target!$C$3/(XLOOKUP($A4,'Data 1'!$K:$K,'Data 1'!C:C))),0))))

If you are considering OPTION NO- Denominator as 2, then you needs to use COUNT function, as below

MIN(100%,(SUM(IFERROR((Target!$C$3/(XLOOKUP($A4,'Data 3'!$K:$K,'Data 3'!B:B))),0),IFERROR((Target!$C$3/(XLOOKUP($A4,'Data 2'!$K:$K,'Data 2'!B:B))),0),IFERROR((Target!$C$3/(XLOOKUP($A4,'Data 1'!$K:$K,'Data 1'!B:B))),0)))/COUNT((XLOOKUP($A4,'Data 3'!$K:$K,'Data 3'!B:B)),(XLOOKUP($A4,'Data 2'!$K:$K,'Data 2'!B:B)),(XLOOKUP($A4,'Data 1'!$K:$K,'Data 1'!B:B))))

Updated sheet is attached.

Trust this will help you.