[Resolved] Need algorithm to determine what part of an order was charged sales tax

[cafeenman]

Because I'm stupid, I don't have an actual business account and because I'm stupider I mix my orders on Amazon to include business stuff and personal stuff. I know I should stop doing that.

The way Amazon works is you sometimes get charged sales tax for some of the order but not everything.

So I have to go through and try different combinations of things until I hit the magic number.

So I need something checks various combinations of items and find out which ones were taxed and which ones weren't.

The way I'm doing it now is I built in a calculator that's not really a calculator. It's just a list that I put all the amounts into until I hit the right combination.

It should be simple because I know how much tax I was charged and can figure out the total amount of items that add up to the amount that was charged tax and then figure out which items add up to that. But sometimes the orders are pretty large with lots of items on them.

Even if I separate my business from my personal I'd have the same problem when I enter it into expenses.

This is probably a standard sort of thing but it's taxing my brain too much.

[OptionBase1]

Pseudocode:

Keep better records

[SamOscarBrown]

I THINK it might be a bit difficult...why I say that, is you will have several items, and possibly some of them will be of the same exact value (and from what I understand, SOME of those may or may not be taxed). So you see the difficulty in identifying which, say $249.99, was taxed and which was not? I mean, one WITH tax, and one WITHOUT tax might end up being the same ($249.99 in my example.)

It IS a taxing problem.

[cafeenman]

This is the form in question.



==================================

This isn't part of the question but a thing...



Again... not the thing in question but the code behind it for anyone interested.

Private Sub cmdCalculateReverse_Click()
Dim AmountPaid As Double
Dim s As String
Dim AmountAfterTax As Double   ' Amount Paid including tax after deducting Shipping.
Dim AmountToBeTaxed As Double  ' Amount to be Taxed
Dim Shipping As Double
Dim TaxRate As Double

' Assumes that Shipping is non-taxable.
' Deducts Shipping from the AmountPaid to find the taxable portion of the AmountPaid and the amount of Taxes paid.

On Error Resume Next

s = Replace(txtTotalOrderAmountReverse.Text, "$", vbNullString, 1, -1, vbTextCompare) ' Strip dollar sign from text to get the value.

AmountPaid = Val(s) ' Total Amount paid.

s = Replace(txtShippingReverse.Text, "$", vbNullString, 1, -1, vbTextCompare)

Shipping = Val(s) ' Amount paid for Shipping

TaxRate = Val(txtTaxRateReverse.Text) ' The tax percent charged.

AmountAfterTax = AmountPaid - Shipping ' This value is the Taxed Amount Plus the Taxes Paid.

AmountToBeTaxed = AmountAfterTax / (1 + TaxRate) ' Find the Amount that was taxed.

TaxedAmountReverse.NewValue = AmountToBeTaxed ' Put it in the textbox.

SalesTaxPaidReverse.NewValue = TaxRate * AmountToBeTaxed ' Find the dollar amount of taxes paid and put it in the textbox.

End Sub

[cafeenman]

Pseudocode:

Keep better records

My record-keeping not withstanding that doesn't resolve the issue.

For example, let's say I do that and keep my business expenses and my personal purchases separate.

So I buy these items from Amazon. This is a shorter, easier list.

Item 1: $19.50
Item 2: $35.50
Item 3: $8.50
Item 4: $127.50
Item 5: $22.50
Item 6: $11.50

I pay 6.5% sales tax. The sales tax charged on this order was $2.02. Which items add up together and charged 6.5% came to $2.02?

Also, let's say we eliminate everything that alone would be more than that. So we can eliminate the $127.50 right away.

I can do this manually but I don't want to. It's time-consuming and tedious.

[cafeenman]

I THINK it might be a bit difficult...why I say that, is you will have several items, and possibly some of them will be of the same exact value (and from what I understand, SOME of those may or may not be taxed). So you see the difficulty in identifying which, say $249.99, was taxed and which was not? I mean, one WITH tax, and one WITHOUT tax might end up being the same ($249.99 in my example.)

It IS a taxing problem.

Yeah... fortunately I haven't run into having multiple items on the same invoice having the same price. But that would make the problem impossible if some were taxed and some weren't.

I mean I could just place individual orders for each item.

[OptionBase1]

My record-keeping not withstanding that doesn't resolve the issue.

For example, let's say I do that and keep my business expenses and my personal purchases separate.

So I buy these items from Amazon. This is a shorter, easier list.

Item 1: $19.50
Item 2: $35.50
Item 3: $8.50
Item 4: $127.50
Item 5: $22.50
Item 6: $11.50

I pay 6.5% sales tax. The sales tax charged on this order was $2.02. Which items add up together and charged 6.5% came to $2.02?

Also, let's say we eliminate everything that alone would be more than that. So we can eliminate the $127.50 right away.

I can do this manually but I don't want to. It's time-consuming and tedious.

Apologies, I thought for sure Amazon's order history would somehow indicate which items were taxed in each order. I just checked some of my past orders that had a mix of taxed and non-taxed items, and I saw no indication of which was which. That's just crazy to me.

[cafeenman]

Yeah... Amazon is very low-effort. There is zero indication like you said which is why I have a problem regardless of whether or not I separate my business from my personal. The problems still remains.

I am making more of an effort to not mix the two in the same orders though so I have fewer numbers in play at least.

[SamOscarBrown]

I was definitely not aware of this:

The way Amazon works is you sometimes get charged sales tax for some of the order but not everything.

I always thought that the tax was calculated on the TOTAL costs of all items. Hmmmmm.....

[cafeenman]

Hi Sam,

No. I don't know where you're from but online orders only have to collect sales tax from items sold within the state the order is being shipped to. I live in Florida.

So let's say I order seven items (all in the same order). If three of them are shipped from Florida I pay sales tax on those three items but not the ones coming from other states.

That part makes sense. The part that doesn't make sense is that nowhere on the invoice do they indicate which items were taxed and which weren't.

[SamOscarBrown]

NC (see my Icon)....well, it seems like an impossible task (to me). Good Luck!

[cafeenman]

I think it's just a finding-the-right-combination problem which is a common problem.

What combination of items allows the most items to fit in a basket given a long-ass list of items for example.

I'm looking from an approach that assumes no two items have the same value.

Also too, I could actually look at the packaging the order comes in and see where it was shipped from and then if it came from Florida I'd have my answer.

In fact, I think I just found my answer.

[Tabi]

I don't know if it's what they're looking for. Keep in mind that my English is very bad, and it is very difficult for me to understand the problem of the proposals. In the spirit of trying to help, I sat down for a while, and wrote this code. Sorry if that's not what you're trying to resolve.

This function searches from an Amount passed in the first Parameter, which is the total tax paid in US$, which were the Items, from a list of 2 to many more items, passed from the second Parameter, which they paid taxes

If there is more than one solution, each list of successful articles is separated from the next, with a double slash " // "

There remains to be resolved a possible rounding that exists in the total tax amount data, which I imagine could happen. But sorry, I'm very tired. Also, I don't know if it's what you're looking for.

Furthermore, it was not thoroughly tested. "We already know, what is not tried, does not work"

Function Determinar(Monto As Single, ParamArray PrcArt() As Variant) As String
    
   'Procedimiento para encontrar que Articulos o Precios, fueron los que abonaron impuestos, de un listado
   
   'Primer Parametro:
   'Monto: Total de Impuestos Cobrados
   
   'Restantes Parametros:
   'PrcArt() Array que contiene todos los Precios cobrados. Puede introducirse 2 o mas
   
   
   
   'Ejemplo en Ventana Inmediato
   '            ? Determinar (1.07965, 10.23, 2.21, 33.32, 14.40, 56.24)
   '            2,21 / 14,40
   
   'La salida indica, que:
   '                      el impuesto de 2.21,  que es: 0.14365 (2.21 x 0.065)
   '                    y el impuesto de 14.40, que es: 0.936 (14.40 x 0.065)
   '                    ambos, sumados dan 1.07965, que es el Monto de Impuesto pasado en el primer Parametro
    
    
    
    Dim CntArt As Integer        'Cantidad de Articulos, o Precios, a analizar. Pueden ser 2, 3, 4, o muchos mas
    Dim CntPsb As Long           'Cantidad de Posibilidades, que se generan, al combinar todas las posibles
    Dim P As Long                'Posibilidad
    Dim IntPsb As Integer        'Entero de Posibilidad Tratada
    Dim BinPsb As String         'Binario de Posibilidad Tratada. Este String, si se lo consiera Numero Binario es igual a IntPsb
    Dim B As String              'Bit
    Dim Tax As Double            'Impuesto correspondiente a Articulo Tratado
    Dim TotTax As Double         'Impuesto Total que se obtiene de ir sumando los Articulos que estan en "1"
    Dim PsbExt(10) As String     'Posibilidad que tuvo Exito
    Dim CE As Integer            'Cantidad de Posibilidades que tuvieron Exito
    Dim S As String              'Salida de la Funcion
    Dim CA As Integer            'Cantidad de Articulos en "1"
   
   'Cantidad de Articulos
    CntArt = UBound(PrcArt()) + 1
   
   'Cantidad de Posibilidades
    CntPsb = 2 ^ CntArt
   
   'Recorrida Posibilidad x Posibilidad
    For P = 1 To CntPsb
       
       'Obtener Entero de Posibilidad tratada
        IntPsb = P: BinPsb = ""
       
       'Obtener Binario de Posibilidad tratada
        Do
            BinPsb = CStr(IntPsb Mod 2) & BinPsb
            IntPsb = Int(IntPsb / 2)
        Loop While IntPsb >= 2
        If IntPsb = 1 Then BinPsb = "1" & BinPsb
        
       'Completar on 0 a la izquierda
        If CntArt > Len(BinPsb) Then BinPsb = Right(String(CntArt, "0") & BinPsb, CntArt)
       
       'Recorrer Bit x Bit, Posibilidad Tratada, que seria equivalente a recorrer Art x Art, almacenados en PrcArt()
        TotTax = 0
        For A = 1 To CntArt
            B = Mid(BinPsb, A, 1)
            If B = "1" Then Tax = PrcArt(A - 1) * 0.065 Else Tax = 0
            TotTax = TotTax + Tax
        Next
   
       'Si hay Coincidencia guardar caso
        If TotTax = Monto Then
            PsbExt(CE) = BinPsb
            CE = CE + 1
        End If
    Next P

   'Recorrida por Resultados Exitosos, y obtener S, que es la Salida de la Funcion
    If CE > 0 Then
        For E = 0 To CE - 1
            CA = 0
            For A = 1 To CntArt
                B = Mid(PsbExt(E), A, 1)
                If B = "1" Then
                    If CA > 0 Then S = S & " / "
                    S = S & Format(PrcArt(A - 1), "0.00")
                    CA = CA + 1
                End If
            Next A
            If E < CE - 1 Then S = S & "    //    "
        Next E
    End If
   
   'Devolver
    If S = "" Then S = "Sin Exito"
    Determinar = S
    
End Function

mmmm... I think the problem was not this... ha ha ha, good night!