CODEWITHSUNDEEP
excelexcel-formulaoffice365array-formulasdynamic-arrays
ciso
ciso

Reputation: 3050

Using Office365 Excel array formulas, how can a cummulative weighted average price be calculated?

Each day, there is a new sale for a given amount and price. How can a cummulative total amount and weighted average price be calculated for that day with an array formula?

Without an array formula, it's simple using this formula and copying it down:

(sum(h3) * sum(i3) + b4 * c4) / (sum(h3) + b4)

Sum() is used to return zero on row 4 when it references row 3, which is a heading. Each day, the previous cumulative total amount and cumulative weighted average price is combined with the new day's amt and price... which calculates a new cumulative total amount and a new cumulative weighted average price.

How can the same thing be done using an array formula? This attempt doesn't work due to circular logic when referencing the previous row's numbers.

=LET(day, A4:A12, amt, B4:B12, price, C4:C12, prevTotalAmt, OFFSET(H4:H12,-1,), prevAvgPrice,OFFSET(I4:I12,-1,),
    newTotalAmt,    IF(day = 1, 0, prevTotalAmt) + amt,
    newTotalPrice,   (IF(day = 1, 0, prevTotalAmt * prevAvgPrice) + amt * price) / newTotalAmt,
HSTACK(newTotalAmt, newTotalPrice) )

enter image description here

Data:

Day Quantity Price
1 100 1.00
2 100 3.00
3 250 2.00
4 400 5.00
5 100 2.00
6 200 3.00
7 100 7.00
8 100 3.00
9 100 2.00

Upvotes: 0

Views: 83

Answers (1)

JvdV
JvdV

Reputation: 75960

This might be a bit too verbose:

enter image description here

Formula in E2:

=LET(amt, B2:B10, price, C2:C10,
    accumFunc,      LAMBDA(a, v, a + v),
    totalAmt,       SCAN(0, amt, accumFunc),
    weightedPrice,  SCAN(0, amt * price, accumFunc),         
HSTACK(totalAmt, ROUND( weightedPrice / totalAmt, 2))  )

The trick here is to use running totals and apply basic math.

First, for each day, the running total amount is calculated using SCAN(). The LAMBDA() function when used with SCAN() has an accumulator as the first parameter and the next array value as the the second parameter, a and v. The initial value of the accumulator is set to zero (first parameter of the SCAN() function).

The calculation is repeated for the weighted price by weighting (multiplying) each amount by the price. HSTACK() is used to show the cumulative amount next to the weighted average price for each day.

Upvotes: 4

Related Questions