Variable Ranges

BCS2

Board Regular
Joined
Dec 8, 2003
Messages
67
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
NOPQ
3RequestedTurnTimeActualTurnTime
4Amburgey24:2818:13
5Amburgey30:407:35
6Amburgey24:2017:00
7Amburgey24:2018:00
8Anliker21:214:26
9Anliker25:4619:06
10Anliker6:400:15
11Anliker0:100: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?

:rolleyes:
 

Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney

parry

MrExcel MVP
Joined
Aug 20, 2002
Messages
3,355
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.
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,210
Given that the sample data is N3:P11 on sheet 1-26 to 1-30, sorted on the SalesPerson column:

Summary sheet...
Book4
ABCDEF
1StartEndSalespersonAvgRTTAvgATT#On-Time
214Amburgey1.081250.6333334
358Anliker0.5619790.2494794
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

Board Regular
Joined
Dec 8, 2003
Messages
67
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

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,210
BCS2 said:
...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?
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,167,953
Messages
5,856,481
Members
431,817
Latest member
db74

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top