How many days it takes for someone to reach a target?

[Gareth90]

Hi Guys,

I was wondering if I could find out how long it takes for someone in sales to reach a target of say £1000?

My guess is I have to add the sales figures cumulatively and then return the date of when the target was reached?

Not sure where to go after that.

Any help would be great!

 

[Gareth90]

Thank you for that Logit a question on the back of this:

With the sample data Jack Doyle reached the $1,000 target on the 04/02/18 which means he reached the target in 95 days - why is the table showing 613 days when the target was already reached?

 

[Logit]

.
The formulas in Post #10 run continually. As additional days are added AFTER the "goal reach date", the numbers continue to climb. The formula does not "know" to
stop counting number of days once the goal is met.

Here is a method of retrieving the date, when the goal is met, and then the formula 'stops calculating'.
The formulas are written for the existing data shown in the image. If you add more salespersons, you will need to adjust the ranges in the formulas to match.


[Table="width:, class:head"][tr=bgcolor:#888888][th] [/th][th]

A​

[/th][th]

B​

[/th][th]

C​

[/th][th]

D​

[/th][th]

E​

[/th][th]

F​

[/th][th]

G​

[/th][th]

H​

[/th][th]

I​

[/th][th]

J​

[/th][th]

K​

[/th][th]

L​

[/th][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

1​

[/td][td][/td][td]

​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

2​

[/td][td=bgcolor:#F2F2F2]

Posting Date​

[/td][td=bgcolor:#F2F2F2]

Name​

[/td][td=bgcolor:#F2F2F2]

Margin​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td=bgcolor:#F2F2F2]

Start Date​

[/td][td=bgcolor:#F2F2F2]

​

[/td][td]

Accumulated Sales​

[/td][td]

Date Goal Met​

[/td][td]

Days To Goal​

[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

3​

[/td][td]

12/28/2017​

[/td][td]

Jack Doyle​

[/td][td]

500.00​

[/td][td][/td][td][/td][td][/td][td][/td][td]Jack Doyle[/td][td]

12/28/2017​

[/td][td]

$1,900.00​

[/td][td]

4/2/2018​

[/td][td]

95​

[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

4​

[/td][td]

1/1/2018​

[/td][td]

Kieran Hall​

[/td][td]

100.00​

[/td][td][/td][td][/td][td][/td][td][/td][td]Kieran Hall[/td][td]

1/1/2017​

[/td][td]

$805.00​

[/td][td][/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

5​

[/td][td]

4/2/2018​

[/td][td]

Jack Doyle​

[/td][td]

800.00​

[/td][td][/td][td][/td][td][/td][td][/td][td]Hannah Bailey[/td][td]

3/30/2017​

[/td][td]

$2,460.00​

[/td][td]

1/4/2018​

[/td][td]

280​

[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

6​

[/td][td]

6/2/2018​

[/td][td]

Kieran Hall​

[/td][td]

250.00​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

7​

[/td][td]

3/30/2017​

[/td][td]

Hannah Bailey​

[/td][td]

190.00​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

8​

[/td][td]

1/4/2018​

[/td][td]

Hannah Bailey​

[/td][td]

1,500.00​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

9​

[/td][td]

5/4/2018​

[/td][td]

Jack Doyle​

[/td][td]

600.00​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

10​

[/td][td]

5/4/2018​

[/td][td]

Hannah Bailey​

[/td][td]

770.00​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

11​

[/td][td]

4/25/2018​

[/td][td]

Kieran Hall​

[/td][td]

455.00​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

12​

[/td][td][/td][td]

​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

13​

[/td][td]Formula in J3 and copied down : =SUMIF(B3:B100,H3,C3:C100)[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

14​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

15​

[/td][td]Formula in K3 : =IFERROR(INDEX($A$3:$A$11,MATCH(TRUE,SUMIF(OFFSET($B$3,,,ROW($B$3:$B$11)-2,1),"Jack Doyle",OFFSET($C$3,,,ROW($B$3:$B$11)-2,1))>=1000,0)),"")[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

16​

[/td][td][/td][td]First paste formula in cell. Edit Name in middle of formula to match name spelling under Start Date. Press Enter.[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

17​

[/td][td][/td][td]Highlight entire formula in the Formula Bar, then at the same time, press CTRL / SHIFT / ENTER.[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

18​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

19​

[/td][td]Formula in L3 and copied dorn : =IFERROR((K3-I3),"")[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

20​

[/td][td][/td][td]

​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[/table]

 

[Gareth90]

Thank you for this Logit.

I can't seem to get the formula in K3 to work - everytime I input it only the first date is picked up?

 

[Logit]

.
If you are referring to the example spreadsheet in the image, Jack Doyle's starting date was 12/28 and the second
date was 4/2.

He generated 500 on 12/28 and then 800 on 4/2. This places his total over the 1000 goal. So 4/12 is the date
showing in K3.

So yes, the first date when the salesperson has met or exceeded the 1000 goal is the date that will be displayed.