Forum Discussion
Funds usage monitoring, weekly details
Dear community, I am coming to you with an excel problem that I am sure will challenge you. I am not sure what I expect is possible to compute though. I am a reseller. T buy what I sell, I send...
i still don't get it as you week 1 shows 1000 in and 900 out so 100 left and then another 1000 in and 200 out so ALL of the 1000 from wk 1 and more is spent but now you want to show 900 but that 900 is from wk 2. And is this cumulative table what you really want or are you hoping it will be a means to the end where you want a value N for which input week still has a outstanding balance because that could be a single formula.
mtarler ,
"i still don't get it as you week 1 shows 1000 in and 900 out so 100 left and then another 1000 in and 200 out so ALL of the 1000 from wk 1 and more is spent but now you want to show 900 but that 900 is from wk 2"
--> Yes, the 900 is from week two, meaning that as in week two, those 900 are less than one week old.
How I explained the weeks isn't very clear, sorry;
I distinguish two kinds of weeks: in my expected result table, Vertically, it is the point in time we are at. Week three means the third week of the year;
Horizontally, it is the seniority of the money, in weeks. So in week n°2 (of the year), those 900 outstanding are one week old, since they are coming from a funding tht happend within this same week. That's why I put 900 in the Week<1 vertically in the table, for the week n° 2 horizontally .
In week n°5, we can see 1000 in the Week<1 column, which are the 1000 funded in week 5 that havn't been used (those funds are less than a week old); 1000 in the 1<Week<2 which are the 1000 funded in week 4 that havn't been used (those funds are now between 1 and 2 weeks old); and 1000 in the 2<Week<4 which are the 1000 funded in week 3 that havn't been used (those funds are now between 2 and 3 weeks old).
Hope it makes it clearer.
"And is this cumulative table what you really want or are you hoping it will be a means to the end where you want a value N for which input week still has a outstanding balance because that could be a single formula. "
--> I am not sure to understand everything; the table I want is the "expected result" cells in orange; it is suppose to split the seniority of the funds in different categories, to check the efficiency of avery purchaser quickly and to assess which one deserves new funds.
Hope it is clear, thank you again
"i still don't get it as you week 1 shows 1000 in and 900 out so 100 left and then another 1000 in and 200 out so ALL of the 1000 from wk 1 and more is spent but now you want to show 900 but that 900 is from wk 2"
--> Yes, the 900 is from week two, meaning that as in week two, those 900 are less than one week old.
How I explained the weeks isn't very clear, sorry;
I distinguish two kinds of weeks: in my expected result table, Vertically, it is the point in time we are at. Week three means the third week of the year;
Horizontally, it is the seniority of the money, in weeks. So in week n°2 (of the year), those 900 outstanding are one week old, since they are coming from a funding tht happend within this same week. That's why I put 900 in the Week<1 vertically in the table, for the week n° 2 horizontally .
In week n°5, we can see 1000 in the Week<1 column, which are the 1000 funded in week 5 that havn't been used (those funds are less than a week old); 1000 in the 1<Week<2 which are the 1000 funded in week 4 that havn't been used (those funds are now between 1 and 2 weeks old); and 1000 in the 2<Week<4 which are the 1000 funded in week 3 that havn't been used (those funds are now between 2 and 3 weeks old).
Hope it makes it clearer.
"And is this cumulative table what you really want or are you hoping it will be a means to the end where you want a value N for which input week still has a outstanding balance because that could be a single formula. "
--> I am not sure to understand everything; the table I want is the "expected result" cells in orange; it is suppose to split the seniority of the funds in different categories, to check the efficiency of avery purchaser quickly and to assess which one deserves new funds.
Hope it is clear, thank you again
- ahhhhh, I get it now. I try my hand at it later when I have some time, but should be do-able now that I understand.
- Thank you so much!
Communication and expressing ideas properly is really the n°1 difficulty in any project 🙂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