To account for freight on incoming inventory, I allocate the freight charges proportionately over the inventory items by total value. Quickbooks doesn’t have a way to handle that, so I use a simple Excel spreadsheet to do the calculation.

The last entry is always the freight (or handling or whatever I want to distribute). I don’t have much information here because it’s just a throwaway calculator. I get the numbers I need for data entry from the Totals column and don’t save the workbook.

I start by making a couple of defined names. First, ‘Freight’ is defined as `=OFFSET(Sheet1!$B$2,COUNT(Sheet1!$B:$B),0)`

.

It picks the last used cell in the Line Items column. It starts at B2 and COUNTs down the number of filled cells. Note that I don’t use COUNTA because I don’t want to count B2, which is text.

Next I define ‘LineItems’ as `=OFFSET(Sheet1!$B$2,1,0,COUNT(Sheet1!$B:$B)-1,1)`

That picks up all the numbers in column B except the last one.

C3: `=IF(ISBLANK(B4),0,ROUND(Freight*(B3/SUM(LineItems)),2))`

The above formula allocates the freight into the Freight column. Download FreightCalculator.xls.zip

Alternatives

a)

C3 =(B4>0)*(LOOKUP(BigNum,$B:$B)/(SUM(B:B)-LOOKUP(BigNum,$B:$B))*B3)

or with a Named Range “Freight” =LOOKUP(BigNum,Sheet1!$B:$B)

C3 =Freight/(SUM(B:B)-Freight)*B3

b)

The Freight Column is not required:

Without separating the freight the formula is

=(B4>0)*(1+Freight/(SUM(B:B)-Freight))*B3

I do this kind of allocation often enough and in enough varied situations that I’ve added an array function to my addins to do it. That lets me select the output range and type a single formula directly into whatever sheet I’m working on. In many cases I’ll immediately copy and paste values to eliminate the formula. In your application, you’d still need to add the distributed freight to the line items (or modify the function to do it).

‘ Array function, select destination range first, end with Ctrl-Shft-Enter

‘ Distributes 1st argument (scalar) like 2nd argument (column range) into

‘ selected column range, rounding to rDigits digits.

Dim baseArray As Variant

Dim dRatio As Double

Dim i As Long

baseArray = Application.Transpose(baseDist)

dRatio = dTotal / Application.Sum(baseArray)

For i = 1 To UBound(baseArray)

baseArray(i) = round(baseArray(i) * dRatio, rDigits)

Next i

Dist = Application.Transpose(baseArray)

End Function

Unfortunately the formulae given can result in rounding errors. Consider 3 line items each for 100 and freight of 10. A formula of C3 =IF(ISBLANK(B4),0,ROUND(Freight*(SUM($B$3:B3)/SUM(LineItems)),2)-SUM($C$2:C2)) will return freight amounts of 3.33, 3.34 and 3.33. Arbitrary, but everything balances!

This is very handy , I plan to utilise in my excel planning for freight Cheers Simon Australia