Forum Discussion
Funds usage monitoring, weekly details
durendal Check the attached. basically it does the following:
a) checks if the #wks is more than the #wks it is looking back
b) MAX(0, ... to make sure it isn't negative
c) MIN( [calculation for total given over the #wks span it is looking at] so the maximum it could be
d) difference in cumulative amounts and show if between 0 and MAX
mtarler,
Thank you so much for your help. At first, I thought the answer was perfectly correct, however when I randomly change some values in the data table, you calculations won't work properly.
I am working on your formula basis since this morning to find a solution, but I am not able to make it work properly until now.
I will explain you whatI've tried to add, and what hasn't worked properly.
To illustrate, I will put an excel file with the situations in the attachement.
[1]
First thing I havn't tell you: if my purchaser is not buying enough, I might take funds back from him, which will result in a Funding Weekly negative. I think I have been trying to settle this by taking off the max formula from the Week<1 column, and by substracting this value if negative, (see in the sheet, [1], not working currently).
[2]
From you original table, if I change values in the weekly purchases data, it might fake the results (all this data is completly random and I am just trying to test all the situation; one thing that can't happend though is for the Outstanding money to be negative).
I have really tried to work on your formulas on my own, but I still don't understand them entirely which makes it imporrible for me to get this right.
I also changed the number of weeks I want to monitor for the seniority in the table, straightforward 1 - 2 - 3 - 4 - >5
May I require you help one more time?
Thank you so much, have a nice day!