Your description of your approach sounds fine. Without knowing what the cells in your Excel formula refer to, I can't comment on its accuracy.
Simple illustration: I have two invoices. On the first there are two assets, one priced at 100 and the other at 200; invoice total is 300.
On the second invoice there are also two items, with prices of 50 and 150, respectively; invoice total is 200.
The total of all four items = the total of the two invoices = 500.
So the first item is 20% of the total of 500; the second is 40%; the third and fourth are 10% and 30%, respectively. (Important: make sure your percentages total to 100, as in this case: 20 + 40 + 10 + 30 = 100. Check.)
Then I'd allocate the total freight / transportation cost to the four items in accordance with those percentages. If the total shipping for those two invoices was, say, 50, then the four items would be allocated freight cost of 10, 20, 5, and 15, respectively (again, make sure these total to 50).
If you're numbers aren't coming together the way they should, a couple of possibilities come to mind:
- Are there other items on the invoice (taxes or other charges) that need to be allocated to the individual items? These might throw your calcs off if so.
- Anytime there's rounding involved in the computations, there's a chance that the result might be off a tad, but it should only be a very small amount.