3

I have an inventory tracking system that unfortunately doesn't provide data for quantity change over weeks. I'm attempting to develop something similar in Excel that will give easy access to average drop week over week in order to identify approximate reorder thresholds.

I have an inventory tracking spreadsheet with new data input weekly. I've been trying to develop a formula that will recursively calculate the change in quantities between consecutive weeks, and then average those changes to give an estimated usage count per week. Ideally this doesn't add additional output cells or use macros; just drop new data in and have the averages update automatically.

excel inventory chart

I'm familiar with both functions of calculating change and averaging, but would require helper cells to hold the change data and then average that row. In essence, in Cell B2: =AVERAGE(D2-C2, E2-D2, ...) run through the entirety of Row 2, including new data added weekly in Column O, P, Q, etc.

I have not worked with any kind of looping or recursion in Excel before as I typically work with static datasets -- is this even possible with formulas? I have not found formulas such as WHILE, LOOP, FOR.

I've tried utilizing COUNT to get the number of cells within a table row, and then INDEX to find specific data and compare backwards =INDEX(A1:N6, 2, [COUNTFORMULA]) - INDEX(A1:N6, 2, [COUNTFORMULA]-1) but obviously this is missing any kind of recursion to work down each weeks data points.

Improve this question
0

2 Answers 2

Reset to default
4

You can use this formula on row 2 and drag it down:

=LET(data,TRIMRANGE($C2:XFD2),AVERAGE(DROP(data,,1)-DROP(data,,-1)))

The definition of the data name is using TRIMRANGE to return those cells on the current row, from column 2 onwards, which contain data (this will grow as you add more weeks, so this is a dynamic way to capture new weeks).

In the example in the image, data contains cells the data in C2:N2.

DROP(data,,1) removes the first column from data, meaning what you're left with is the data in cells D2:N2.

Similarly, DROP(data,,-1) removes the last column, leaving you with the data in cells C2:M2.

Subtracting the former from the latter returns an array of the differences between successive weeks.

Wrapping that in AVERAGE returns the average of those differences.

enter image description here

Improve this answer
Sign up to request clarification or add additional context in comments.

3 Comments

Nice use of trimrange. Note that $C2:.XFD2 is equivalent to TRIMRANGE($C2:XFD2)
Works perfectly in my testing! Removing the AVERAGE function expanded the array which really helped clarify what it doing. If I understand right, the "recursiveness" I was looking for happens as a result of the LET function using the specified TRIMRANGE as the array applying the AVERAGE(DROP) calculation? I think what's not clear to me is where the iteration to consecutive cells occurs. All of these functions will certainly have future applications in my work so want to make sure I have the best understanding. Thank you much!
Glad it worked. Actually we don't need recursion here, we just need to subtract the array from a shifted version of itself. Array subtraction happens element by element, so you end up with that array of differences. TRIMRANGE is there to ensure you're not using any blank cells at the end, and to make sure you capture any new weeks as they're added.
0

Dynamic formula. Just change the week array C2:N6 to any updated actual array.

=BYROW(C2:N6, LAMBDA(r, AVERAGE(DROP(r,,1)-DROP(r,,-1))))

enter image description here

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.