Small Business need help tracking Customers Daily Purchase Windows7 Excel 2007

[Ms. Isabella]

Hello, I'm currently starting a small business of delivering bottled drinking water (gallon) to my customers.
Technically, I sell them the water and I only lend the gallon bottles to my customers, so I'd like to keep track of who's keeping it and how many of it remains in every customer. Every now and then, I collect the empty gallon bottles and replace them with the filled ones. So basically, this is a purchase data of an unspecific range of time between customers. Here's how it looks like, and please note that I'm nowhere near the excel expert and I only use some very basic formula like SUM.

My question is :
- I'd like to know which customer bought my product in the most time interval, so I can approach him/her personally and try to improve my selling.
- I'd like to know if they are running out of water in, let's say, three to five days (per gallon) after their last purchase, without them notifying me first, so I can deliver it to them voluntarily and see if I can make a good deal, or at least I can make a call and see if they need some more.

I tried to find the answers from Google, but I'm not really sure if I put the keywords correctly. I also tried the online template with the keywords: 'customer', 'daily purchase', and some others I can't remember but to no avail. I'd really appreciate any help or tips, or even the source of where I could learn more about this case. Thanks!

 

[smkyle]

When you say "Most time interval" are you referring to how long ago you last sold them some water? One thing worth mentioning is that dates are stored as numbers so functions like MAX() will work, and adding 3 to a date will add 3 days. Also, there is a function TODAY() which returns today's date.

So, if you want to test if a person's last purchase was more than 3 days ago, you could do D1=if(TODAY()-MAX(B6:B16)>3,"REFILL!","No Refill")

If you want to know how often they are refilling on average you could do =AVERAGE(B6:B16). Note: AVERAGE() will ignore BLANKS but not ZEROES.

Let me know if that is a good start.

 

[Ms. Isabella]

Thanks, it works like charm!! You're awesome!! <3

By the way, what I meant with "most time interval" is the length between their purchases. For an example, Customer#1 to #5 are constantly refilling their bottles every 3 days on average, but Customer#6 average refill is 7 days, and Customer#7 is 10 days. Is there any way I can differentiate Customer#6 and #7 from other customers without having to look at every customers average refill, one by one?

Uh and one more thing, can I add the bottle quantity as a part of the formula? You see, when someone refilled 3 bottles in a day, I would want to spare him 9 days, not the average 3 days.

 

[smkyle]

Thanks, it works like charm!! You're awesome!! <3

By the way, what I meant with "most time interval" is the length between their purchases. For an example, Customer#1 to #5 are constantly refilling their bottles every 3 days on average, but Customer#6 average refill is 7 days, and Customer#7 is 10 days. Is there any way I can differentiate Customer#6 and #7 from other customers without having to look at every customers average refill, one by one?

Uh and one more thing, can I add the bottle quantity as a part of the formula? You see, when someone refilled 3 bottles in a day, I would want to spare him 9 days, not the average 3 days.

For the first question, my suggestion is that you have a worksheet with a list of all of your customers. Then you can look up the average based on the name of the customer and have a list. Similarly, you could lookup the Refill/No Refill cell and have that on the same list. There are different ways to do this depending on how your spreadsheet is set up. Are you working with a single file or multiple files. If you have a tab for each customer (which I recommend), then my answer is different than if you have a different file for each customer. Also, if your tab names are the same as your customer names, then I could give you an answer that requires less work.

As for the second question, my technique would be F1=offset(E5,count(B6:B16),0) which will give you the current number of bottles. Then, D1=if(TODAY()-MAX(B6:B16)>3*F1,"REFILL!","No Refill")

Also, you should learn about absolute references. Many of my formulas should probably include them but I don't want to overwhelm you. e.g. $B$1, $B1, B$1, B1 are different references to B1, but it is important to learn when each is appropriate.

 

[Ms. Isabella]

Hey, as you suggest I learned about the excel basics (ouch) overnight, and more about absolute reference. Now I'm able to make my formula better! I'm still not quite sure about what should I do with the multiple worksheet tho, but I'll look up to it. It certainly is a good thing to know what I should search and learn specifically. Thanks again, smkyle!

 

[smkyle]

You are very welcome =) One last parting piece of advice. When I first started using Excel I wasted a lot of time because I didn't know how to use the different paste features.
At work I very regularly use

Paste Format
Paste Links
Paste Formulas
Paste Values

 

[Ms. Isabella]

Hello! I want to update my current data. So far, I'm satisfied enough for it's a major improvement from my previous data.

Details are as follow:
I1=TODAY()-LOOKUP("zzzzz",B6:B16)
it specifies the range between today's date and the last purchase date.

I2=IF(I1>(3*VLOOKUP(9.99999999999999E+307,C6:C16,1))),"REFILL!","No Refill")
I managed to add the quantity of the bottles, thanks to you! Every bottles has 3 days time and their last purchase is 5 days ago; even so, they did refill 15 bottles that day, so it spares them 15x3=45 days until the "No Refill" becomes "REFILL!".

I haven't figure out how to work on the multiple sheets to differentiate one customer to another tho, and I'm trying to add the average purchase to the formula, so I'm not limited to 3 days per bottle for all customers, but I'm sure I will be able to!

Cheers!

 

[smkyle]

There are many advantages to having everything in the same file. You can merge files without too much effort by right clicking on the tabs and moving them to a single main file (the proper term is workbook an the proper term for tab is worksheet).