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:
 

Some videos you may like

Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.

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,201
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,201
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?
 

Watch MrExcel Video

Forum statistics

Threads
1,122,508
Messages
5,596,555
Members
414,077
Latest member
ammylar5

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