Predict next visit

[lionginass]

Hello,


Lets say that i am trying to predict when will a customer come back to my shop.

1 means that customer has visited my shop, meanwhile blank cell means that customer did not visit
I recorded data of 10 days and i need to predict next visit.
It is obvious that customer1 visits every 2 days ant customer2 every 3 days, so Customer1 will visit on 12th day and Customer2 on 13th day.
problem is that i have much more customers and less obvious patterns

So i need a formula which return "blanks" for both customers on 11th day, return "1" for customer1 on 12th day and return "1" for customer2 on 13th day

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Day[/TD]
[TD]1[/TD]
[TD]2[/TD]
[TD]3[/TD]
[TD]4[/TD]
[TD]5[/TD]
[TD]6[/TD]
[TD]7[/TD]
[TD]8[/TD]
[TD]9[/TD]
[TD]10[/TD]
[TD]11[/TD]
[TD]12[/TD]
[TD]13[/TD]
[/TR]
[TR]
[TD]Customer1
[/TD]
[TD][/TD]
[TD]1[/TD]
[TD][/TD]
[TD]1[/TD]
[TD][/TD]
[TD]1[/TD]
[TD][/TD]
[TD]1[/TD]
[TD][/TD]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Customer2[/TD]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


Hope that everything is clear and solutions is easy

 

[Keebellah]

Hi, first thing, which version of Excel (just for those who don't have the same version)
I would suggest one table with the actual visits and another table with the predictions

 

[lionginass]

Hi,

I have excel 2010


[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[TD]I[/TD]
[TD]J[/TD]
[TD]K[/TD]
[TD]L[/TD]
[TD]M[/TD]
[TD]N[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Day
[/TD]
[TD]1[/TD]
[TD]2[/TD]
[TD]3[/TD]
[TD]4[/TD]
[TD]5[/TD]
[TD]6[/TD]
[TD]7[/TD]
[TD]8[/TD]
[TD]9[/TD]
[TD]10[/TD]
[TD]11[/TD]
[TD]12[/TD]
[TD]13[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Customer1[/TD]
[TD][/TD]
[TD]1[/TD]
[TD][/TD]
[TD]1[/TD]
[TD][/TD]
[TD]1[/TD]
[TD][/TD]
[TD]1[/TD]
[TD][/TD]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Customer2[/TD]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Once again, i need a formula to return 1 in Cell M2 and Cell N3

1 means that customer has visited the shop

 

[Keebellah]

Since you cannot have a formula AND a value in the same cell. have you given the idea I opted a thought?

 

[Keebellah]

It will probably have to be done using a macro to calculate the intervals between each visit and the number of visits the customer makes.
I don't think a ready-made formula exists for these type of calculations, but maybe someone with statistics knowledge can shed a light for you.
I guess it will be writing down the options and then write the code for this.