How can I calculate a percentage of sales, and then output how many months it took to hit that?

[IloveExcel12]

Hey guys, hopefully someone can help before I get back into work tomorrow...

I have a list of ~10 customers, each of which have a target revenue. For all 10 customers I have sales data going from 2013 to date (by month), along with month-to-date sales data for each month. I need to calculate, based off the target revenue and month-to-date sales, how long it took each customer to hit a certain percentage (10%, 20%, 30%...100%) of their target revenue.

I pretty much need to calculate when then the customer hit the number, and then maybe a count function or something to get how many months it took for each percentage. But there's nothing that would act as a "when" function that I know of.

Example: When "customer x" hit 10% of revenue, count how many months from their start to when they hit it.

With only 10 customers it'd be easy to eyeball, but the plan is to blow it up into a couple hundred.

 

[sheetspread]

This tells you when a goal was reached, you can change to whatever % and add a datedif() to count months:

Excel 2010
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	Customer	1/31/2013	2/28/2013	3/31/2013	4/30/2013	5/31/2013	6/30/2013	7/31/2013	8/31/2013	9/30/2013	10/31/2013	11/30/2013	12/31/2013	1/31/2014
2	Q	97	98	41	12	19	78	79	75	88	94	65	53	25
3	W	66	31	27	73	99	51	24	58	57	7	39	25	95
4	E	57	77	48	38	39	37	55	57	37	27	76	70	98
5	R	58	44	1	14	95	29	51	70	12	58	37	19	26
6	T	66	31	23	22	82	44	57	40	7	88	13	82	4
7	Y	75	80	68	79	20	1	57	65	8	73	28	17	93
8	U	53	61	43	25	81	20	18	25	93	12	54	22	87
9	I	69	52	59	59	53	96	86	20	100	39	57	86	1
10	O	22	27	38	85	40	33	46	92	46	47	53	38	79
11	P	21	19	37	5	50	28	29	37	90	3	82	33	1
12
13
14	Q	700	11/30/2013
15	W	900	4/30/2014
16	E	200	4/30/2013
Sheet10

Cell Formulas
Range	Formula
C14	{=INDEX($B$1:$BP$1,MATCH(TRUE,MMULT($B2:$BP2,--(TRANSPOSE(COLUMN($B2:$BP2))<=COLUMN($B2:$BP2)))>=B14,0))}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[IloveExcel12]

This tells you when a goal was reached, you can change to whatever % and add a datedif() to count months:

Excel 2010

	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	Customer	1/31/2013	2/28/2013	3/31/2013	4/30/2013	5/31/2013	6/30/2013	7/31/2013	8/31/2013	9/30/2013	10/31/2013	11/30/2013	12/31/2013	1/31/2014
2	Q	97	98	41	12	19	78	79	75	88	94	65	53	25
3	W	66	31	27	73	99	51	24	58	57	7	39	25	95
4	E	57	77	48	38	39	37	55	57	37	27	76	70	98
5	R	58	44	1	14	95	29	51	70	12	58	37	19	26
6	T	66	31	23	22	82	44	57	40	7	88	13	82	4
7	Y	75	80	68	79	20	1	57	65	8	73	28	17	93
8	U	53	61	43	25	81	20	18	25	93	12	54	22	87
9	I	69	52	59	59	53	96	86	20	100	39	57	86	1
10	O	22	27	38	85	40	33	46	92	46	47	53	38	79
11	P	21	19	37	5	50	28	29	37	90	3	82	33	1
12
13
14	Q	700	11/30/2013
15	W	900	4/30/2014
16	E	200	4/30/2013

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet10​

Array Formulas Cell Formula C14 {=INDEX($B$1:$BP$1,MATCH(TRUE,MMULT($B2:$BP2,--(TRANSPOSE(COLUMN($B2:$BP2))<=COLUMN($B2:$BP2)))>=B14,0))} <thead> </thead><tbody> </tbody> Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>

I have no idea what's even going in there lol, I'm going to try and play around with it a bit and see if I can figure it out. Thanks!

 

[sheetspread]

Actually you don't need index, the # of months is already given by:

=MATCH(TRUE,MMULT($B2:$BP2,--(TRANSPOSE(COLUMN($B2:$BP2))<=COLUMN($B2:$BP2)))>=B14,0) ctrl-shift-enter

which just searches an array of cumulative sums for the 700, 900, etc.