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.
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.
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.
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.
Thank you for this! It solved the problem for me!!
