To the retail gurus, I need a little bit of help in a certain formula.
I've been trying to figure out how to do a Weeks of Sales calculation over a straight table chart.
To illustrate what I'm trying to accomplish, please see the table below.
| Fiscal Period | BOP | SLS Retail | Weeks of Sales |
| P01 | 4,600,000.00 | 1,211,393.00 | 3.44 |
| P02 | 4,429,066.00 | 1,072,099.00 | 3.29 |
| P03 | 4,584,333.00 | 1,766,039.90 | 6.04 |
| P04 | 4,119,848.00 | 1,238,298.00 | 8.87 |
| P05 | 3,922,274.00 | 1,198,110.00 | 15.20 |
| P06 | 4,341,743.72 | 350,615.56 | 74.30 |
Weeks of Sales is calculated by looping through each SLS Retail row and getting the number of periods/weeks BOP could cover it given the actual sales per period. For example, the P01 BOP of 4,600,000 could be spread over roughly 3.44 periods (total SLS Retail from P01 to P03 = 4,049, 531).
I've been exploring combining rangesum and chart inter-record functions but I can't figure out how to make it conditional such that it compares BOP with a running sum of SLS Retail.
Any hint would be of great help.
TIA!
