# Solved I want to know how to track the movements on excel?

June 27, 2018 at 10:22:23
Specs: Windows 7
 Hi,I currently analyze data detailing opportunities that have been categorization based on stages towards completion i.e. 1 to 5, with 1 being the initial stage and 5 the completion stage. I am trying to track how each opportunity moves along each stage and based on average stay time in each stage, the opportunity will be triggered if it stays longer than the average stay time. This is expected to allow me intervene early enough and determine if the opportunity is worth investing more resources or if we should let go.Can this be done?

See More: I want to know how to track the movements on excel?

✔ Best Answer
June 27, 2018 at 18:08:28
 Assuming my question in my previous response is correct, perhaps this will get us started.I am also going to try and address this issue:"However, my challenge is that the type of opportunity determines the total length of time it takes to close, so different opportunities on the same stage will have different average stay times on that particular stage"If we create a table of AST per Stage per Opportunity, then we can pull those values into the table and determine the Alert Schedule.Let's start with your table, except for the values in Column E (AST)``` A B C D E 1 Oppurtunity Date Created Estimated Close Date Stage Average Stay Time (days) 2 xtyu 7/1/2018 12/30/2018 1 3 gread 7/2/2018 12/31/2018 2 4 kiubg 7/3/2018 1/1/2019 3 5 ftres 7/4/2018 1/2/2019 4 6 fders 7/5/2018 1/3/2019 5 7 ouhgtb 7/6/2018 1/4/2019 1 8 tyedanhy 7/7/2018 1/5/2019 2 9 ftyyeioaj 7/8/2018 1/6/2019 3 10 loiyh 7/9/2018 1/7/2019 4 11 oiyt 7/10/2018 1/8/2019 5 ```Now let's build a table of AST per Stage per Opportunity:``` L M N O P Q 1 Opp/Stage 1 2 3 4 5 2 xtyu 32 53 67 91 108 3 read 38 56 62 99 99 4 kiubg 37 60 61 83 90 5 ftres 40 49 70 80 99 6 fders 35 48 77 81 110 7 ouhgtb 34 59 61 90 95 8 tyedanhy 37 56 76 91 94 9 ftyyeioaj 36 49 66 75 103 10 loiyh 39 56 72 83 96 11 oiyt 31 46 73 90 108 ```Next, put this formula in E2 and drag it down. This formula will pull the AST from the table into Column E based on the Opportunity and Stage.=VLOOKUP(A2,\$L\$1:\$Q\$11,D2+1,0)VLOOKUP the Opportunity (Column A) in Column L and use the Stage (Column D) as the col_num argument for the VLOOKUP table.Now, put this in F2. This will return Alert! whenever "Today's Date" is greater than the Create Date plus the AST for the corresponding Opportunity and Stage.=IF(TODAY()>B2+E2,"Alert!","")Is that close to what you are looking for?

#1
June 27, 2018 at 10:46:51
 It might be able to be done if we had more detail into how you have your data organized/laid out.For example, let's say you entered "Stage 1" in A1 and then entered the date/time that Stage 1 began in B1. Excel could calculate the difference between the date in B1 and an ever-changing "now" and alert you (somehow) when a certain threshold has been exceeded.Before we could offer any suggestions (of which there are many) we would need more detail related to your data layout and work process.If you are going to post any example data in this forum, please click on the following How-To link and read the instructions found on that page.

Report •

#2
June 27, 2018 at 11:20:00
 I followed the instructions, doesn't seem to be posting right. Basically, the data comes in 4 headings, Opportunity, Date Created, Estimated Close Date and the Stage. The stage is automatically updated in the data base if the opportunity moves to the next stage. The data is spooled everyday and the outcome has the headings highlighted earlier. From the spooled data, I want a report that highlights opportunities that have stayed longer than a specified average stay time, even if it is within the Estimated close date.We currently only track stale opportunity after the opportunity goes beyond the estimated close date and its still open. I am looking a better way to deploy resources, hence the need to intervene on time while the opportunity is still within the projected time frame of close, as I believe this is more efficient.message edited by Kings632

Report •

#3
June 27, 2018 at 11:30:42
 It does not appear that you followed the directions on how to post data in this forum. It appears that you did not use the pre tags nor did you include Column letters or Row numbers as shown in the example.Click on the pre icon above the reply box. You will see this (without the spaces):< PRE >< /PRE >If you place your data between the tags, the column layout will be maintained:< PRE > data goes here< /PRE >``` A B C 1 Data 1 Data 2 Data 3 2 Data 4 DAta 5 Data 6 ```In addition, the only thing that you posted was the data. You didn't explain to us what you want do with that data. e.g. If that is the input, what are you expecting the output to be?

Report •

Related Solutions

#4
June 27, 2018 at 11:59:14
 ``` A B C D 1 Oppurtunity Date Created Estimated Close Date Stage 2 xtyu 7/1/2018 12/30/2018 1 3 gread 7/2/2018 12/31/2018 2 4 kiubg 7/3/2018 1/1/2019 3 5 ftres 7/4/2018 1/2/2019 4 6 fders 7/5/2018 1/3/2019 5 7 ouhgtb 7/6/2018 1/4/2019 1 8 tyedanhy 7/7/2018 1/5/2019 2 9 ftyyeioaj 7/8/2018 1/6/2019 3 10 loiyh 7/9/2018 1/7/2019 4 11 oiyt 7/10/2018 1/8/2019 5 ```The data is spooled everyday and the outcome has the headings highlighted earlier. From the spooled data, I want a report that highlights opportunities that have stayed longer than a specified average stay time, even if it is within the Estimated close date.We currently only track stale opportunity after the opportunity goes beyond the estimated close date and its still open. I am looking a better way to deploy resources, hence the need to intervene on time while the opportunity is still within the projected time frame of close, as I believe this is more efficient.At this point I am not sure how I want the result.....but what I'm more interested in is if any opportunity fails the test (goes beyond the average stay time for each stage), these opportunities should be highlighted. I think we can take it from there! or I can always circle back to youmessage edited by Kings632

Report •

#5
June 27, 2018 at 12:16:06
 re: "I want a report that highlights opportunities that have stayed longer than a specified average stay time, even if it is within the Estimated close date."I see a column for Date Created and a column for Estimated Close Date. Don't we need a column for the "specified average stay time" if we are going to highlight the opportunity based on that value?Add a column for the "average stay time" and use that with either Conditional Formatting or an IF function or something that determines if that the time period has been exceeded.I don't actually know what you mean by "specified average stay time" but I'm pretty sure that it needs to be part of the data set if we are going to make decisions based on it. Add that column and we can work on the next steps.message edited by DerbyDad03

Report •

#6
June 27, 2018 at 13:44:15
 ``` A B C D E 1 Oppurtunity Date Created Estimated Close Date Stage Average Stay Time (days) 2 xtyu 7/1/2018 12/30/2018 1 36.4 3 gread 7/2/2018 12/31/2018 2 45.5 4 kiubg 7/3/2018 1/1/2019 3 60.67 5 ftres 7/4/2018 1/2/2019 4 91.00 6 fders 7/5/2018 1/3/2019 5 182.00 7 ouhgtb 7/6/2018 1/4/2019 1 36.40 8 tyedanhy 7/7/2018 1/5/2019 2 45.50 9 ftyyeioaj 7/8/2018 1/6/2019 3 60.67 10 loiyh 7/9/2018 1/7/2019 4 91.00 11 oiyt 7/10/2018 1/8/2019 5 182.00 ```You are spot on.....we actually do need data on the average stay time. However, my challenge is that the type of opportunity determines the total length of time it takes to close, so different opportunities on the same stage will have different average stay times on that particular stage (i.e. how long it takes to move to the next stage).While we are trying to conduct a survey with the entire business units to determine these averages, I have used the "IF Statement" to come up with plausible averages based on the total time required to close and the current stage of the opportunity.Does this help?

Report •

#7
June 27, 2018 at 14:05:34
 It helps get us started. Let me work on something and get back to you. It make take more than one iteration.I do have a question though.Am I correct in assuming that the AST is from the Date Created, not from the end of the previous Stage?In other words, if an Opportunity is in Stage 1 and it's 36.4 days since the Date Created, then an alert should occur. However if an Opportunity is in Stage 2, we wait until 45.5 days from the Date Created to produce the alert. If that's not correct, please explain how to read that table.

Report •

#8
June 27, 2018 at 18:08:28
✔ Best Answer
 Assuming my question in my previous response is correct, perhaps this will get us started.I am also going to try and address this issue:"However, my challenge is that the type of opportunity determines the total length of time it takes to close, so different opportunities on the same stage will have different average stay times on that particular stage"If we create a table of AST per Stage per Opportunity, then we can pull those values into the table and determine the Alert Schedule.Let's start with your table, except for the values in Column E (AST)``` A B C D E 1 Oppurtunity Date Created Estimated Close Date Stage Average Stay Time (days) 2 xtyu 7/1/2018 12/30/2018 1 3 gread 7/2/2018 12/31/2018 2 4 kiubg 7/3/2018 1/1/2019 3 5 ftres 7/4/2018 1/2/2019 4 6 fders 7/5/2018 1/3/2019 5 7 ouhgtb 7/6/2018 1/4/2019 1 8 tyedanhy 7/7/2018 1/5/2019 2 9 ftyyeioaj 7/8/2018 1/6/2019 3 10 loiyh 7/9/2018 1/7/2019 4 11 oiyt 7/10/2018 1/8/2019 5 ```Now let's build a table of AST per Stage per Opportunity:``` L M N O P Q 1 Opp/Stage 1 2 3 4 5 2 xtyu 32 53 67 91 108 3 read 38 56 62 99 99 4 kiubg 37 60 61 83 90 5 ftres 40 49 70 80 99 6 fders 35 48 77 81 110 7 ouhgtb 34 59 61 90 95 8 tyedanhy 37 56 76 91 94 9 ftyyeioaj 36 49 66 75 103 10 loiyh 39 56 72 83 96 11 oiyt 31 46 73 90 108 ```Next, put this formula in E2 and drag it down. This formula will pull the AST from the table into Column E based on the Opportunity and Stage.=VLOOKUP(A2,\$L\$1:\$Q\$11,D2+1,0)VLOOKUP the Opportunity (Column A) in Column L and use the Stage (Column D) as the col_num argument for the VLOOKUP table.Now, put this in F2. This will return Alert! whenever "Today's Date" is greater than the Create Date plus the AST for the corresponding Opportunity and Stage.=IF(TODAY()>B2+E2,"Alert!","")Is that close to what you are looking for?

Report •

#9
June 28, 2018 at 10:16:38
 Hi,Thank you very much!I will look into the steps you provided, before the end of the day and provide feedback. Its been a very busy day for me, I do apologize for not getting back to you earlier!

Report •