Formula for "counting" numbers - not cells!

[Willyum]

I am trying to create a formula that will “count†a set of given numbers. I
want to hand out numbered raffle tickets and have the ability to track how
many are given to each representative of my group. The issue is the numbers
printed on the tickets are large and each member receives various amounts of
tickets.

Here is an example

I give Rep A ticket numbers 73267 through 73356, which are 90 tickets. If I
use the subtract function – it equals 89! I am one off!

Unfortunately, it is complicated. The collective “They†want to be able to
track the tickets, who sold what tickets, what tickets were not sold,
percentage of tickets sold by a Rep that won and lost…. I could go on and
on….

FUN, I know…..


Any help would be appreciated!

 

[Ag]

One way to keep track of the tickets is to have indvidual tickets
numbers captured in excel -one per each row in column A -I am assuming
numbers are in series in which case you can use autofill command to
create the list of raffle ticket numbers and then in coumn B could the
representative and column C could ve the status of the ticket.
Once you have the data in that format ,you can create a pivot table to
with representative as the row labels and and status as both columns
label and data values ,to track ticket status
HTH
Regards
Anirudh

 

[Mike H]

I'm afraid that's where you are because that's how subtratction works so

a2=a1+1 is your answer but you could do it another way. You have a record of
the tickets issued; you must have or you can't do any calculations, and could
keep them in a table thus

Col a col b col c
col d
Name First ticket Last ticket
Joe Bloggs 1 51
=b3-b2=10
Me 52 88
You 89 1020

as you can see the formula in d2 (=b3-b2) retuens the correct No of tickets
sold for that person. You could then add further columns such as 'tickets
returned' and a simple subtraction returns the amount sold.

It's also easy using a simple lookup on this sorted table to find out who
sold a ticket.
=INDEX(A2:A10,(MATCH(12,B2:B10,1)))
would reveal 'Joe Bloggs' sold ticket 12

Mike

 

[Sandy Mann]

use the subtract function - it equals 89! I am one off!

If you put in 90 fence posts you have only 89 spaces between them, which is
what you did in your subtraction. You must add one if you are actually
using the first and last numbers.

With the names of Reps in A2:A40, the start numbers of the Rep's tickets in
B2:B40 and the last number in C2:C40, for the Rep's name of the winning
ticket in - say - Cell G2 try:

=INDEX(A2:A40,MATCH(1,((B2:B40<=G2)*(C2:C40>=G2)),0))

This is an array formula and so must be entered with Ctrl + Shift + enter
not just enter.

The percentage of tickets sold by that Rep is:

=(RS-RL+1)/(VF-VL+1)

Where:
RS = Rep's Start Ticket No
RL = Rep's Last Ticket No
VF = Very Frst ticket No
VL = Very Last ticket No

... I could go on and on..... <g>

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk