How to calculate the average spend per user

D

debeenus

New member
Joined
Feb 5, 2014
Messages
9
Reaction score
0
Points
0
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 AmountCurrencyStatus
2/18/2014 17:3414603992725502204.5GBPSuccess
2/18/2014 15:1214603903157696304.5GBPFailed

Thank you for your time!
 
how are you trying to get the data reported?
on a new sheet in the sameworkbook?
 
Hi Simi,

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

Thank you,
 
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.
 
where is the user id in the data?
 
Simi said:
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.
Click to expand...

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: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! :)
 
Hi Bob,

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

Thank you,
 
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.
 
Maybe you need separators of semi-colon in the formula, not comma (,).
 
Thanks everyone, I found a solution to both my issues. You all have been very helpful! Thank you!
 
Can you say what those solutions are, for the archives?
 
Sorry for the late reply Bob, I was on a vacation. I managed to use two Dutch Excel formulas, the last one worked well for me:

=ALS.FOUT(SOMMEN.ALS(F:F,B:B,I4,H:H,"Delivered"),"NIETS")

Not sure if this would help the archives a lot due to the Dutch nature. Happy to help though. :)

Have a great day everyone!
 
So it was semi-colons as I suggested?
 
You must log in or register to reply here.
Back
Top