SOLVED

# XLookUp and Sum (Formula Support)

Copper Contributor

# XLookUp and Sum (Formula Support)

Hi there,

I'm trying to create an attrition based formula using a combination of both XLOOKUP and SUM.

In the attached example, I want to use the following calculation within a single cell:

Group A:

2021 (Start) + 2021 (End) / 2 = AVERAGE

Leavers (Total) / AVERAGE = ATTRITION %

Then the same calculation above, but specific to Group B and Group C. I thought that by using XLOOKUP, it would help separate out the groups.

Within the attached, feel like I'm on the right track as illustrated within the 'Current XLOOKUP Formula' section - but I can't work out the next step once I have the average. Not even sure if it's possible to even achieve?

Thank you in advance for any help!

4 Replies

# Re: XLookUp and Sum (Formula Support)

@E_D123 I believe the formulas in the desired outcome section are wrong, so I changed them and completed the XLOOKUP formulas. See attached.

# Re: XLookUp and Sum (Formula Support)

Ah thanks for flagging, that was my mistake within the example.

Do you know how to capture the attrition % within the 'Current XLOOKUP Formula' section, so it's all within a single cell?
best response confirmed by E_D123 (Copper Contributor)
Solution

# Re: XLookUp and Sum (Formula Support)

@E_D123 See attached!

# Re: XLookUp and Sum (Formula Support)

@Riny_van_Eekelen Perfect- thank you!!

1 best response

Accepted Solutions
best response confirmed by E_D123 (Copper Contributor)
Solution

# Re: XLookUp and Sum (Formula Support)

@E_D123 See attached!