From the dates in the headings, D2:O2 looks like the next 12 months?divide the stock on hand by the average demand of the last 12 months
=B2/AVERAGE(D2:O2)
20 03 09.xlsm | ||||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
B | C | D | E | F | G | H | I | J | K | L | M | N | O | |||
1 | SOH | Months Coverage | Mar-20 | Apr-20 | May-20 | Jun-20 | Jul-20 | Aug-20 | Sep-20 | Oct-20 | Nov-20 | Dec-20 | Jan-21 | Feb-21 | ||
2 | 425 | 7 | 23 | 39 | 69 | 54 | 94 | 77 | 21 | 90 | 64 | 96 | 94 | 86 | ||
3 | 7 | 0 | 10 | 39 | 57 | 24 | 50 | 11 | 10 | 86 | 49 | 44 | 61 | 40 | ||
4 | 289 | 4 | 35 | 92 | 4 | 74 | 90 | 85 | 10 | 84 | 36 | 55 | 62 | 99 | ||
5 | 500 | 11 | 90 | 1 | 7 | 1 | 8 | 73 | 50 | 46 | 66 | 31 | 92 | 43 | ||
Sheet2 |
Cell Formulas | ||
---|---|---|
Range | Formula | |
C2:C5 | C2 | =IFERROR(MATCH(B2,SUBTOTAL(9,OFFSET(D2,,,,COLUMN(D2:O2)-COLUMN(D2)+1))),0) |
Press CTRL+SHIFT+ENTER to enter array formulas surrounded with curly braces. |
this works but is there a way for it show to one decimal place?Or try this in C2:
=IFERROR(MATCH(B2,SUMIF($D$1:$O$1,"<="&$D$1:$O$1,D2:O2)),0)
Enter with Ctrl+Shift+Enter.
Yes. I like to split it into several rows to not mash several formulas into one.this works but is there a way for it show to one decimal place?