# Thread: How to calculate the average spend per user

1. ## How to calculate the average spend per user

Good afternoon!

I am struggling with the following case.

Over the past few months I collected a list of online client transactions. Each client has a unique transaction ID that doesn't change (A rebill will show the same transaction ID). The list shows successful and unsuccessful transactions.

My goal is to calculate the average spend per user, given the fact I am only looking for clients with successful payments.

The fields look like this:

 Date Transaction ID Amount Currency Status 2/18/2014 17:34 1460399272550220 4.5 GBP Success
 2/18/2014 15:12 1460390315769630 4.5 GBP Failed

2. how are you trying to get the data reported?
on a new sheet in the sameworkbook?

3. Hi Simi,

whatever is easiest, it doesn't matter by me.

Thank you,

4. well this is something I have done before, it isn't perfect but can get you what you are asking.

1. You need to get a list of the unique transaction ID's. You can do this by simply copying the entire column that has the transaction ID's pasting them to a new column, I used column H in my example. while column H is highlighted you can click on the data tab, and use the remove duplicates tool. This leaves you with a list of all the unique ID's.

2. Now you need a formula to calculate the average if the transaction ID matches and status equals Success.
in column "I" I put.
=IFERROR(AVERAGEIFS(C:C,B:B,H2,E:E,"Success"),"")
in this formula column C has the amounts to be averaged.
column B is the unique ID's to compare against column H.
column E is the status and we want to only calculate if it equals "Success"
The IFERROR portion helps if you end up with no matches or a div by 0, it will simply return blank text.

5. where is the user id in the data?

6. Originally Posted by Simi
well this is something I have done before, it isn't perfect but can get you what you are asking.

1. You need to get a list of the unique transaction ID's. You can do this by simply copying the entire column that has the transaction ID's pasting them to a new column, I used column H in my example. while column H is highlighted you can click on the data tab, and use the remove duplicates tool. This leaves you with a list of all the unique ID's.

2. Now you need a formula to calculate the average if the transaction ID matches and status equals Success.
in column "I" I put.
=IFERROR(AVERAGEIFS(C:C,B:B,H2,E:E,"Success"),"")
in this formula column C has the amounts to be averaged.
column B is the unique ID's to compare against column H.
column E is the status and we want to only calculate if it equals "Success"
The IFERROR portion helps if you end up with no matches or a div by 0, it will simply return blank text.
Hi Simi,

thanks a lot for the help, I went ahead and changed the two English functions for the Dutch words and pasted the following code into the sheet

=ALSFOUT(GEMIDDELDEN.ALS(C:C,B:B,E2,D,"Delivered"),"")

2/19/2014 0:36 1460425796916090 4.5 Cancelled 1460425796916090 #NAAM?
2/19/2014 0:35 1455414686175060 4.5 Failed 1455414686175060 #NAAM?

For a reason I am unable to figure out I am getting the #NAAM? error which shows me something is not quite right, I looked at it for 20 minutes and am unable to see the issue.

Perhaps someone else is able to see it?

Thanks a million everyone and Siri of course! :-)

7. Hi Bob,

the only user ID is the transaction ID which is unique to each client.

Thank you,

8. try using the evaluate formula option to see where in the formula you are getting an error.
In the example you posted, try changing "Delivered" in the formula to "Failed" so it matches something and see if it works.

9. Maybe you need separators of semi-colon in the formula, not comma (,).

10. Thanks everyone, I found a solution to both my issues. You all have been very helpful! Thank you!

Page 1 of 2 1 2 Last

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•