User Profile
durendal
Brass Contributor
Joined 4 years ago
User Widgets
Recent Discussions
Sharing data in a semi hidden way
Dear Community, Let me explain my issue. I have 80 persons reporting for me. Each week, there is a certain type of information I would like to communicate to them. Very simple information, just a weekly value, I'll provide an exmple below. The issues are: 1 - I can't do that manually, it would take way too long; 2 - I can't allow them to see the other employee's values. We work through shared folders; basically I created 80 folders that I shared with them individually (so each guy has access to one folder) and I can get the data from the 80 folders. I usually work with their data with power query so if your solutions include it, feel free to explain. I was thinking of very hiding the file, protecting the file, etc etc but in the end if the data is protected the queries or the formulas to get the data will not reach it, if you see what I mean. Let me show you the exemple of data I want to share: Week 1 Week 2 Week 3 Employee 1 100 200 100 Employee 2 500 600 0 Employee 3 20 5 50 Employee 4 0 20 20 Employee 5 30 0 20 To employee 2, I want to share the line employee 2 / 500 / 600 / 0 But nothing else about the other values. Any idea welcomeRe: Categorizing the below range for entire column into header wise.
sanjanakuril Here is a solution, if I understand your issue. With the left function, you will get the first number of your argument. Multiply it by one in order to have it as a value. Second, create a table showing that number 1 = ABC, 2 = DEF etc. Third, with an xlookup, associate the number to the code. Fourth, create a pivot table appropriately Fifth, copy as values if necessary Hope it helpsComplex Cycle time for users liking to stretch their brains with formulas
Hello everyone, I think I have a complex problem. I need to find a formula to calculate exactly how long it takes to convert my financing into a purchase. Let me explain (part of) my activity: I have several buyers, to whom I send funds, and who with these funds buy materials that I need in large quantities. We won't talk in money, but in tons of materials to simplify. So if I send 10 of funds, I expect him to buy 10 tons of materials. My goal is to know, if I send 10 of funds, in how long on average does he manage to convert the funds into materials. I have so far found a formula that gives an average that respects a FIFO logic. You will find all the detail in the exhibit. For me this formula is already quite complex. However, it still has two problems: -if I send 10 of funds and my buyer buys 9 tons after 3 days, then buys 1 ton of materials after 17 more days, then my formula will return that on this funding, its cycle time is 20 days. In reality, his cycle time would be ((3*9)+(20*1))/10 = 4.7 days. Secondly, I may decide to withdraw funds from one buyer (if he performs badly), and allocate them to another buyer. This will cause problems for my current formula, as explained in the PJ. If anybody is interested in helping me, I am here to answer any question. Lot of thanksCounting consecutive cells equal to 0 at the end of the range
Hell everybody, I am looking for a formula to count the consecutives values of 0 at the end of a line. Let me show you an example with the expected result. Let's say the only two values in this table are 0 or 1: I havn't been able to find a formula, especially with the fact that I don't want the maximum consecutive values of zero in the line, but only the consecutive values from the end of the line. Thank you allRe: Funds usage monitoring, weekly details
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!Re: Funds usage monitoring, weekly details
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 againRe: Funds usage monitoring, weekly details
mtarler, Thanks a lot for trying. Indeed it is not the result I am expecting. Let me try to be more clear, though I know it Can be really challenging. The Key is to understand what the count if formula does. It shows the amount of week since the outstanding money is superior to a funding in the past week. Let me give you an example. I found 1000 in week one, and my purchaser buys for 900. The count if will show 1, as one week ago, we had a funding of 1000, and the total amount of sales (900) is not higher than my funding of 1000. What it means to me is that when I will prepare my New funding for the next week, I'll know he has not finished using the funds I send from a week ago. If the week 2, I fund him one thousand again, but he buys nothing, total purchases will still be 900, and now the count if will show 2 weeks because he still hasn't overcome those one thousand from the previous week (so current week + previous week = 2 weeks). If in week 3 I fund him 1000, and he purchases for 200, now his total purchases will be 1100. It means he overcome the funding of 1000 from the first week, but not the 1000 from the second week (which he will when his total purchases will be over 2000). So the count if formula will show 2, because it has been two weeks (week 2 and three) that he has had funding he has not overcome. I Hope you understand the Idea, excuse me if it's tough to understand, I am not native english. What I now want is to break down this week count. If I start with the simplest case, the week one of my little example. The count if formula equals to one. At that at the same Time,the money outstanding equals to 100 (1000 funding - 900 purchases, he still has 100 available). What I want to achieve is the table to show that the 100 Comes from 1 week ago. Very simple in that case. In week 2, the count if equals to 2. At that at the same Time, the money outstanding equals to 1100 (2000 cumulative funding - 900 purchases, he now 1100 available). The table should show that amongst the 1100, 100 Comes from 2 weeks ago and 1000 Comes from 1 week ago. Now, if I take week 3, the count if formula equals to 2 again. At the same Time, the money outstanding equals to 1900, as I funded 1000 more for this week 2 (cumulative funding 3000 - cumulative sales 1100). What I now want to achieve is the table to show me that amont those 1900, there are 900 that Comes from 2 weeks ago, and 1000 that Comes from 1 week ago. And so on and so forth. I can't put figures for my example right now, I will add one tonight. I Hope that makes it clearer, I really appreciate the help, thank you so much. Don't hesitate if you have a question.
