Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula for "counting" numbers - not cells!
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! |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula for "counting" numbers - not cells!
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 On Jan 13, 5:44 pm, Willyum wrote: 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! |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula for "counting" numbers - not cells!
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 "Willyum" wrote: 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! |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula for "counting" numbers - not cells!
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) Whe 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 Replace @mailinator.com with @tiscali.co.uk "Willyum" wrote in message ... 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! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Formula for "counting" numbers - bot cells! | Excel Worksheet Functions | |||
Counting cells that "contain" a text string | Excel Discussion (Misc queries) | |||
Formating numbers &"Text" to appear as currency &"Text" in formula | Excel Discussion (Misc queries) | |||
Related to "Counting cells..." (post below) | Excel Discussion (Misc queries) | |||
In excel counting cells in a range which meet condition "Xand<X" | Excel Worksheet Functions |