Forum Discussion
Solved
Time Between Purchases Issues
Hello, I have a dilemma that is over my head. I will try to explain it below and I thank you all in advance for your support with this. I have purchase history of companies in the sample data in...
I have achieved the formulas only with some new Excel functions like LET(), FILTER(), BYCOL() and DROP(). I hope you can already use them. Otherwise you can use Excel for the web. It should work there.
=LET(a,FILTER(COLUMN($C:$Z),BYCOL($C$3:$Z$7,LAMBDA(in,SUMIF($B$3:$B$7,$B3,in)))>0,"")-2,IFERROR(AVERAGE(DROP(a,,1)-DROP(a,,-1)),"One off purchase"))Try it out and see if it works for you.
I have achieved the formulas only with some new Excel functions like LET(), FILTER(), BYCOL() and DROP(). I hope you can already use them. Otherwise you can use Excel for the web. It should work there.
=LET(a,FILTER(COLUMN($C:$Z),BYCOL($C$3:$Z$7,LAMBDA(in,SUMIF($B$3:$B$7,$B3,in)))>0,"")-2,IFERROR(AVERAGE(DROP(a,,1)-DROP(a,,-1)),"One off purchase"))Try it out and see if it works for you.
Thank you, dscheikey, very much for the solution. This is exactly what I was looking for!