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

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="align: center"]Customer[/TD]
[TD="align: right"]1/31/2013[/TD]
[TD="align: right"]2/28/2013[/TD]
[TD="align: right"]3/31/2013[/TD]
[TD="align: right"]4/30/2013[/TD]
[TD="align: right"]5/31/2013[/TD]
[TD="align: right"]6/30/2013[/TD]
[TD="align: right"]7/31/2013[/TD]
[TD="align: right"]8/31/2013[/TD]
[TD="align: right"]9/30/2013[/TD]
[TD="align: right"]10/31/2013[/TD]
[TD="align: right"]11/30/2013[/TD]
[TD="align: right"]12/31/2013[/TD]
[TD="align: right"]1/31/2014[/TD]

[TD="align: center"]2[/TD]
[TD="align: center"]Q[/TD]
[TD="align: right"]97[/TD]
[TD="align: right"]98[/TD]
[TD="align: right"]41[/TD]
[TD="align: right"]12[/TD]
[TD="align: right"]19[/TD]
[TD="align: right"]78[/TD]
[TD="align: right"]79[/TD]
[TD="align: right"]75[/TD]
[TD="align: right"]88[/TD]
[TD="align: right"]94[/TD]
[TD="align: right"]65[/TD]
[TD="align: right"]53[/TD]
[TD="align: right"]25[/TD]

[TD="align: center"]3[/TD]
[TD="align: center"]W[/TD]
[TD="align: right"]66[/TD]
[TD="align: right"]31[/TD]
[TD="align: right"]27[/TD]
[TD="align: right"]73[/TD]
[TD="align: right"]99[/TD]
[TD="align: right"]51[/TD]
[TD="align: right"]24[/TD]
[TD="align: right"]58[/TD]
[TD="align: right"]57[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]39[/TD]
[TD="align: right"]25[/TD]
[TD="align: right"]95[/TD]

[TD="align: center"]4[/TD]
[TD="align: center"]E[/TD]
[TD="align: right"]57[/TD]
[TD="align: right"]77[/TD]
[TD="align: right"]48[/TD]
[TD="align: right"]38[/TD]
[TD="align: right"]39[/TD]
[TD="align: right"]37[/TD]
[TD="align: right"]55[/TD]
[TD="align: right"]57[/TD]
[TD="align: right"]37[/TD]
[TD="align: right"]27[/TD]
[TD="align: right"]76[/TD]
[TD="align: right"]70[/TD]
[TD="align: right"]98[/TD]

[TD="align: center"]5[/TD]
[TD="align: center"]R[/TD]
[TD="align: right"]58[/TD]
[TD="align: right"]44[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]14[/TD]
[TD="align: right"]95[/TD]
[TD="align: right"]29[/TD]
[TD="align: right"]51[/TD]
[TD="align: right"]70[/TD]
[TD="align: right"]12[/TD]
[TD="align: right"]58[/TD]
[TD="align: right"]37[/TD]
[TD="align: right"]19[/TD]
[TD="align: right"]26[/TD]

[TD="align: center"]6[/TD]
[TD="align: center"]T[/TD]
[TD="align: right"]66[/TD]
[TD="align: right"]31[/TD]
[TD="align: right"]23[/TD]
[TD="align: right"]22[/TD]
[TD="align: right"]82[/TD]
[TD="align: right"]44[/TD]
[TD="align: right"]57[/TD]
[TD="align: right"]40[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]88[/TD]
[TD="align: right"]13[/TD]
[TD="align: right"]82[/TD]
[TD="align: right"]4[/TD]

[TD="align: center"]7[/TD]
[TD="align: center"]Y[/TD]
[TD="align: right"]75[/TD]
[TD="align: right"]80[/TD]
[TD="align: right"]68[/TD]
[TD="align: right"]79[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]57[/TD]
[TD="align: right"]65[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]73[/TD]
[TD="align: right"]28[/TD]
[TD="align: right"]17[/TD]
[TD="align: right"]93[/TD]

[TD="align: center"]8[/TD]
[TD="align: center"]U[/TD]
[TD="align: right"]53[/TD]
[TD="align: right"]61[/TD]
[TD="align: right"]43[/TD]
[TD="align: right"]25[/TD]
[TD="align: right"]81[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]18[/TD]
[TD="align: right"]25[/TD]
[TD="align: right"]93[/TD]
[TD="align: right"]12[/TD]
[TD="align: right"]54[/TD]
[TD="align: right"]22[/TD]
[TD="align: right"]87[/TD]

[TD="align: center"]9[/TD]
[TD="align: center"]I[/TD]
[TD="align: right"]69[/TD]
[TD="align: right"]52[/TD]
[TD="align: right"]59[/TD]
[TD="align: right"]59[/TD]
[TD="align: right"]53[/TD]
[TD="align: right"]96[/TD]
[TD="align: right"]86[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]39[/TD]
[TD="align: right"]57[/TD]
[TD="align: right"]86[/TD]
[TD="align: right"]1[/TD]

[TD="align: center"]10[/TD]
[TD="align: center"]O[/TD]
[TD="align: right"]22[/TD]
[TD="align: right"]27[/TD]
[TD="align: right"]38[/TD]
[TD="align: right"]85[/TD]
[TD="align: right"]40[/TD]
[TD="align: right"]33[/TD]
[TD="align: right"]46[/TD]
[TD="align: right"]92[/TD]
[TD="align: right"]46[/TD]
[TD="align: right"]47[/TD]
[TD="align: right"]53[/TD]
[TD="align: right"]38[/TD]
[TD="align: right"]79[/TD]

[TD="align: center"]11[/TD]
[TD="align: center"]P[/TD]
[TD="align: right"]21[/TD]
[TD="align: right"]19[/TD]
[TD="align: right"]37[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]50[/TD]
[TD="align: right"]28[/TD]
[TD="align: right"]29[/TD]
[TD="align: right"]37[/TD]
[TD="align: right"]90[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]82[/TD]
[TD="align: right"]33[/TD]
[TD="align: right"]1[/TD]

[TD="align: center"]12[/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]13[/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]14[/TD]
[TD="align: center"]Q[/TD]
[TD="align: right"]700[/TD]
[TD="align: right"]11/30/2013[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]15[/TD]
[TD="align: center"]W[/TD]
[TD="align: right"]900[/TD]
[TD="align: right"]4/30/2014[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]16[/TD]
[TD="align: center"]E[/TD]
[TD="align: right"]200[/TD]
[TD="align: right"]4/30/2013[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

Sheet10​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]C14[/TH]
[TD="align: left"]{=INDEX($B$1:$BP$1,MATCH(TRUE,MMULT($B2:$BP2,--(TRANSPOSE(COLUMN($B2:$BP2))<=COLUMN($B2:$BP2)))>=B14,0))}[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself[/TD]
[/TR]
</tbody>[/TABLE]

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.