CODEWITHSUNDEEP
excel
rwille
rwille

Reputation: 1

How to write an AverageIFS formula in excel when values that have the same identifier need to be summed together and counted as one

I have a dataset of sales data and I need to find the average order value of all customers. If I take the average of all orders the average is lower than expected. This is because a customer will make a large purchase one day and then maybe a smaller purchase a week later and the average formula thinks its two different customers.

So I'm trying to sum the sales values if the client ID is the same and then count is as one. And if there isnt a repeat customer, still use that value in the average.

Say you have a dataset like this:

A1 $1000 A1 $100 A2 $2000 A3 $4000

I want the formula to sum the two values of A1 but only count it as one observation. so the average should be $7100/3, not $7100/4

The Client ID and sales data are in seperate columns.

Upvotes: 0

Views: 140

Answers (1)

Ike
Ike

Reputation: 13064

I am not sure if I understand your sample data correct:

enter image description here

If so and if you have Excel 365 then you can use this formula:

=SUM(B2:B5)/COUNTA(UNIQUE(A2:A5))

Upvotes: 1

Related Questions