Calculating distribution of tickets

[robeau]

Hi,

I need to calculate the distribution of tickets across a predefined value of tickets (ie. $5, $10, $50, $100, $1000), given the total dollars and total tickets.

For example, if total dollars is $30 and total tickets is 3, then there were 3 - $10 tickets, and no others. Or, if total dollars is $25 and total tickets is 4, then there were 2 - $10 and 2 - $5 tickets, and no others.

Is it possible to calculate this distribution in Excel? Has anyone done this before?

Thank you in advance.

 

[mscallisto]

Did he but 5 $10 tickets or 1 $50 ticket?

My thought is there may be multiple answers.

 

[robeau]

Very true. But I know the total number of tickets. So if the dollars were 50, and total tickets was 1, then it would be 1 - $50, but if total tickets was 5, then it would be 5 - $10.

The formula is simple if there were only two ticket denominations. But I have 5 different denominations, so if the total dollars was $1465, and total tickets was 9, can I use formulae in Excel to tell me that it would be 1 - $1000, 4 - $100, 1 - $50, and 3 - $5 tickets?

Thanks for the response.

 

[mscallisto]

4 - $100, 1 - $50

or

9 $50's

or

2 $100's 5 $50's

etc

how would you know which one?

 

[SkipVought]

because you have given: BOTH Total Price AND Total # Tickets. so...
[tt]
TP TT Dist
450 5: 4*100+1*50
450 9: 9*50
450 7: 2*100+5*50
[/tt]
Intriguing!

Skip,

Want to get great answers to your Tek-Tips questions? Have a look at faq222-2244

 

[robeau]

By the total number of tickets! Each of the scenarios you presented, although they add up to $450, has a different number of tickets (5, 9, 7). I know the number of tickets, so I would know the correct combination. But can I get a spreadsheet to calculate the different combinations, and select the correct one?

Thanks again.

 

[rdeleon]

I think eventually you will run into scenarios with duplicate number of tickets. Take the following as one example:

could be:
$15 3 - $5
$10 1 - $10
$300 3 - $100
$325 7 tickets

or

$5 1 - $5
$20 2 - $10
$100 2 - $50
$200 2 - $100
$325 7 tickets

 

[robeau]

Yes, you are correct. I think there are just too many variables to be able to make it work.

Thanks for the reply.