NEWBIE...be gentle

[mhalsey]

First off, you guys rock.
Secondly, I am clueless as to where to begin.
So:
I have 2 vans, each has seats for 14 people.
Total seats: 28.
I have 7 ticket prices: $15, $16, $17, $19, $21, $23, $24
My current reservation system is old enough to remember when Sun owned Java and ColdFusion was a MacroMedia product.
Each week, I receive a clearing statement from my authorization system (credit cards) that tells me that on Day X I have receipts for $200.92 including sales tax. I also know that 8 people rode that day.
My sales tax is 8.25%.
I am looking for any help I can get on how to build a spreadsheet that will help me reduced the guessing game down to 'here are 3 combinations that would meet the number of people who bought tickets, how much they paid, and tax for each.'
Any direction or help or someone to take me to the vet and put me down would be appreciated.

MH

 

[Asala42]

I my be missing something, but wouldn't your before tax price for $200.92 be $185.61? I don't see any combination of those 7 prices across 8 units adding up to that, simply due to the round price numbers.

I'd probably use the Solver add in for this kind of thing. You may need to add it first under Add-ins:
Tools->Add ins on early versions of excel
File->Options->Add-Ins->"Go" in later versions

Basically you build a small table of prices and (initial guess) quantities, and the Solver will try to manipulate the units of each price to reach a target given some constraints (like total units = 8 in your example).

The alternative I think is a messy vba solution that loops through your prices.

 

[Eric W]

I wrote something up for you, but I don't think it will do you a lot of good. The number of possible combinations is just too big. But for what it's worth, here it is:

1) Calculate the dollar amount before taxes. Take the amount(200.92) and divide by 1.0825. Round that to the nearest dollar.
2) Open an empty spreadsheet
3) Right click on the sheet tab on the bottom and select View Code
4) Paste this code into the sheet that opens

Sub test2()
Dim c As Integer, MyTot As Integer, NumRiders As Integer
Dim a(7) As Integer

    myvals = Array(0, 15, 16, 17, 19, 21, 23, 24)
    MyTot = 167
    NumRiders = 8
    
    Erase a
    c = 2
    For i = 1 To 7
        Cells(i, 1) = myvals(i)
    Next i
    
Loop1:
    If WorksheetFunction.Sum(a) = NumRiders Then
        t = 0
        For i = 1 To 7
            t = t + a(i) * myvals(i)
        Next i
        If t = MyTot Then
            c = c + 1
            For i = 1 To 7
                Cells(i, c) = a(i)
            Next i
        End If
    End If
    
    For i = 1 To 7
        a(i) = a(i) + 1
        If WorksheetFunction.Sum(a) <= NumRiders Then Exit For
        a(i) = 0
    Next i
    If i < 8 Then GoTo Loop1:
    
End Sub

5) Change the values in red to match your situation. The ticket prices, the dollar amount, and the number of riders
6) Press F5 to run it.
7) Return to your spreadsheet to look at the results. You'll probably see dozens of possibilities.

There are also ways to use the Solver, but I got this to work first. Let me know if this helps.

(Asala42, I don't think my code's that messy! )

 

[mhalsey]

I my be missing something, but wouldn't your before tax price for $200.92 be $185.61? I don't see any combination of those 7 prices across 8 units adding up to that, simply due to the round price numbers.

I'd probably use the Solver add in for this kind of thing. You may need to add it first under Add-ins:
Tools->Add ins on early versions of excel
File->Options->Add-Ins->"Go" in later versions

Basically you build a small table of prices and (initial guess) quantities, and the Solver will try to manipulate the units of each price to reach a target given some constraints (like total units = 8 in your example).

The alternative I think is a messy vba solution that loops through your prices.

______________________________________
THANK YOU FOR YOUR QUICK REPLY
Here's the updated question:
There are only 3 possible ticket prices ($17, $21, and $25).
Seats < 28 ... number of seats sold will be known at some point... say end of day reconciliation. there were 9 seats sold.
Total amount of sales for the day will be some $ amount.
Is there a way to know what combinations of these 3 ticket prices and the number of seats sold = the $ amount of revenue for the day?

 

[Eric W]

You can change the above macro for 3 prices like this:

Sub test2()
Dim c As Integer, MyTot As Integer, NumRiders As Integer
Dim a(3) As Integer

    myvals = Array(0, 17, 21, 25)
    MyTot = 167
    NumRiders = 8
    
    Erase a
    c = 2
    For i = 1 To 3
        Cells(i, 1) = myvals(i)
    Next i
    
Loop1:
    If WorksheetFunction.Sum(a) = NumRiders Then
        t = 0
        For i = 1 To 3
            t = t + a(i) * myvals(i)
        Next i
        If t = MyTot Then
            c = c + 1
            For i = 1 To 3
                Cells(i, c) = a(i)
            Next i
        End If
    End If
    
    For i = 1 To 3
        a(i) = a(i) + 1
        If WorksheetFunction.Sum(a) <= NumRiders Then Exit For
        a(i) = 0
    Next i
    If i < 4 Then GoTo Loop1:
    
End Sub

 

[jasonlogical]

If you don't want to use a macro, you can get a solution using Excel solver.

	With Tax	Multiplier	Product
17	=G3*(1+$C$2)	0	=H3*I3		Target	=B2
21	=G4*(1+$C$2)	0	=H4*I4		Sum of product	=SUM(J3:J9)
25	=G5*(1+$C$2)	0	=H5*I5		Difference	=M3-M4

<colgroup><col span="5"><col><col></colgroup><tbody>
</tbody>

Use excel solver to have the multiplier column add up to however many tickets were sold. C2 = sales tax and B2 = total.