Forum Discussion
Formula Help - XLookup + average
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.
If you want to return 0 from the average of the three XLOOKUPs in the case where the email address is not found on one sheet, then you can use this (KPI 3 as example):
=MIN(100%,IFERROR(AVERAGE(Target!$C$3/XLOOKUP(A4,'Data 3'!K:K,'Data 3'!C:C),Target!$C$3/XLOOKUP(A4,'Data 2'!K:K,'Data 2'!C:C),Target!$C$3/XLOOKUP(A4,'Data 1'!K:K,'Data 1'!C:C)),0))
Put simply, wrap the AVERAGE function in IFERROR and pass a zero as the second parameter to IFERROR.
I removed a lot of the parentheses as they were confusing me and they weren't strictly necessary.
You can just as easily wrap the MIN with IFERROR in the same way and it will be the same result.
I did notice that you seem to be using $C$3 as the numerator in each of the score calculations - I wonder if you meant this to be different for each XLOOKUP? i.e. for XLOOKUP using A:A, use Target!$C$3. For B:B, use Target!$C$4, and so on.
Additionally, I think you meant to use A:A for the return array from the data sheets for KPI 1 on the second user. When I opened your file, it was showing B:B, but the row above showed A:A.
Let me know if it was your intention to return a zero in this way. Note that since the mean KPI score is zero, the component of the efficiency expression that references that zero will also be zero, and so will be ignored as you need.
