Forum Discussion

geraldvassallo's avatar
geraldvassallo
Copper Contributor
Jul 14, 2020
Solved

Formula Help - XLookup + average

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 u...
excel
office 365
geraldvassallo's avatar
geraldvassallo
Copper Contributor
Jul 21, 2020

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. 

 

sunil_daware's avatar
sunil_daware
Copper Contributor
Aug 10, 2020

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.

 

Resources