Formula Help - XLookup + average

[geraldvassallo]

Hello, 

I am trying to work out a formula where, XLOOKUP finds the data in 3 different worksheets, and gives me the average, however, whenever it is not finding a value in 1 sheet, it is coming up with N/A and disregarding or not calculating the rest. (Attached example) 

Once this has been done, I am trying to calculate the efficiency, however, I am trying to make the formula work in a way where if B C or D value is 0, it disregards that cell. 

Anyone got some ideas of how to make this work please? 

[#2]

@geraldvassallo 

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.

[#3 · Accepted solution]

@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.

[#4]

Thank you for this! It solved the problem for me!! 

[#5]

Thank you!@OwenPrice - This was also a good solution for it!

I appreciate your time! 

[#6]

@geraldvassallo 

In general the same

=MIN(1,Target!$C$3*AVERAGE(
       IFNA(1/XLOOKUP($A3,'Data 1'!$K:$K,'Data 1'!A:A,NA()),FALSE),
       IFNA(1/XLOOKUP($A3,'Data 2'!$K:$K,'Data 2'!A:A,NA()),FALSE),
       IFNA(1/XLOOKUP($A3,'Data 3'!$K:$K,'Data 3'!A:A,NA()),FALSE)
))

[#7]

@sunil_daware once again thank you for your assistance with this. 

Unfortunately, I'm finding a problem, the average is not being worked out properly.. 

When it is taken on their own, IE Data 1 on its own, Data 2 on its own, and Data 3 on its own, the calculation is done correctly, however, once I put everything in the same formula, it for some reason goes wrong. 

As per the attached sheet, the 3 lookups are giving an average of 66%, however, when calculating it manually, This should be around 52%. 

Any idea of what may be causing this, please?

Thanks in advance. 

[#8]

@geraldvassallo 

Hello there, sorry for delayed response.

Hope you have figured-out the solution.

If not, Could you please elaborate challenge.

As per my understanding you are referring to below circled 66%

You may like to refer the Calculations elaborated for arriving 66%, i think it is self explanatory.

Trust this will be of help.

Please feel free to get in touch.