Find the average redemption rate

Hi All,

Please see the attached sheet(s) I'm trying to figure out for each 'ID' what their average redemption rate is (eg. from their first redemption to their last redemption they redeemed and average of once per week or 0.2 times per week) and then a total average redemption

the difficulty comes from the first redemption and last redemption being different across each different ID....

Does anyone know how I could work this out?

Thank you :D

Does anyone have any ideas on this? Maybe doing it just on the ones that are 2+ redemptions would help otherwise all the single redemptions will throw out the averages?

Thanks for any help anyone can give!

Hi JeteMc,

Thanks for getting back to me! The problem with this (and I expect it's been caused by the way I've laid out the pivot table) is that not all the people were there for the whole 3 weeks. Some of them only started to redeem on, say the last week.

So customer ***1385 came in for the 1st time on the 14th and the last time on the 27th so their average of 5 visits was between 14 days - meaning they visit every 2.8 days. Now I can work this out individually for each line by doing =datedif to count up the days, however because the dates are only in the top column and because the dates start and end in different columns I've had to manually type in which columns of row 2 to count between (I did this after doing a copy & paste of just the pivot table values) ....it's gets the results but isn't a good way to get there, this spreadsheet is only a fraction of the total data i need to do this for!

Hope that makes more sense - I've included a new sheet (Very manual workings!) with examples of what I've done on row's 3-5, columns n, m,o.

Any improvements would be much appreciated!

Thanks
Lx

In P3 of your manual results:

=COUNTIF('RAW DATA'!D:D,'Very manual workings!'!A3) Number of transaction for a voucher

in Q3 First Transaction date

=MIN(IF('RAW DATA'!$D$2:$D$100=$A3,'RAW DATA'!$E$2:$E$100))

in R3 Last transaction date

=Max(IF('RAW DATA'!$D$2:$D$100=$A3,'RAW DATA'!$E$2:$E$100))

in S3 Average redemption

=((R3-Q3)+1)/P3

You can combine Q and R and replace in S3 formula

Hope this helps.

Hey John,

Great - It took me a while to work out the changes I needed to move it into the bigger document... but I got there eventually!! I still don't really understand why but eventually figured (after lots of googling!) I had to Ctrl/Shift and Enter to make the Min and max formula's work!

Thank you very much for your help!

Lx

My apologies:I (obviously!) missed telling you enter MIN/MAX with Ctrl+Shift+Enter.

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.