Forum Discussion
DSO count back method
Yes, it is possible using 365. Anything you can do in traditional spreadsheets by using helper ranges can be achieved with dynamic arrays using LET local variables.
To provide a formula, though, we would need to know the periods and their associated sales figures run from left to right (normal time series) or right to left (reversed), or similar considerations apply if written vertically.
Written newest to oldest one might have
= LET(
opening, SCAN(0, sales, SUM) - sales,
closing, DROP(SORT(HSTACK(opening + sales, outstanding),,,1),,-1),
28 * (closing-opening) / sales
)
Could you give a broader explanation? I haven't used the LET function before and can't get it to work.
Firstly, do you use Excel 365 (or 2021)?
Are you happy to proceed with the sales in reverse order (recent to historic)?
[it is probably possible to reverse the order within the formula but makes it even more complicated]
Have you applied the defined name 'sales' to the sales row and named the value 'outstanding'?
From there the calculation first generates a running total showing the value which is than added to the current sales at each step. The value reached after the value is added is capped by 'outstanding'. The difference is the sales breakdown you require.