Formula for counting customers waiting to be served

[jimtmcdaniels]

Our office issues Take a number tickets to customers when they are waiting to
be called-served. Hundreds each business day.
These paper tickets are premade, currently with each roll starting with
ticket #A00 and ending with ticket #J99. (why they don't go to the letter "Z"
is a mystery anyway)

I've got an excel sheet that a staff employee enters hourly the current take
a ticket number pulled and the current take a ticket number being served for
statistics.

I want a formula that compares these 2 data entries and counts how many take
a ticket numbers have yet to be served at that time (how many customers are
waiting to be served).
But since the tickets numbers contain a letter, I'm having trouble coming up
with a formula.
Thanks for any help!

 

[Ron Coderre]

Try this non-VBA method:

A1: (Last Ticket Out)
A2: (Last Ticket Served)

Put this formula in B1 and copy to B2:
=(CODE(LEFT(A1,1))-65)*100+RIGHT(A1,2)

B3: =B1-B2

For
A1: B20
A2: A99

B1: 120
B2: 99
B3: 21

Does that help?

***********
Regards,
Ron

 

[bpeltzer]

If the ticket# (A00, leave off the #) is in A2, then the formula
=100*(CODE(LEFT(A2,1))-65)+VALUE(RIGHT(A2,2))
will convert it to a sequential number from 0 to 999. You can apply the
formula to both the 'last served' and 'last taken', and the difference will
be the number waiting each hour. (To allow for rolling over from J99 back to
A00, you could take the difference mod 1000.)
HTH. --Bruce

 

[Ron Coderre]

If the user might accidentally type a99, instead of A99, use this formula in
B1 and copy to B2:
=(CODE(UPPER(LEFT(A1,1)))-65)*100+RIGHT(A1,2)


***********
Regards,
Ron