Variable Ranges

[BCS2]

I have a sheet that is sorted alphabetically by Salesperson. I am calculating this data as a whole but also want to break it down to get some data based on the salesperson.

What I need is a formula that will identify a range based on the name of the salesperson and only do calculations within that range. Sample data below.

Experiment.xls
	N	O	P	Q
3		RequestedTurnTime	ActualTurnTime
4	Amburgey	24:28	18:13
5	Amburgey	30:40	7:35
6	Amburgey	24:20	17:00
7	Amburgey	24:20	18:00
8	Anliker	21:21	4:26
9	Anliker	25:46	19:06
10	Anliker	6:40	0:15
11	Anliker	0:10	0:10
1-26 to 1-30

What I am trying to get is the average requested turn time, average actual turn time and # of on-times for each salesperson.

Keep in mind the ranges for each salesperson will change by week. They might have 5 requests this week and 10 the next, but they will always be together in the worksheet because they are sorted.

Any suggestions?

 

[parry]

Hi, you could use the Subtotal formula (eg =SUBTOTAL(1,O2:O11) and then just use an autofilter to filter the name you want to see.

 

[Zack Barresse]

how about a pivot table?

 

[Aladin Akyurek]

Given that the sample data is N3:P11 on sheet 1-26 to 1-30, sorted on the SalesPerson column:

Summary sheet...

Book4
	A	B	C	D	E	F
1	Start	End	Salesperson	AvgRTT	AvgATT	#On-Time
2	1	4	Amburgey	1.08125	0.633333	4
3	5	8	Anliker	0.561979	0.249479	4
4
Summary

Formulas...

A2:

=MATCH($C2,'1-26 to 1-30 '!$N$4:$N$11,0)

B2:

=MATCH($C2,'1-26 to 1-30 '!$N$4:$N$11)

D2, copied to E2, then down:

=AVERAGE(INDEX('1-26 to 1-30 '!O$4:O$11,$A2):INDEX('1-26 to 1-30 '!O$4:O$11,$B2))

F2:

=SUMPRODUCT(--(OFFSET('1-26 to 1-30 '!$O$4,A2-1,0,B2-A2+1,1)>=OFFSET('1-26 to 1-30 '!$P$4,A2-1,0,B2-A2+1,1)))

 

[BCS2]

Aladin:

Thanks for the code!

I have them all working accept for the SUMPRODUCT formula. I got kind of lost when trying to logic my way through it. I am not familiar with the OFFSET function. Can you explain what's going on here? I looked at the help file in Excel but didn't get much out of it.

I have substituted the corresponding cell values in the formula that relate to my sheet and I get a #VALUE! error.

Thanks

 

[Aladin Akyurek]

...I have substituted the corresponding cell values in the formula that relate to my sheet and I get a #VALUE! error.
...

Everywhere or just in a few cells?