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?

Reply ↓  Report •

✔ 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?

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code



#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.

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code


Reply ↓  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


Reply ↓  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?

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code


Reply ↓  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 you

message edited by Kings632


Reply ↓  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.

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code

message edited by DerbyDad03


Reply ↓  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?


Reply ↓  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.

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code


Reply ↓  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?

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code


Reply ↓  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!


Reply ↓  Report •

Ask Question