Freight Allocation

[jackson_hollon]

Hello everyone, Thanks for looking at my thread. I need some help on the attached file. I have invoice number on column A. There are a lot of different items on each invoice. Some of them has freight charge, some not. All different items has different cost. I need to come up with a formula (column E) that allocate the freight cost (column D) to each item based on the item cost (column B).

I hope I explain it well enough.

Thanks,

Jackson

[FDibbins]

Can you explain how you arrive at the values in E, and why they are in the cells they are?

eg the fright for 282367 is 350, but you have only added in 300?

[jackson_hollon]

Ford,

The 282367 has freight cost 350, and the allocation is from row E4 to E8.

Thanks for the help.

Jackson

[humdingaling]

try this

Please Login or Register  to view this content.

[jackson_hollon]

Oh humdingaling,

I had ever thought about this thread can be solved as I tried so many different ways.

Thank you so much for the help.

Jackson

[AZ-XL]

Hi


=B2/SUMIF(A:A;A2;B:B)*SUMIF(A:A;A2;D:D)

Options-advanced-show a zero in cells that that have zero value

[humdingaling]

thanks for the rep

it also works for weighted cost if you need to flush that out

[jackson_hollon]

humdingaling,

Thanks for the weighted cost. I have one more question regarding your first formula. I also need to allocate the sales tax. I have attached a file here. I do not know how to allocate the sales tax to the product only without assigning it to the freight charge?

[humdingaling]

So im assuming its just randomly on one line per invoice?
in which case use the same principle

Please Login or Register  to view this content.

work out the % based on volume and times by sales tax

[jackson_hollon]

humdingaling,

Thanks a lot.

Jacksdon