AverageIFS formula where values that have the same identifier need to be counted as 1

**rwille3500** · 09-29-2022, 06:57 PM

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:
ID Sales Value
A1 $1000
A2 $2000
A1 $100
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

pls help

**sandy666** · 09-29-2022, 07:16 PM

not formula but Power Query

ID	Sales	Average
A1	1000	2366.666667
A2	2000
A1	100
A3	4000

Please Login or Register  to view this content.

next time post example excel file , see top yellow banner

**HansDouwe** · 09-29-2022, 10:07 PM

Please try this formula:

Formula:

Please Login or Register  to view this content.

**ELeGault** · 09-29-2022, 10:55 PM

Please Login or Register  to view this content.

if the data was in such a structure as Sandy Shared...

Or if its a table

Please Login or Register  to view this content.

**AliGW** · 09-30-2022, 07:58 AM

Administrative Note:

Welcome to the forum.

We would very much like to help you with your query, however it has been brought to our attention that the same query has been posted on one or more other forums and you have not told us about this. You are required to do so.

Please see Forum Rule #3 about cross-posting and adjust accordingly. Read this to understand why we (and other sites like us) consider this to be important: https://excelguru.ca/a-message-to-forum-cross-posters/

(Note: this requirement is not optional. As you are new here, I shall do it for you this time: https://www.mrexcel.com/board/thread...-as-1.1217966/)

AverageIFS formula where values that have the same identifier need to be counted as 1

LinkBack

Thread Tools

Rate This Thread

Display

AverageIFS formula where values that have the same identifier need to be counted as 1

Re: AverageIFS formula where values that have the same identifier need to be counted as 1

Re: AverageIFS formula where values that have the same identifier need to be counted as 1

Re: AverageIFS formula where values that have the same identifier need to be counted as 1

Re: AverageIFS formula where values that have the same identifier need to be counted as 1

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] Sum Error, Cell Values Not Counted

[SOLVED] Formula to show volume of 2nd identifier if volume of 1st identifier is missing

Formula that Return values between common identifier

Formula to count range of only different dates , repeated values wont be counted?

[SOLVED] Formula to Capture Multiple Values for one Identifier

Return counted values from a different sheet

range values counted problem

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions