I have the raw data below where I would like to 'average' for September, the
combined total in the last three columns for any particular agent.
Is vLookup the correct funtion for this?

Agent Submit Time Attitude Aptitude Experience
Zoumadakis, Steven 07/29/08 09:12 AM 5 5 5
Zoumadakis, Steven 08/05/08 05:07 PM 5 5 3
Zoumadakis, Steven 08/07/08 09:06 AM 5 5 5
Zoumadakis, Steven 08/07/08 03:24 PM 5 4 5
Zoumadakis, Steven 08/11/08 08:08 AM 4 4 4
Zoumadakis, Steven 08/11/08 03:13 PM 5 5 5
Zoumadakis, Steven 08/12/08 02:28 PM 5 5 5
Zoumadakis, Steven 08/13/08 03:20 PM 5 5 5
Zoumadakis, Steven 08/14/08 03:13 PM 5 5 5
Zoumadakis, Steven 08/15/08 01:13 PM 5 5 5
Zoumadakis, Steven 08/19/08 07:41 AM 4 3 4
Zoumadakis, Steven 08/21/08 01:50 PM 5 5 5
Vasquez, Richard 08/11/08 09:23 AM 5 5 5
Vasquez, Richard 08/13/08 01:21 PM 4 3 4
Vasquez, Richard 08/20/08 06:56 AM 2 1 2
Vasquez, Richard 08/21/08 08:16 AM 4 3 4
Vasquez, Richard 08/25/08 09:29 AM 4 3 3
Vasquez, Richard 09/10/08 01:05 PM 5 4 5
Vasquez, Richard 09/15/08 09:04 AM 4 4 4

Try this array formula** :

=AVERAGE(IF((A2:A20=H2)*(MONTH(B2:B20)=9),C2:E20))

Whe

A2:A20 are names
H2 is the name of interest
B2:B20 are dates
9 is the month number of interest (9 = September)
C2:E20 are the values to average

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

--
Biff
Microsoft Excel MVP


"Jodi Macy" <Jodi wrote in message
...
I have the raw data below where I would like to 'average' for September,
the
combined total in the last three columns for any particular agent.
Is vLookup the correct funtion for this?

Agent Submit Time Attitude Aptitude Experience
Zoumadakis, Steven 07/29/08 09:12 AM 5 5 5
Zoumadakis, Steven 08/05/08 05:07 PM 5 5 3
Zoumadakis, Steven 08/07/08 09:06 AM 5 5 5
Zoumadakis, Steven 08/07/08 03:24 PM 5 4 5
Zoumadakis, Steven 08/11/08 08:08 AM 4 4 4
Zoumadakis, Steven 08/11/08 03:13 PM 5 5 5
Zoumadakis, Steven 08/12/08 02:28 PM 5 5 5
Zoumadakis, Steven 08/13/08 03:20 PM 5 5 5
Zoumadakis, Steven 08/14/08 03:13 PM 5 5 5
Zoumadakis, Steven 08/15/08 01:13 PM 5 5 5
Zoumadakis, Steven 08/19/08 07:41 AM 4 3 4
Zoumadakis, Steven 08/21/08 01:50 PM 5 5 5
Vasquez, Richard 08/11/08 09:23 AM 5 5 5
Vasquez, Richard 08/13/08 01:21 PM 4 3 4
Vasquez, Richard 08/20/08 06:56 AM 2 1 2
Vasquez, Richard 08/21/08 08:16 AM 4 3 4
Vasquez, Richard 08/25/08 09:29 AM 4 3 3
Vasquez, Richard 09/10/08 01:05 PM 5 4 5
Vasquez, Richard 09/15/08 09:04 AM 4 4 4

I don't understand why H2 has relevance....
Also, how do I involk the "CTRL,SHIFT,ENTER (not just ENTER)" command? I've
never heard of this.
Jodi



"T. Valko" wrote:

Try this array formula** :

=AVERAGE(IF((A2:A20=H2)*(MONTH(B2:B20)=9),C2:E20))

Whe

A2:A20 are names
H2 is the name of interest
B2:B20 are dates
9 is the month number of interest (9 = September)
C2:E20 are the values to average

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

--
Biff
Microsoft Excel MVP


"Jodi Macy" <Jodi wrote in message
...
I have the raw data below where I would like to 'average' for September,
the
combined total in the last three columns for any particular agent.
Is vLookup the correct funtion for this?

Agent Submit Time Attitude Aptitude Experience
Zoumadakis, Steven 07/29/08 09:12 AM 5 5 5
Zoumadakis, Steven 08/05/08 05:07 PM 5 5 3
Zoumadakis, Steven 08/07/08 09:06 AM 5 5 5
Zoumadakis, Steven 08/07/08 03:24 PM 5 4 5
Zoumadakis, Steven 08/11/08 08:08 AM 4 4 4
Zoumadakis, Steven 08/11/08 03:13 PM 5 5 5
Zoumadakis, Steven 08/12/08 02:28 PM 5 5 5
Zoumadakis, Steven 08/13/08 03:20 PM 5 5 5
Zoumadakis, Steven 08/14/08 03:13 PM 5 5 5
Zoumadakis, Steven 08/15/08 01:13 PM 5 5 5
Zoumadakis, Steven 08/19/08 07:41 AM 4 3 4
Zoumadakis, Steven 08/21/08 01:50 PM 5 5 5
Vasquez, Richard 08/11/08 09:23 AM 5 5 5
Vasquez, Richard 08/13/08 01:21 PM 4 3 4
Vasquez, Richard 08/20/08 06:56 AM 2 1 2
Vasquez, Richard 08/21/08 08:16 AM 4 3 4
Vasquez, Richard 08/25/08 09:29 AM 4 3 3
Vasquez, Richard 09/10/08 01:05 PM 5 4 5
Vasquez, Richard 09/15/08 09:04 AM 4 4 4

I don't understand why H2 has relevance....

H2 is the name of the agent that you want the average for.

how do I involk the "CTRL,SHIFT,ENTER (not just ENTER)" command?

This is an array formula. Array formulas are entered differently than a
regular formula. After you type in a regular formula you
hit the ENTER key. With an array formula you *must* use a combination of
keys. Those keys are the CTRL key, the SHIFT key and the ENTER key. That is,
hold down both the CTRL key and the SHIFT key then hit the ENTER key.

When done properly Excel will enclose the formulas in the brackets. You
can't just type these brackets in, you *must* use the key combo. Also,
anytime you edit an array formula it *must* be re-entered as an array using
the key combo.


--
Biff
Microsoft Excel MVP


"Jodi Macy" wrote in message
...
I don't understand why H2 has relevance....
Also, how do I involk the "CTRL,SHIFT,ENTER (not just ENTER)" command?
I've
never heard of this.
Jodi



"T. Valko" wrote:

Try this array formula** :

=AVERAGE(IF((A2:A20=H2)*(MONTH(B2:B20)=9),C2:E20))

Whe

A2:A20 are names
H2 is the name of interest
B2:B20 are dates
9 is the month number of interest (9 = September)
C2:E20 are the values to average

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

--
Biff
Microsoft Excel MVP


"Jodi Macy" <Jodi wrote in message
...
I have the raw data below where I would like to 'average' for September,
the
combined total in the last three columns for any particular agent.
Is vLookup the correct funtion for this?

Agent Submit Time Attitude Aptitude Experience
Zoumadakis, Steven 07/29/08 09:12 AM 5 5 5
Zoumadakis, Steven 08/05/08 05:07 PM 5 5 3
Zoumadakis, Steven 08/07/08 09:06 AM 5 5 5
Zoumadakis, Steven 08/07/08 03:24 PM 5 4 5
Zoumadakis, Steven 08/11/08 08:08 AM 4 4 4
Zoumadakis, Steven 08/11/08 03:13 PM 5 5 5
Zoumadakis, Steven 08/12/08 02:28 PM 5 5 5
Zoumadakis, Steven 08/13/08 03:20 PM 5 5 5
Zoumadakis, Steven 08/14/08 03:13 PM 5 5 5
Zoumadakis, Steven 08/15/08 01:13 PM 5 5 5
Zoumadakis, Steven 08/19/08 07:41 AM 4 3 4
Zoumadakis, Steven 08/21/08 01:50 PM 5 5 5
Vasquez, Richard 08/11/08 09:23 AM 5 5 5
Vasquez, Richard 08/13/08 01:21 PM 4 3 4
Vasquez, Richard 08/20/08 06:56 AM 2 1 2
Vasquez, Richard 08/21/08 08:16 AM 4 3 4
Vasquez, Richard 08/25/08 09:29 AM 4 3 3
Vasquez, Richard 09/10/08 01:05 PM 5 4 5
Vasquez, Richard 09/15/08 09:04 AM 4 4 4

Nevermind, I figured it out and I LOVE HOW THIS WORKS. I've been struggling
with this for awhile now. THANK YOU, thank-you, thank-you...
Jodi



"Jodi Macy" wrote:

I don't understand why H2 has relevance....
Also, how do I involk the "CTRL,SHIFT,ENTER (not just ENTER)" command? I've
never heard of this.
Jodi



"T. Valko" wrote:

Try this array formula** :

=AVERAGE(IF((A2:A20=H2)*(MONTH(B2:B20)=9),C2:E20))

Whe

A2:A20 are names
H2 is the name of interest
B2:B20 are dates
9 is the month number of interest (9 = September)
C2:E20 are the values to average

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

--
Biff
Microsoft Excel MVP


"Jodi Macy" <Jodi wrote in message
...
I have the raw data below where I would like to 'average' for September,
the
combined total in the last three columns for any particular agent.
Is vLookup the correct funtion for this?

Agent Submit Time Attitude Aptitude Experience
Zoumadakis, Steven 07/29/08 09:12 AM 5 5 5
Zoumadakis, Steven 08/05/08 05:07 PM 5 5 3
Zoumadakis, Steven 08/07/08 09:06 AM 5 5 5
Zoumadakis, Steven 08/07/08 03:24 PM 5 4 5
Zoumadakis, Steven 08/11/08 08:08 AM 4 4 4
Zoumadakis, Steven 08/11/08 03:13 PM 5 5 5
Zoumadakis, Steven 08/12/08 02:28 PM 5 5 5
Zoumadakis, Steven 08/13/08 03:20 PM 5 5 5
Zoumadakis, Steven 08/14/08 03:13 PM 5 5 5
Zoumadakis, Steven 08/15/08 01:13 PM 5 5 5
Zoumadakis, Steven 08/19/08 07:41 AM 4 3 4
Zoumadakis, Steven 08/21/08 01:50 PM 5 5 5
Vasquez, Richard 08/11/08 09:23 AM 5 5 5
Vasquez, Richard 08/13/08 01:21 PM 4 3 4
Vasquez, Richard 08/20/08 06:56 AM 2 1 2
Vasquez, Richard 08/21/08 08:16 AM 4 3 4
Vasquez, Richard 08/25/08 09:29 AM 4 3 3
Vasquez, Richard 09/10/08 01:05 PM 5 4 5
Vasquez, Richard 09/15/08 09:04 AM 4 4 4

Good deal. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"Jodi Macy" wrote in message
...
Nevermind, I figured it out and I LOVE HOW THIS WORKS. I've been
struggling
with this for awhile now. THANK YOU, thank-you, thank-you...
Jodi



"Jodi Macy" wrote:

I don't understand why H2 has relevance....
Also, how do I involk the "CTRL,SHIFT,ENTER (not just ENTER)" command?
I've
never heard of this.
Jodi



"T. Valko" wrote:

Try this array formula** :

=AVERAGE(IF((A2:A20=H2)*(MONTH(B2:B20)=9),C2:E20))

Whe

A2:A20 are names
H2 is the name of interest
B2:B20 are dates
9 is the month number of interest (9 = September)
C2:E20 are the values to average

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

--
Biff
Microsoft Excel MVP


"Jodi Macy" <Jodi wrote in message
...
I have the raw data below where I would like to 'average' for
September,
the
combined total in the last three columns for any particular agent.
Is vLookup the correct funtion for this?

Agent Submit Time Attitude Aptitude Experience
Zoumadakis, Steven 07/29/08 09:12 AM 5 5 5
Zoumadakis, Steven 08/05/08 05:07 PM 5 5 3
Zoumadakis, Steven 08/07/08 09:06 AM 5 5 5
Zoumadakis, Steven 08/07/08 03:24 PM 5 4 5
Zoumadakis, Steven 08/11/08 08:08 AM 4 4 4
Zoumadakis, Steven 08/11/08 03:13 PM 5 5 5
Zoumadakis, Steven 08/12/08 02:28 PM 5 5 5
Zoumadakis, Steven 08/13/08 03:20 PM 5 5 5
Zoumadakis, Steven 08/14/08 03:13 PM 5 5 5
Zoumadakis, Steven 08/15/08 01:13 PM 5 5 5
Zoumadakis, Steven 08/19/08 07:41 AM 4 3 4
Zoumadakis, Steven 08/21/08 01:50 PM 5 5 5
Vasquez, Richard 08/11/08 09:23 AM 5 5 5
Vasquez, Richard 08/13/08 01:21 PM 4 3 4
Vasquez, Richard 08/20/08 06:56 AM 2 1 2
Vasquez, Richard 08/21/08 08:16 AM 4 3 4
Vasquez, Richard 08/25/08 09:29 AM 4 3 3
Vasquez, Richard 09/10/08 01:05 PM 5 4 5
Vasquez, Richard 09/15/08 09:04 AM 4 4 4

How would I code this to get rid of the #DIV/0! and show a dash (-) ?
I've tried a few things with no luck....
Jodi

{=AVERAGE(IF((Agents=I2)*(MONTH(SubmitDate)=7),Dat aRange,"-"))}




"T. Valko" wrote:

Good deal. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"Jodi Macy" wrote in message
...
Nevermind, I figured it out and I LOVE HOW THIS WORKS. I've been
struggling
with this for awhile now. THANK YOU, thank-you, thank-you...
Jodi



"Jodi Macy" wrote:

I don't understand why H2 has relevance....
Also, how do I involk the "CTRL,SHIFT,ENTER (not just ENTER)" command?
I've
never heard of this.
Jodi



"T. Valko" wrote:

Try this array formula** :

=AVERAGE(IF((A2:A20=H2)*(MONTH(B2:B20)=9),C2:E20))

Whe

A2:A20 are names
H2 is the name of interest
B2:B20 are dates
9 is the month number of interest (9 = September)
C2:E20 are the values to average

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

--
Biff
Microsoft Excel MVP


"Jodi Macy" <Jodi wrote in message
...
I have the raw data below where I would like to 'average' for
September,
the
combined total in the last three columns for any particular agent.
Is vLookup the correct funtion for this?

Agent Submit Time Attitude Aptitude Experience
Zoumadakis, Steven 07/29/08 09:12 AM 5 5 5
Zoumadakis, Steven 08/05/08 05:07 PM 5 5 3
Zoumadakis, Steven 08/07/08 09:06 AM 5 5 5
Zoumadakis, Steven 08/07/08 03:24 PM 5 4 5
Zoumadakis, Steven 08/11/08 08:08 AM 4 4 4
Zoumadakis, Steven 08/11/08 03:13 PM 5 5 5
Zoumadakis, Steven 08/12/08 02:28 PM 5 5 5
Zoumadakis, Steven 08/13/08 03:20 PM 5 5 5
Zoumadakis, Steven 08/14/08 03:13 PM 5 5 5
Zoumadakis, Steven 08/15/08 01:13 PM 5 5 5
Zoumadakis, Steven 08/19/08 07:41 AM 4 3 4
Zoumadakis, Steven 08/21/08 01:50 PM 5 5 5
Vasquez, Richard 08/11/08 09:23 AM 5 5 5
Vasquez, Richard 08/13/08 01:21 PM 4 3 4
Vasquez, Richard 08/20/08 06:56 AM 2 1 2
Vasquez, Richard 08/21/08 08:16 AM 4 3 4
Vasquez, Richard 08/25/08 09:29 AM 4 3 3
Vasquez, Richard 09/10/08 01:05 PM 5 4 5
Vasquez, Richard 09/15/08 09:04 AM 4 4 4

Depending on what version of Excel you're using...

Excel 2007 only:

=IFERROR(AVERAGE(IF((Agents=I2)*(MONTH(SubmitDate) =7),DataRange)),"-")

All versions of Excel:

=IF(ISERROR(AVERAGE(IF((agents=I2)*(MONTH(submitda te)=7),datarange))),"-",AVERAGE(IF((agents=I2)*(MONTH(submitdate)=7),dat arange)))

Both are array entered!

--
Biff
Microsoft Excel MVP


"Jodi Macy" wrote in message
...
How would I code this to get rid of the #DIV/0! and show a dash (-) ?
I've tried a few things with no luck....
Jodi

{=AVERAGE(IF((Agents=I2)*(MONTH(SubmitDate)=7),Dat aRange,"-"))}




"T. Valko" wrote:

Good deal. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"Jodi Macy" wrote in message
...
Nevermind, I figured it out and I LOVE HOW THIS WORKS. I've been
struggling
with this for awhile now. THANK YOU, thank-you, thank-you...
Jodi



"Jodi Macy" wrote:

I don't understand why H2 has relevance....
Also, how do I involk the "CTRL,SHIFT,ENTER (not just ENTER)" command?
I've
never heard of this.
Jodi



"T. Valko" wrote:

Try this array formula** :

=AVERAGE(IF((A2:A20=H2)*(MONTH(B2:B20)=9),C2:E20))

Whe

A2:A20 are names
H2 is the name of interest
B2:B20 are dates
9 is the month number of interest (9 = September)
C2:E20 are the values to average

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

--
Biff
Microsoft Excel MVP


"Jodi Macy" <Jodi wrote in message
...
I have the raw data below where I would like to 'average' for
September,
the
combined total in the last three columns for any particular agent.
Is vLookup the correct funtion for this?

Agent Submit Time Attitude Aptitude Experience
Zoumadakis, Steven 07/29/08 09:12 AM 5 5 5
Zoumadakis, Steven 08/05/08 05:07 PM 5 5 3
Zoumadakis, Steven 08/07/08 09:06 AM 5 5 5
Zoumadakis, Steven 08/07/08 03:24 PM 5 4 5
Zoumadakis, Steven 08/11/08 08:08 AM 4 4 4
Zoumadakis, Steven 08/11/08 03:13 PM 5 5 5
Zoumadakis, Steven 08/12/08 02:28 PM 5 5 5
Zoumadakis, Steven 08/13/08 03:20 PM 5 5 5
Zoumadakis, Steven 08/14/08 03:13 PM 5 5 5
Zoumadakis, Steven 08/15/08 01:13 PM 5 5 5
Zoumadakis, Steven 08/19/08 07:41 AM 4 3 4
Zoumadakis, Steven 08/21/08 01:50 PM 5 5 5
Vasquez, Richard 08/11/08 09:23 AM 5 5 5
Vasquez, Richard 08/13/08 01:21 PM 4 3 4
Vasquez, Richard 08/20/08 06:56 AM 2 1 2
Vasquez, Richard 08/21/08 08:16 AM 4 3 4
Vasquez, Richard 08/25/08 09:29 AM 4 3 3
Vasquez, Richard 09/10/08 01:05 PM 5 4 5
Vasquez, Richard 09/15/08 09:04 AM 4 4 4

The second one worked great with my 2003 version.
Thanks again!
One more question, is this the correct way to obtain a Ytd number?
=IF(ISERROR(AVERAGE(IF((Agents=L5)*(MONTH(SubmitDa te)),DataRange))),"-",AVERAGE(IF((Agents=L5)*(MONTH(SubmitDate)),DataR ange)))

And would this be the way for a particular quarter?
=IF(ISERROR(AVERAGE(IF((Agents=L5)*(MONTH(SubmitDa te)<"7,9"),DataRange))),"-",AVERAGE(IF((Agents=L5)*(MONTH(SubmitDate)<"7,9" ),DataRange)))




"T. Valko" wrote:

Depending on what version of Excel you're using...

Excel 2007 only:

=IFERROR(AVERAGE(IF((Agents=I2)*(MONTH(SubmitDate) =7),DataRange)),"-")

All versions of Excel:

=IF(ISERROR(AVERAGE(IF((agents=I2)*(MONTH(submitda te)=7),datarange))),"-",AVERAGE(IF((agents=I2)*(MONTH(submitdate)=7),dat arange)))

Both are array entered!

--
Biff
Microsoft Excel MVP


"Jodi Macy" wrote in message
...
How would I code this to get rid of the #DIV/0! and show a dash (-) ?
I've tried a few things with no luck....
Jodi

{=AVERAGE(IF((Agents=I2)*(MONTH(SubmitDate)=7),Dat aRange,"-"))}




"T. Valko" wrote:

Good deal. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"Jodi Macy" wrote in message
...
Nevermind, I figured it out and I LOVE HOW THIS WORKS. I've been
struggling
with this for awhile now. THANK YOU, thank-you, thank-you...
Jodi



"Jodi Macy" wrote:

I don't understand why H2 has relevance....
Also, how do I involk the "CTRL,SHIFT,ENTER (not just ENTER)" command?
I've
never heard of this.
Jodi



"T. Valko" wrote:

Try this array formula** :

=AVERAGE(IF((A2:A20=H2)*(MONTH(B2:B20)=9),C2:E20))

Whe

A2:A20 are names
H2 is the name of interest
B2:B20 are dates
9 is the month number of interest (9 = September)
C2:E20 are the values to average

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

--
Biff
Microsoft Excel MVP


"Jodi Macy" <Jodi wrote in message
...
I have the raw data below where I would like to 'average' for
September,
the
combined total in the last three columns for any particular agent.
Is vLookup the correct funtion for this?

Agent Submit Time Attitude Aptitude Experience
Zoumadakis, Steven 07/29/08 09:12 AM 5 5 5
Zoumadakis, Steven 08/05/08 05:07 PM 5 5 3
Zoumadakis, Steven 08/07/08 09:06 AM 5 5 5
Zoumadakis, Steven 08/07/08 03:24 PM 5 4 5
Zoumadakis, Steven 08/11/08 08:08 AM 4 4 4
Zoumadakis, Steven 08/11/08 03:13 PM 5 5 5
Zoumadakis, Steven 08/12/08 02:28 PM 5 5 5
Zoumadakis, Steven 08/13/08 03:20 PM 5 5 5
Zoumadakis, Steven 08/14/08 03:13 PM 5 5 5
Zoumadakis, Steven 08/15/08 01:13 PM 5 5 5
Zoumadakis, Steven 08/19/08 07:41 AM 4 3 4
Zoumadakis, Steven 08/21/08 01:50 PM 5 5 5
Vasquez, Richard 08/11/08 09:23 AM 5 5 5
Vasquez, Richard 08/13/08 01:21 PM 4 3 4
Vasquez, Richard 08/20/08 06:56 AM 2 1 2
Vasquez, Richard 08/21/08 08:16 AM 4 3 4
Vasquez, Richard 08/25/08 09:29 AM 4 3 3
Vasquez, Richard 09/10/08 01:05 PM 5 4 5
Vasquez, Richard 09/15/08 09:04 AM 4 4 4

For the YTD, change each instance of:

....(MONTH(SubmitDate)=7)...

To:

....(YEAR(SubmitDate)=year_number)...

For the quarter (calendar quarter) you have to add another test. For
example, for the 1st quarter which is Jan - Mar:

....(MONTH(SubmitDate)=1)*(MONTH(SubmitDate)<=3). ..


--
Biff
Microsoft Excel MVP


"Jodi" wrote in message
...
The second one worked great with my 2003 version.
Thanks again!
One more question, is this the correct way to obtain a Ytd number?
=IF(ISERROR(AVERAGE(IF((Agents=L5)*(MONTH(SubmitDa te)),DataRange))),"-",AVERAGE(IF((Agents=L5)*(MONTH(SubmitDate)),DataR ange)))

And would this be the way for a particular quarter?
=IF(ISERROR(AVERAGE(IF((Agents=L5)*(MONTH(SubmitDa te)<"7,9"),DataRange))),"-",AVERAGE(IF((Agents=L5)*(MONTH(SubmitDate)<"7,9" ),DataRange)))




"T. Valko" wrote:

Depending on what version of Excel you're using...

Excel 2007 only:

=IFERROR(AVERAGE(IF((Agents=I2)*(MONTH(SubmitDate) =7),DataRange)),"-")

All versions of Excel:

=IF(ISERROR(AVERAGE(IF((agents=I2)*(MONTH(submitda te)=7),datarange))),"-",AVERAGE(IF((agents=I2)*(MONTH(submitdate)=7),dat arange)))

Both are array entered!

--
Biff
Microsoft Excel MVP


"Jodi Macy" wrote in message
...
How would I code this to get rid of the #DIV/0! and show a dash (-) ?
I've tried a few things with no luck....
Jodi

{=AVERAGE(IF((Agents=I2)*(MONTH(SubmitDate)=7),Dat aRange,"-"))}




"T. Valko" wrote:

Good deal. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"Jodi Macy" wrote in message
...
Nevermind, I figured it out and I LOVE HOW THIS WORKS. I've been
struggling
with this for awhile now. THANK YOU, thank-you, thank-you...
Jodi



"Jodi Macy" wrote:

I don't understand why H2 has relevance....
Also, how do I involk the "CTRL,SHIFT,ENTER (not just ENTER)"
command?
I've
never heard of this.
Jodi



"T. Valko" wrote:

Try this array formula** :

=AVERAGE(IF((A2:A20=H2)*(MONTH(B2:B20)=9),C2:E20))

Whe

A2:A20 are names
H2 is the name of interest
B2:B20 are dates
9 is the month number of interest (9 = September)
C2:E20 are the values to average

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

--
Biff
Microsoft Excel MVP


"Jodi Macy" <Jodi wrote in
message
...
I have the raw data below where I would like to 'average' for
September,
the
combined total in the last three columns for any particular
agent.
Is vLookup the correct funtion for this?

Agent Submit Time Attitude Aptitude Experience
Zoumadakis, Steven 07/29/08 09:12 AM 5 5 5
Zoumadakis, Steven 08/05/08 05:07 PM 5 5 3
Zoumadakis, Steven 08/07/08 09:06 AM 5 5 5
Zoumadakis, Steven 08/07/08 03:24 PM 5 4 5
Zoumadakis, Steven 08/11/08 08:08 AM 4 4 4
Zoumadakis, Steven 08/11/08 03:13 PM 5 5 5
Zoumadakis, Steven 08/12/08 02:28 PM 5 5 5
Zoumadakis, Steven 08/13/08 03:20 PM 5 5 5
Zoumadakis, Steven 08/14/08 03:13 PM 5 5 5
Zoumadakis, Steven 08/15/08 01:13 PM 5 5 5
Zoumadakis, Steven 08/19/08 07:41 AM 4 3 4
Zoumadakis, Steven 08/21/08 01:50 PM 5 5 5
Vasquez, Richard 08/11/08 09:23 AM 5 5 5
Vasquez, Richard 08/13/08 01:21 PM 4 3 4
Vasquez, Richard 08/20/08 06:56 AM 2 1 2
Vasquez, Richard 08/21/08 08:16 AM 4 3 4
Vasquez, Richard 08/25/08 09:29 AM 4 3 3
Vasquez, Richard 09/10/08 01:05 PM 5 4 5
Vasquez, Richard 09/15/08 09:04 AM 4 4 4

THAT WORKED PERFECTLY.

And I hate to bother you one more time, but I can't seem to figure out how
to compute just October.... I tried this with no luck amongst other
criteria. I'm hopless....

=IF(ISERROR(AVERAGE(IF((Agents)*(MONTH(SubmitDate) =10),DataRange))),"-",AVERAGE(IF((Agents)*(MONTH(SubmitDate)=10),DataR ange)))




"T. Valko" wrote:

For the YTD, change each instance of:

....(MONTH(SubmitDate)=7)...

To:

....(YEAR(SubmitDate)=year_number)...

For the quarter (calendar quarter) you have to add another test. For
example, for the 1st quarter which is Jan - Mar:

....(MONTH(SubmitDate)=1)*(MONTH(SubmitDate)<=3). ..


--
Biff
Microsoft Excel MVP


"Jodi" wrote in message
...
The second one worked great with my 2003 version.
Thanks again!
One more question, is this the correct way to obtain a Ytd number?
=IF(ISERROR(AVERAGE(IF((Agents=L5)*(MONTH(SubmitDa te)),DataRange))),"-",AVERAGE(IF((Agents=L5)*(MONTH(SubmitDate)),DataR ange)))

And would this be the way for a particular quarter?
=IF(ISERROR(AVERAGE(IF((Agents=L5)*(MONTH(SubmitDa te)<"7,9"),DataRange))),"-",AVERAGE(IF((Agents=L5)*(MONTH(SubmitDate)<"7,9" ),DataRange)))




"T. Valko" wrote:

Depending on what version of Excel you're using...

Excel 2007 only:

=IFERROR(AVERAGE(IF((Agents=I2)*(MONTH(SubmitDate) =7),DataRange)),"-")

All versions of Excel:

=IF(ISERROR(AVERAGE(IF((agents=I2)*(MONTH(submitda te)=7),datarange))),"-",AVERAGE(IF((agents=I2)*(MONTH(submitdate)=7),dat arange)))

Both are array entered!

--
Biff
Microsoft Excel MVP


"Jodi Macy" wrote in message
...
How would I code this to get rid of the #DIV/0! and show a dash (-) ?
I've tried a few things with no luck....
Jodi

{=AVERAGE(IF((Agents=I2)*(MONTH(SubmitDate)=7),Dat aRange,"-"))}




"T. Valko" wrote:

Good deal. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"Jodi Macy" wrote in message
...
Nevermind, I figured it out and I LOVE HOW THIS WORKS. I've been
struggling
with this for awhile now. THANK YOU, thank-you, thank-you...
Jodi



"Jodi Macy" wrote:

I don't understand why H2 has relevance....
Also, how do I involk the "CTRL,SHIFT,ENTER (not just ENTER)"
command?
I've
never heard of this.
Jodi



"T. Valko" wrote:

Try this array formula** :

=AVERAGE(IF((A2:A20=H2)*(MONTH(B2:B20)=9),C2:E20))

Whe

A2:A20 are names
H2 is the name of interest
B2:B20 are dates
9 is the month number of interest (9 = September)
C2:E20 are the values to average

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

--
Biff
Microsoft Excel MVP


"Jodi Macy" <Jodi wrote in
message
...
I have the raw data below where I would like to 'average' for
September,
the
combined total in the last three columns for any particular
agent.
Is vLookup the correct funtion for this?

Agent Submit Time Attitude Aptitude Experience
Zoumadakis, Steven 07/29/08 09:12 AM 5 5 5
Zoumadakis, Steven 08/05/08 05:07 PM 5 5 3
Zoumadakis, Steven 08/07/08 09:06 AM 5 5 5
Zoumadakis, Steven 08/07/08 03:24 PM 5 4 5
Zoumadakis, Steven 08/11/08 08:08 AM 4 4 4
Zoumadakis, Steven 08/11/08 03:13 PM 5 5 5
Zoumadakis, Steven 08/12/08 02:28 PM 5 5 5
Zoumadakis, Steven 08/13/08 03:20 PM 5 5 5
Zoumadakis, Steven 08/14/08 03:13 PM 5 5 5
Zoumadakis, Steven 08/15/08 01:13 PM 5 5 5
Zoumadakis, Steven 08/19/08 07:41 AM 4 3 4
Zoumadakis, Steven 08/21/08 01:50 PM 5 5 5
Vasquez, Richard 08/11/08 09:23 AM 5 5 5
Vasquez, Richard 08/13/08 01:21 PM 4 3 4
Vasquez, Richard 08/20/08 06:56 AM 2 1 2
Vasquez, Richard 08/21/08 08:16 AM 4 3 4
Vasquez, Richard 08/25/08 09:29 AM 4 3 3
Vasquez, Richard 09/10/08 01:05 PM 5 4 5
Vasquez, Richard 09/15/08 09:04 AM 4 4 4

I think you're missing the comparison test for Agents.

In logical terms you have:

If Agents....

Ok, If Agents what? You have to test Agents against some condition. For
example:

If Agents = "some name"

....IF((Agents="Maxwell Smart")*(MONTH(SubmitDate)=10)...

....IF((Agents=A1)*(MONTH(SubmitDate)=10)...

--
Biff
Microsoft Excel MVP


"Jodi" wrote in message
...
THAT WORKED PERFECTLY.

And I hate to bother you one more time, but I can't seem to figure out how
to compute just October.... I tried this with no luck amongst other
criteria. I'm hopless....

=IF(ISERROR(AVERAGE(IF((Agents)*(MONTH(SubmitDate) =10),DataRange))),"-",AVERAGE(IF((Agents)*(MONTH(SubmitDate)=10),DataR ange)))




"T. Valko" wrote:

For the YTD, change each instance of:

....(MONTH(SubmitDate)=7)...

To:

....(YEAR(SubmitDate)=year_number)...

For the quarter (calendar quarter) you have to add another test. For
example, for the 1st quarter which is Jan - Mar:

....(MONTH(SubmitDate)=1)*(MONTH(SubmitDate)<=3). ..


--
Biff
Microsoft Excel MVP


"Jodi" wrote in message
...
The second one worked great with my 2003 version.
Thanks again!
One more question, is this the correct way to obtain a Ytd number?
=IF(ISERROR(AVERAGE(IF((Agents=L5)*(MONTH(SubmitDa te)),DataRange))),"-",AVERAGE(IF((Agents=L5)*(MONTH(SubmitDate)),DataR ange)))

And would this be the way for a particular quarter?
=IF(ISERROR(AVERAGE(IF((Agents=L5)*(MONTH(SubmitDa te)<"7,9"),DataRange))),"-",AVERAGE(IF((Agents=L5)*(MONTH(SubmitDate)<"7,9" ),DataRange)))




"T. Valko" wrote:

Depending on what version of Excel you're using...

Excel 2007 only:

=IFERROR(AVERAGE(IF((Agents=I2)*(MONTH(SubmitDate) =7),DataRange)),"-")

All versions of Excel:

=IF(ISERROR(AVERAGE(IF((agents=I2)*(MONTH(submitda te)=7),datarange))),"-",AVERAGE(IF((agents=I2)*(MONTH(submitdate)=7),dat arange)))

Both are array entered!

--
Biff
Microsoft Excel MVP


"Jodi Macy" wrote in message
...
How would I code this to get rid of the #DIV/0! and show a dash (-)
?
I've tried a few things with no luck....
Jodi

{=AVERAGE(IF((Agents=I2)*(MONTH(SubmitDate)=7),Dat aRange,"-"))}




"T. Valko" wrote:

Good deal. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"Jodi Macy" wrote in message
...
Nevermind, I figured it out and I LOVE HOW THIS WORKS. I've been
struggling
with this for awhile now. THANK YOU, thank-you, thank-you...
Jodi



"Jodi Macy" wrote:

I don't understand why H2 has relevance....
Also, how do I involk the "CTRL,SHIFT,ENTER (not just ENTER)"
command?
I've
never heard of this.
Jodi



"T. Valko" wrote:

Try this array formula** :

=AVERAGE(IF((A2:A20=H2)*(MONTH(B2:B20)=9),C2:E20))

Whe

A2:A20 are names
H2 is the name of interest
B2:B20 are dates
9 is the month number of interest (9 = September)
C2:E20 are the values to average

** array formulas need to be entered using the key combination
of
CTRL,SHIFT,ENTER (not just ENTER)

--
Biff
Microsoft Excel MVP


"Jodi Macy" <Jodi wrote in
message
...
I have the raw data below where I would like to 'average' for
September,
the
combined total in the last three columns for any particular
agent.
Is vLookup the correct funtion for this?

Agent Submit Time Attitude Aptitude
Experience
Zoumadakis, Steven 07/29/08 09:12 AM 5 5 5
Zoumadakis, Steven 08/05/08 05:07 PM 5 5 3
Zoumadakis, Steven 08/07/08 09:06 AM 5 5 5
Zoumadakis, Steven 08/07/08 03:24 PM 5 4 5
Zoumadakis, Steven 08/11/08 08:08 AM 4 4 4
Zoumadakis, Steven 08/11/08 03:13 PM 5 5 5
Zoumadakis, Steven 08/12/08 02:28 PM 5 5 5
Zoumadakis, Steven 08/13/08 03:20 PM 5 5 5
Zoumadakis, Steven 08/14/08 03:13 PM 5 5 5
Zoumadakis, Steven 08/15/08 01:13 PM 5 5 5
Zoumadakis, Steven 08/19/08 07:41 AM 4 3 4
Zoumadakis, Steven 08/21/08 01:50 PM 5 5 5
Vasquez, Richard 08/11/08 09:23 AM 5 5 5
Vasquez, Richard 08/13/08 01:21 PM 4 3 4
Vasquez, Richard 08/20/08 06:56 AM 2 1 2
Vasquez, Richard 08/21/08 08:16 AM 4 3 4
Vasquez, Richard 08/25/08 09:29 AM 4 3 3
Vasquez, Richard 09/10/08 01:05 PM 5 4 5
Vasquez, Richard 09/15/08 09:04 AM 4 4 4

Actually, I don't need the Agents at all, just a monthly number for the
month. is there a date range I need to specify?
Jodi


"T. Valko" wrote:

I think you're missing the comparison test for Agents.

In logical terms you have:

If Agents....

Ok, If Agents what? You have to test Agents against some condition. For
example:

If Agents = "some name"

....IF((Agents="Maxwell Smart")*(MONTH(SubmitDate)=10)...

....IF((Agents=A1)*(MONTH(SubmitDate)=10)...

--
Biff
Microsoft Excel MVP


"Jodi" wrote in message
...
THAT WORKED PERFECTLY.

And I hate to bother you one more time, but I can't seem to figure out how
to compute just October.... I tried this with no luck amongst other
criteria. I'm hopless....

=IF(ISERROR(AVERAGE(IF((Agents)*(MONTH(SubmitDate) =10),DataRange))),"-",AVERAGE(IF((Agents)*(MONTH(SubmitDate)=10),DataR ange)))




"T. Valko" wrote:

For the YTD, change each instance of:

....(MONTH(SubmitDate)=7)...

To:

....(YEAR(SubmitDate)=year_number)...

For the quarter (calendar quarter) you have to add another test. For
example, for the 1st quarter which is Jan - Mar:

....(MONTH(SubmitDate)=1)*(MONTH(SubmitDate)<=3). ..


--
Biff
Microsoft Excel MVP


"Jodi" wrote in message
...
The second one worked great with my 2003 version.
Thanks again!
One more question, is this the correct way to obtain a Ytd number?
=IF(ISERROR(AVERAGE(IF((Agents=L5)*(MONTH(SubmitDa te)),DataRange))),"-",AVERAGE(IF((Agents=L5)*(MONTH(SubmitDate)),DataR ange)))

And would this be the way for a particular quarter?
=IF(ISERROR(AVERAGE(IF((Agents=L5)*(MONTH(SubmitDa te)<"7,9"),DataRange))),"-",AVERAGE(IF((Agents=L5)*(MONTH(SubmitDate)<"7,9" ),DataRange)))




"T. Valko" wrote:

Depending on what version of Excel you're using...

Excel 2007 only:

=IFERROR(AVERAGE(IF((Agents=I2)*(MONTH(SubmitDate) =7),DataRange)),"-")

All versions of Excel:

=IF(ISERROR(AVERAGE(IF((agents=I2)*(MONTH(submitda te)=7),datarange))),"-",AVERAGE(IF((agents=I2)*(MONTH(submitdate)=7),dat arange)))

Both are array entered!

--
Biff
Microsoft Excel MVP


"Jodi Macy" wrote in message
...
How would I code this to get rid of the #DIV/0! and show a dash (-)
?
I've tried a few things with no luck....
Jodi

{=AVERAGE(IF((Agents=I2)*(MONTH(SubmitDate)=7),Dat aRange,"-"))}




"T. Valko" wrote:

Good deal. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"Jodi Macy" wrote in message
...
Nevermind, I figured it out and I LOVE HOW THIS WORKS. I've been
struggling
with this for awhile now. THANK YOU, thank-you, thank-you...
Jodi



"Jodi Macy" wrote:

I don't understand why H2 has relevance....
Also, how do I involk the "CTRL,SHIFT,ENTER (not just ENTER)"
command?
I've
never heard of this.
Jodi



"T. Valko" wrote:

Try this array formula** :

=AVERAGE(IF((A2:A20=H2)*(MONTH(B2:B20)=9),C2:E20))

Whe

A2:A20 are names
H2 is the name of interest
B2:B20 are dates
9 is the month number of interest (9 = September)
C2:E20 are the values to average

** array formulas need to be entered using the key combination
of
CTRL,SHIFT,ENTER (not just ENTER)

--
Biff
Microsoft Excel MVP


"Jodi Macy" <Jodi wrote in
message
...
I have the raw data below where I would like to 'average' for
September,
the
combined total in the last three columns for any particular
agent.
Is vLookup the correct funtion for this?

Agent Submit Time Attitude Aptitude
Experience
Zoumadakis, Steven 07/29/08 09:12 AM 5 5 5
Zoumadakis, Steven 08/05/08 05:07 PM 5 5 3
Zoumadakis, Steven 08/07/08 09:06 AM 5 5 5
Zoumadakis, Steven 08/07/08 03:24 PM 5 4 5
Zoumadakis, Steven 08/11/08 08:08 AM 4 4 4
Zoumadakis, Steven 08/11/08 03:13 PM 5 5 5
Zoumadakis, Steven 08/12/08 02:28 PM 5 5 5
Zoumadakis, Steven 08/13/08 03:20 PM 5 5 5
Zoumadakis, Steven 08/14/08 03:13 PM 5 5 5
Zoumadakis, Steven 08/15/08 01:13 PM 5 5 5
Zoumadakis, Steven 08/19/08 07:41 AM 4 3 4
Zoumadakis, Steven 08/21/08 01:50 PM 5 5 5
Vasquez, Richard 08/11/08 09:23 AM 5 5 5
Vasquez, Richard 08/13/08 01:21 PM 4 3 4
Vasquez, Richard 08/20/08 06:56 AM 2 1 2
Vasquez, Richard 08/21/08 08:16 AM 4 3 4
Vasquez, Richard 08/25/08 09:29 AM 4 3 3
Vasquez, Richard 09/10/08 01:05 PM 5 4 5
Vasquez, Richard 09/15/08 09:04 AM 4 4 4

Actually, I don't need the Agents at all

So, you want the average based on the sole condition that SubmitDate =
October?

=IF(ISERROR(AVERAGE(IF(MONTH(SubmitDate)=10,DataRa nge))),"-",AVERAGE(IF(MONTH(SubmitDate)=10,DataRange)))


--
Biff
Microsoft Excel MVP


"Jodi" wrote in message
...
Actually, I don't need the Agents at all, just a monthly number for the
month. is there a date range I need to specify?
Jodi


"T. Valko" wrote:

I think you're missing the comparison test for Agents.

In logical terms you have:

If Agents....

Ok, If Agents what? You have to test Agents against some condition. For
example:

If Agents = "some name"

....IF((Agents="Maxwell Smart")*(MONTH(SubmitDate)=10)...

....IF((Agents=A1)*(MONTH(SubmitDate)=10)...

--
Biff
Microsoft Excel MVP


"Jodi" wrote in message
...
THAT WORKED PERFECTLY.

And I hate to bother you one more time, but I can't seem to figure out
how
to compute just October.... I tried this with no luck amongst other
criteria. I'm hopless....

=IF(ISERROR(AVERAGE(IF((Agents)*(MONTH(SubmitDate) =10),DataRange))),"-",AVERAGE(IF((Agents)*(MONTH(SubmitDate)=10),DataR ange)))




"T. Valko" wrote:

For the YTD, change each instance of:

....(MONTH(SubmitDate)=7)...

To:

....(YEAR(SubmitDate)=year_number)...

For the quarter (calendar quarter) you have to add another test. For
example, for the 1st quarter which is Jan - Mar:

....(MONTH(SubmitDate)=1)*(MONTH(SubmitDate)<=3). ..


--
Biff
Microsoft Excel MVP


"Jodi" wrote in message
...
The second one worked great with my 2003 version.
Thanks again!
One more question, is this the correct way to obtain a Ytd number?
=IF(ISERROR(AVERAGE(IF((Agents=L5)*(MONTH(SubmitDa te)),DataRange))),"-",AVERAGE(IF((Agents=L5)*(MONTH(SubmitDate)),DataR ange)))

And would this be the way for a particular quarter?
=IF(ISERROR(AVERAGE(IF((Agents=L5)*(MONTH(SubmitDa te)<"7,9"),DataRange))),"-",AVERAGE(IF((Agents=L5)*(MONTH(SubmitDate)<"7,9" ),DataRange)))




"T. Valko" wrote:

Depending on what version of Excel you're using...

Excel 2007 only:

=IFERROR(AVERAGE(IF((Agents=I2)*(MONTH(SubmitDate) =7),DataRange)),"-")

All versions of Excel:

=IF(ISERROR(AVERAGE(IF((agents=I2)*(MONTH(submitda te)=7),datarange))),"-",AVERAGE(IF((agents=I2)*(MONTH(submitdate)=7),dat arange)))

Both are array entered!

--
Biff
Microsoft Excel MVP


"Jodi Macy" wrote in message
...
How would I code this to get rid of the #DIV/0! and show a dash
(-)
?
I've tried a few things with no luck....
Jodi

{=AVERAGE(IF((Agents=I2)*(MONTH(SubmitDate)=7),Dat aRange,"-"))}




"T. Valko" wrote:

Good deal. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"Jodi Macy" wrote in
message
...
Nevermind, I figured it out and I LOVE HOW THIS WORKS. I've
been
struggling
with this for awhile now. THANK YOU, thank-you, thank-you...
Jodi



"Jodi Macy" wrote:

I don't understand why H2 has relevance....
Also, how do I involk the "CTRL,SHIFT,ENTER (not just ENTER)"
command?
I've
never heard of this.
Jodi



"T. Valko" wrote:

Try this array formula** :

=AVERAGE(IF((A2:A20=H2)*(MONTH(B2:B20)=9),C2:E20))

Whe

A2:A20 are names
H2 is the name of interest
B2:B20 are dates
9 is the month number of interest (9 = September)
C2:E20 are the values to average

** array formulas need to be entered using the key
combination
of
CTRL,SHIFT,ENTER (not just ENTER)

--
Biff
Microsoft Excel MVP


"Jodi Macy" <Jodi wrote in
message
...
I have the raw data below where I would like to 'average'
for
September,
the
combined total in the last three columns for any
particular
agent.
Is vLookup the correct funtion for this?

Agent Submit Time Attitude Aptitude
Experience
Zoumadakis, Steven 07/29/08 09:12 AM 5 5 5
Zoumadakis, Steven 08/05/08 05:07 PM 5 5 3
Zoumadakis, Steven 08/07/08 09:06 AM 5 5 5
Zoumadakis, Steven 08/07/08 03:24 PM 5 4 5
Zoumadakis, Steven 08/11/08 08:08 AM 4 4 4
Zoumadakis, Steven 08/11/08 03:13 PM 5 5 5
Zoumadakis, Steven 08/12/08 02:28 PM 5 5 5
Zoumadakis, Steven 08/13/08 03:20 PM 5 5 5
Zoumadakis, Steven 08/14/08 03:13 PM 5 5 5
Zoumadakis, Steven 08/15/08 01:13 PM 5 5 5
Zoumadakis, Steven 08/19/08 07:41 AM 4 3 4
Zoumadakis, Steven 08/21/08 01:50 PM 5 5 5
Vasquez, Richard 08/11/08 09:23 AM 5 5 5
Vasquez, Richard 08/13/08 01:21 PM 4 3 4
Vasquez, Richard 08/20/08 06:56 AM 2 1 2
Vasquez, Richard 08/21/08 08:16 AM 4 3 4
Vasquez, Richard 08/25/08 09:29 AM 4 3 3
Vasquez, Richard 09/10/08 01:05 PM 5 4 5
Vasquez, Richard 09/15/08 09:04 AM 4 4 4

This is very awesome, I can't believe how patient you've been with me. My
job depends on it. Thank you!!!

So, my last request would be, what if I only wanted the Agents score say
from a specified daterange, like Oct 1 through Oct 8th?
I realized I needed to slice this data every which way but loose, ha!
Jodi




"T. Valko" wrote:
So, you want the average based on the sole condition that SubmitDate =
October?
=IF(ISERROR(AVERAGE(IF(MONTH(SubmitDate)=10,DataRa nge))),"-",AVERAGE(IF(MONTH(SubmitDate)=10,DataRange)))


--
Biff
Microsoft Excel MVP


"Jodi" wrote in message
...
Actually, I don't need the Agents at all, just a monthly number for the
month. is there a date range I need to specify?
Jodi


"T. Valko" wrote:

I think you're missing the comparison test for Agents.

In logical terms you have:

If Agents....

Ok, If Agents what? You have to test Agents against some condition. For
example:

If Agents = "some name"

....IF((Agents="Maxwell Smart")*(MONTH(SubmitDate)=10)...

....IF((Agents=A1)*(MONTH(SubmitDate)=10)...

--
Biff
Microsoft Excel MVP


"Jodi" wrote in message
...
THAT WORKED PERFECTLY.

And I hate to bother you one more time, but I can't seem to figure out
how
to compute just October.... I tried this with no luck amongst other
criteria. I'm hopless....

=IF(ISERROR(AVERAGE(IF((Agents)*(MONTH(SubmitDate) =10),DataRange))),"-",AVERAGE(IF((Agents)*(MONTH(SubmitDate)=10),DataR ange)))




"T. Valko" wrote:

For the YTD, change each instance of:

....(MONTH(SubmitDate)=7)...

To:

....(YEAR(SubmitDate)=year_number)...

For the quarter (calendar quarter) you have to add another test. For
example, for the 1st quarter which is Jan - Mar:

....(MONTH(SubmitDate)=1)*(MONTH(SubmitDate)<=3). ..


--
Biff
Microsoft Excel MVP


"Jodi" wrote in message
...
The second one worked great with my 2003 version.
Thanks again!
One more question, is this the correct way to obtain a Ytd number?
=IF(ISERROR(AVERAGE(IF((Agents=L5)*(MONTH(SubmitDa te)),DataRange))),"-",AVERAGE(IF((Agents=L5)*(MONTH(SubmitDate)),DataR ange)))

And would this be the way for a particular quarter?
=IF(ISERROR(AVERAGE(IF((Agents=L5)*(MONTH(SubmitDa te)<"7,9"),DataRange))),"-",AVERAGE(IF((Agents=L5)*(MONTH(SubmitDate)<"7,9" ),DataRange)))




"T. Valko" wrote:

Depending on what version of Excel you're using...

Excel 2007 only:

=IFERROR(AVERAGE(IF((Agents=I2)*(MONTH(SubmitDate) =7),DataRange)),"-")

All versions of Excel:

=IF(ISERROR(AVERAGE(IF((agents=I2)*(MONTH(submitda te)=7),datarange))),"-",AVERAGE(IF((agents=I2)*(MONTH(submitdate)=7),dat arange)))

Both are array entered!

--
Biff
Microsoft Excel MVP


"Jodi Macy" wrote in message
...
How would I code this to get rid of the #DIV/0! and show a dash
(-)
?
I've tried a few things with no luck....
Jodi

{=AVERAGE(IF((Agents=I2)*(MONTH(SubmitDate)=7),Dat aRange,"-"))}




"T. Valko" wrote:

Good deal. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"Jodi Macy" wrote in
message
...
Nevermind, I figured it out and I LOVE HOW THIS WORKS. I've
been
struggling
with this for awhile now. THANK YOU, thank-you, thank-you...
Jodi



"Jodi Macy" wrote:

I don't understand why H2 has relevance....
Also, how do I involk the "CTRL,SHIFT,ENTER (not just ENTER)"
command?
I've
never heard of this.
Jodi



"T. Valko" wrote:

Try this array formula** :

=AVERAGE(IF((A2:A20=H2)*(MONTH(B2:B20)=9),C2:E20))

Whe

A2:A20 are names
H2 is the name of interest
B2:B20 are dates
9 is the month number of interest (9 = September)
C2:E20 are the values to average

** array formulas need to be entered using the key
combination
of
CTRL,SHIFT,ENTER (not just ENTER)

--
Biff
Microsoft Excel MVP


"Jodi Macy" <Jodi wrote in
message
...
I have the raw data below where I would like to 'average'
for
September,
the
combined total in the last three columns for any
particular
agent.
Is vLookup the correct funtion for this?

Agent Submit Time Attitude Aptitude
Experience
Zoumadakis, Steven 07/29/08 09:12 AM 5 5 5
Zoumadakis, Steven 08/05/08 05:07 PM 5 5 3
Zoumadakis, Steven 08/07/08 09:06 AM 5 5 5
Zoumadakis, Steven 08/07/08 03:24 PM 5 4 5
Zoumadakis, Steven 08/11/08 08:08 AM 4 4 4
Zoumadakis, Steven 08/11/08 03:13 PM 5 5 5
Zoumadakis, Steven 08/12/08 02:28 PM 5 5 5
Zoumadakis, Steven 08/13/08 03:20 PM 5 5 5
Zoumadakis, Steven 08/14/08 03:13 PM 5 5 5
Zoumadakis, Steven 08/15/08 01:13 PM 5 5 5
Zoumadakis, Steven 08/19/08 07:41 AM 4 3 4
Zoumadakis, Steven 08/21/08 01:50 PM 5 5 5
Vasquez, Richard 08/11/08 09:23 AM 5 5 5
Vasquez, Richard 08/13/08 01:21 PM 4 3 4
Vasquez, Richard 08/20/08 06:56 AM 2 1 2
Vasquez, Richard 08/21/08 08:16 AM 4 3 4
Vasquez, Richard 08/25/08 09:29 AM 4 3 3
Vasquez, Richard 09/10/08 01:05 PM 5 4 5
Vasquez, Richard 09/15/08 09:04 AM 4 4 4

For a date range...

=AVERAGE(IF((SubmitDate=lower_date_boundary)*(Sub mitDate<=upper_date_boundary),DataRange))

The date boundaries can be expressed several different ways:

Using the DATE function:

=AVERAGE(IF((SubmitDate=DATE(2008,10,1))*(SubmitD ate<=DATE(2008,10,8)),DataRange))

Using a hardcoded date value (I personally don't like this method!):

=AVERAGE(IF((SubmitDate=--"2008/10/1")*(SubmitDate<=--"2008/10/8"),DataRange))

Using cell references (this would be my preference!):

A1 = lower date boundary = 10/1/2008
B1 = upper date boundary = 10/8/2008
C1 = agent name = Maxwell Smart

=AVERAGE(IF((SubmitDate=-A1)*(SubmitDate<=B1),DataRange))

And, if you want to break it out for a specific agent:

=AVERAGE(IF((Agents=C1)*(SubmitDate=A1)*(SubmitDa te<=B1),DataRange))

All formulas are array entered.

--
Biff
Microsoft Excel MVP


"Jodi" wrote in message
...
This is very awesome, I can't believe how patient you've been with me. My
job depends on it. Thank you!!!

So, my last request would be, what if I only wanted the Agents score say
from a specified daterange, like Oct 1 through Oct 8th?
I realized I needed to slice this data every which way but loose, ha!
Jodi




"T. Valko" wrote:
So, you want the average based on the sole condition that SubmitDate =
October?
=IF(ISERROR(AVERAGE(IF(MONTH(SubmitDate)=10,DataRa nge))),"-",AVERAGE(IF(MONTH(SubmitDate)=10,DataRange)))


--
Biff
Microsoft Excel MVP


"Jodi" wrote in message
...
Actually, I don't need the Agents at all, just a monthly number for the
month. is there a date range I need to specify?
Jodi


"T. Valko" wrote:

I think you're missing the comparison test for Agents.

In logical terms you have:

If Agents....

Ok, If Agents what? You have to test Agents against some condition.
For
example:

If Agents = "some name"

....IF((Agents="Maxwell Smart")*(MONTH(SubmitDate)=10)...

....IF((Agents=A1)*(MONTH(SubmitDate)=10)...

--
Biff
Microsoft Excel MVP


"Jodi" wrote in message
...
THAT WORKED PERFECTLY.

And I hate to bother you one more time, but I can't seem to figure
out
how
to compute just October.... I tried this with no luck amongst other
criteria. I'm hopless....

=IF(ISERROR(AVERAGE(IF((Agents)*(MONTH(SubmitDate) =10),DataRange))),"-",AVERAGE(IF((Agents)*(MONTH(SubmitDate)=10),DataR ange)))




"T. Valko" wrote:

For the YTD, change each instance of:

....(MONTH(SubmitDate)=7)...

To:

....(YEAR(SubmitDate)=year_number)...

For the quarter (calendar quarter) you have to add another test.
For
example, for the 1st quarter which is Jan - Mar:

....(MONTH(SubmitDate)=1)*(MONTH(SubmitDate)<=3). ..


--
Biff
Microsoft Excel MVP


"Jodi" wrote in message
...
The second one worked great with my 2003 version.
Thanks again!
One more question, is this the correct way to obtain a Ytd
number?
=IF(ISERROR(AVERAGE(IF((Agents=L5)*(MONTH(SubmitDa te)),DataRange))),"-",AVERAGE(IF((Agents=L5)*(MONTH(SubmitDate)),DataR ange)))

And would this be the way for a particular quarter?
=IF(ISERROR(AVERAGE(IF((Agents=L5)*(MONTH(SubmitDa te)<"7,9"),DataRange))),"-",AVERAGE(IF((Agents=L5)*(MONTH(SubmitDate)<"7,9" ),DataRange)))




"T. Valko" wrote:

Depending on what version of Excel you're using...

Excel 2007 only:

=IFERROR(AVERAGE(IF((Agents=I2)*(MONTH(SubmitDate) =7),DataRange)),"-")

All versions of Excel:

=IF(ISERROR(AVERAGE(IF((agents=I2)*(MONTH(submitda te)=7),datarange))),"-",AVERAGE(IF((agents=I2)*(MONTH(submitdate)=7),dat arange)))

Both are array entered!

--
Biff
Microsoft Excel MVP


"Jodi Macy" wrote in
message
...
How would I code this to get rid of the #DIV/0! and show a
dash
(-)
?
I've tried a few things with no luck....
Jodi

{=AVERAGE(IF((Agents=I2)*(MONTH(SubmitDate)=7),Dat aRange,"-"))}




"T. Valko" wrote:

Good deal. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"Jodi Macy" wrote in
message
...
Nevermind, I figured it out and I LOVE HOW THIS WORKS. I've
been
struggling
with this for awhile now. THANK YOU, thank-you,
thank-you...
Jodi



"Jodi Macy" wrote:

I don't understand why H2 has relevance....
Also, how do I involk the "CTRL,SHIFT,ENTER (not just
ENTER)"
command?
I've
never heard of this.
Jodi



"T. Valko" wrote:

Try this array formula** :

=AVERAGE(IF((A2:A20=H2)*(MONTH(B2:B20)=9),C2:E20))

Whe

A2:A20 are names
H2 is the name of interest
B2:B20 are dates
9 is the month number of interest (9 = September)
C2:E20 are the values to average

** array formulas need to be entered using the key
combination
of
CTRL,SHIFT,ENTER (not just ENTER)

--
Biff
Microsoft Excel MVP


"Jodi Macy" <Jodi wrote
in
message
...
I have the raw data below where I would like to
'average'
for
September,
the
combined total in the last three columns for any
particular
agent.
Is vLookup the correct funtion for this?

Agent Submit Time Attitude Aptitude
Experience
Zoumadakis, Steven 07/29/08 09:12 AM 5 5 5
Zoumadakis, Steven 08/05/08 05:07 PM 5 5 3
Zoumadakis, Steven 08/07/08 09:06 AM 5 5 5
Zoumadakis, Steven 08/07/08 03:24 PM 5 4 5
Zoumadakis, Steven 08/11/08 08:08 AM 4 4 4
Zoumadakis, Steven 08/11/08 03:13 PM 5 5 5
Zoumadakis, Steven 08/12/08 02:28 PM 5 5 5
Zoumadakis, Steven 08/13/08 03:20 PM 5 5 5
Zoumadakis, Steven 08/14/08 03:13 PM 5 5 5
Zoumadakis, Steven 08/15/08 01:13 PM 5 5 5
Zoumadakis, Steven 08/19/08 07:41 AM 4 3 4
Zoumadakis, Steven 08/21/08 01:50 PM 5 5 5
Vasquez, Richard 08/11/08 09:23 AM 5 5 5
Vasquez, Richard 08/13/08 01:21 PM 4 3 4
Vasquez, Richard 08/20/08 06:56 AM 2 1 2
Vasquez, Richard 08/21/08 08:16 AM 4 3 4
Vasquez, Richard 08/25/08 09:29 AM 4 3 3
Vasquez, Richard 09/10/08 01:05 PM 5 4 5
Vasquez, Richard 09/15/08 09:04 AM 4 4 4

This all works well. Thank you.
I used your suggested method. It's better, yes because I don't need to keep
the history.
You're a dream come true.....
Jodi


"T. Valko" wrote:

For a date range...

=AVERAGE(IF((SubmitDate=lower_date_boundary)*(Sub mitDate<=upper_date_boundary),DataRange))

The date boundaries can be expressed several different ways:

Using the DATE function:

=AVERAGE(IF((SubmitDate=DATE(2008,10,1))*(SubmitD ate<=DATE(2008,10,8)),DataRange))

Using a hardcoded date value (I personally don't like this method!):

=AVERAGE(IF((SubmitDate=--"2008/10/1")*(SubmitDate<=--"2008/10/8"),DataRange))

Using cell references (this would be my preference!):

A1 = lower date boundary = 10/1/2008
B1 = upper date boundary = 10/8/2008
C1 = agent name = Maxwell Smart

=AVERAGE(IF((SubmitDate=-A1)*(SubmitDate<=B1),DataRange))

And, if you want to break it out for a specific agent:

=AVERAGE(IF((Agents=C1)*(SubmitDate=A1)*(SubmitDa te<=B1),DataRange))

All formulas are array entered.

--
Biff
Microsoft Excel MVP


"Jodi" wrote in message
...
This is very awesome, I can't believe how patient you've been with me. My
job depends on it. Thank you!!!

So, my last request would be, what if I only wanted the Agents score say
from a specified daterange, like Oct 1 through Oct 8th?
I realized I needed to slice this data every which way but loose, ha!
Jodi




"T. Valko" wrote:
So, you want the average based on the sole condition that SubmitDate =
October?
=IF(ISERROR(AVERAGE(IF(MONTH(SubmitDate)=10,DataRa nge))),"-",AVERAGE(IF(MONTH(SubmitDate)=10,DataRange)))


--
Biff
Microsoft Excel MVP


"Jodi" wrote in message
...
Actually, I don't need the Agents at all, just a monthly number for the
month. is there a date range I need to specify?
Jodi


"T. Valko" wrote:

I think you're missing the comparison test for Agents.

In logical terms you have:

If Agents....

Ok, If Agents what? You have to test Agents against some condition.
For
example:

If Agents = "some name"

....IF((Agents="Maxwell Smart")*(MONTH(SubmitDate)=10)...

....IF((Agents=A1)*(MONTH(SubmitDate)=10)...

--
Biff
Microsoft Excel MVP


"Jodi" wrote in message
...
THAT WORKED PERFECTLY.

And I hate to bother you one more time, but I can't seem to figure
out
how
to compute just October.... I tried this with no luck amongst other
criteria. I'm hopless....

=IF(ISERROR(AVERAGE(IF((Agents)*(MONTH(SubmitDate) =10),DataRange))),"-",AVERAGE(IF((Agents)*(MONTH(SubmitDate)=10),DataR ange)))




"T. Valko" wrote:

For the YTD, change each instance of:

....(MONTH(SubmitDate)=7)...

To:

....(YEAR(SubmitDate)=year_number)...

For the quarter (calendar quarter) you have to add another test.
For
example, for the 1st quarter which is Jan - Mar:

....(MONTH(SubmitDate)=1)*(MONTH(SubmitDate)<=3). ..


--
Biff
Microsoft Excel MVP


"Jodi" wrote in message
...
The second one worked great with my 2003 version.
Thanks again!
One more question, is this the correct way to obtain a Ytd
number?
=IF(ISERROR(AVERAGE(IF((Agents=L5)*(MONTH(SubmitDa te)),DataRange))),"-",AVERAGE(IF((Agents=L5)*(MONTH(SubmitDate)),DataR ange)))

And would this be the way for a particular quarter?
=IF(ISERROR(AVERAGE(IF((Agents=L5)*(MONTH(SubmitDa te)<"7,9"),DataRange))),"-",AVERAGE(IF((Agents=L5)*(MONTH(SubmitDate)<"7,9" ),DataRange)))




"T. Valko" wrote:

Depending on what version of Excel you're using...

Excel 2007 only:

=IFERROR(AVERAGE(IF((Agents=I2)*(MONTH(SubmitDate) =7),DataRange)),"-")

All versions of Excel:

=IF(ISERROR(AVERAGE(IF((agents=I2)*(MONTH(submitda te)=7),datarange))),"-",AVERAGE(IF((agents=I2)*(MONTH(submitdate)=7),dat arange)))

Both are array entered!

--
Biff
Microsoft Excel MVP


"Jodi Macy" wrote in
message
...
How would I code this to get rid of the #DIV/0! and show a
dash
(-)
?
I've tried a few things with no luck....
Jodi

{=AVERAGE(IF((Agents=I2)*(MONTH(SubmitDate)=7),Dat aRange,"-"))}




"T. Valko" wrote:

Good deal. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"Jodi Macy" wrote in
message
...
Nevermind, I figured it out and I LOVE HOW THIS WORKS. I've
been
struggling
with this for awhile now. THANK YOU, thank-you,
thank-you...
Jodi



"Jodi Macy" wrote:

I don't understand why H2 has relevance....
Also, how do I involk the "CTRL,SHIFT,ENTER (not just
ENTER)"
command?
I've
never heard of this.
Jodi



"T. Valko" wrote:

Try this array formula** :

=AVERAGE(IF((A2:A20=H2)*(MONTH(B2:B20)=9),C2:E20))

Whe

A2:A20 are names
H2 is the name of interest
B2:B20 are dates
9 is the month number of interest (9 = September)
C2:E20 are the values to average

** array formulas need to be entered using the key
combination
of
CTRL,SHIFT,ENTER (not just ENTER)

--
Biff
Microsoft Excel MVP


"Jodi Macy" <Jodi wrote
in
message
...
I have the raw data below where I would like to
'average'
for
September,
the
combined total in the last three columns for any
particular
agent.
Is vLookup the correct funtion for this?

Agent Submit Time Attitude Aptitude
Experience
Zoumadakis, Steven 07/29/08 09:12 AM 5 5 5
Zoumadakis, Steven 08/05/08 05:07 PM 5 5 3
Zoumadakis, Steven 08/07/08 09:06 AM 5 5 5
Zoumadakis, Steven 08/07/08 03:24 PM 5 4 5
Zoumadakis, Steven 08/11/08 08:08 AM 4 4 4
Zoumadakis, Steven 08/11/08 03:13 PM 5 5 5
Zoumadakis, Steven 08/12/08 02:28 PM 5 5 5
Zoumadakis, Steven 08/13/08 03:20 PM 5 5 5
Zoumadakis, Steven 08/14/08 03:13 PM 5 5 5
Zoumadakis, Steven 08/15/08 01:13 PM 5 5 5
Zoumadakis, Steven 08/19/08 07:41 AM 4 3 4
Zoumadakis, Steven 08/21/08 01:50 PM 5 5 5
Vasquez, Richard 08/11/08 09:23 AM 5 5 5
Vasquez, Richard 08/13/08 01:21 PM 4 3 4
Vasquez, Richard 08/20/08 06:56 AM 2 1 2
Vasquez, Richard 08/21/08 08:16 AM 4 3 4
Vasquez, Richard 08/25/08 09:29 AM 4 3 3
Vasquez, Richard 09/10/08 01:05 PM 5 4 5
Vasquez, Richard 09/15/08 09:04 AM 4 4 4

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"Jodi" wrote in message
...
This all works well. Thank you.
I used your suggested method. It's better, yes because I don't need to
keep
the history.
You're a dream come true.....
Jodi


"T. Valko" wrote:

For a date range...

=AVERAGE(IF((SubmitDate=lower_date_boundary)*(Sub mitDate<=upper_date_boundary),DataRange))

The date boundaries can be expressed several different ways:

Using the DATE function:

=AVERAGE(IF((SubmitDate=DATE(2008,10,1))*(SubmitD ate<=DATE(2008,10,8)),DataRange))

Using a hardcoded date value (I personally don't like this method!):

=AVERAGE(IF((SubmitDate=--"2008/10/1")*(SubmitDate<=--"2008/10/8"),DataRange))

Using cell references (this would be my preference!):

A1 = lower date boundary = 10/1/2008
B1 = upper date boundary = 10/8/2008
C1 = agent name = Maxwell Smart

=AVERAGE(IF((SubmitDate=-A1)*(SubmitDate<=B1),DataRange))

And, if you want to break it out for a specific agent:

=AVERAGE(IF((Agents=C1)*(SubmitDate=A1)*(SubmitDa te<=B1),DataRange))

All formulas are array entered.

--
Biff
Microsoft Excel MVP


"Jodi" wrote in message
...
This is very awesome, I can't believe how patient you've been with me.
My
job depends on it. Thank you!!!

So, my last request would be, what if I only wanted the Agents score
say
from a specified daterange, like Oct 1 through Oct 8th?
I realized I needed to slice this data every which way but loose, ha!
Jodi




"T. Valko" wrote:
So, you want the average based on the sole condition that SubmitDate =
October?
=IF(ISERROR(AVERAGE(IF(MONTH(SubmitDate)=10,DataRa nge))),"-",AVERAGE(IF(MONTH(SubmitDate)=10,DataRange)))


--
Biff
Microsoft Excel MVP


"Jodi" wrote in message
...
Actually, I don't need the Agents at all, just a monthly number for
the
month. is there a date range I need to specify?
Jodi


"T. Valko" wrote:

I think you're missing the comparison test for Agents.

In logical terms you have:

If Agents....

Ok, If Agents what? You have to test Agents against some condition.
For
example:

If Agents = "some name"

....IF((Agents="Maxwell Smart")*(MONTH(SubmitDate)=10)...

....IF((Agents=A1)*(MONTH(SubmitDate)=10)...

--
Biff
Microsoft Excel MVP


"Jodi" wrote in message
...
THAT WORKED PERFECTLY.

And I hate to bother you one more time, but I can't seem to
figure
out
how
to compute just October.... I tried this with no luck amongst
other
criteria. I'm hopless....

=IF(ISERROR(AVERAGE(IF((Agents)*(MONTH(SubmitDate) =10),DataRange))),"-",AVERAGE(IF((Agents)*(MONTH(SubmitDate)=10),DataR ange)))




"T. Valko" wrote:

For the YTD, change each instance of:

....(MONTH(SubmitDate)=7)...

To:

....(YEAR(SubmitDate)=year_number)...

For the quarter (calendar quarter) you have to add another test.
For
example, for the 1st quarter which is Jan - Mar:

....(MONTH(SubmitDate)=1)*(MONTH(SubmitDate)<=3). ..


--
Biff
Microsoft Excel MVP


"Jodi" wrote in message
...
The second one worked great with my 2003 version.
Thanks again!
One more question, is this the correct way to obtain a Ytd
number?
=IF(ISERROR(AVERAGE(IF((Agents=L5)*(MONTH(SubmitDa te)),DataRange))),"-",AVERAGE(IF((Agents=L5)*(MONTH(SubmitDate)),DataR ange)))

And would this be the way for a particular quarter?
=IF(ISERROR(AVERAGE(IF((Agents=L5)*(MONTH(SubmitDa te)<"7,9"),DataRange))),"-",AVERAGE(IF((Agents=L5)*(MONTH(SubmitDate)<"7,9" ),DataRange)))




"T. Valko" wrote:

Depending on what version of Excel you're using...

Excel 2007 only:

=IFERROR(AVERAGE(IF((Agents=I2)*(MONTH(SubmitDate) =7),DataRange)),"-")

All versions of Excel:

=IF(ISERROR(AVERAGE(IF((agents=I2)*(MONTH(submitda te)=7),datarange))),"-",AVERAGE(IF((agents=I2)*(MONTH(submitdate)=7),dat arange)))

Both are array entered!

--
Biff
Microsoft Excel MVP


"Jodi Macy" wrote in
message
...
How would I code this to get rid of the #DIV/0! and show a
dash
(-)
?
I've tried a few things with no luck....
Jodi

{=AVERAGE(IF((Agents=I2)*(MONTH(SubmitDate)=7),Dat aRange,"-"))}




"T. Valko" wrote:

Good deal. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"Jodi Macy" wrote in
message
...
Nevermind, I figured it out and I LOVE HOW THIS WORKS.
I've
been
struggling
with this for awhile now. THANK YOU, thank-you,
thank-you...
Jodi



"Jodi Macy" wrote:

I don't understand why H2 has relevance....
Also, how do I involk the "CTRL,SHIFT,ENTER (not just
ENTER)"
command?
I've
never heard of this.
Jodi



"T. Valko" wrote:

Try this array formula** :

=AVERAGE(IF((A2:A20=H2)*(MONTH(B2:B20)=9),C2:E20))

Whe

A2:A20 are names
H2 is the name of interest
B2:B20 are dates
9 is the month number of interest (9 = September)
C2:E20 are the values to average

** array formulas need to be entered using the key
combination
of
CTRL,SHIFT,ENTER (not just ENTER)

--
Biff
Microsoft Excel MVP


"Jodi Macy" <Jodi
wrote
in
message
...
I have the raw data below where I would like to
'average'
for
September,
the
combined total in the last three columns for any
particular
agent.
Is vLookup the correct funtion for this?

Agent Submit Time Attitude Aptitude
Experience
Zoumadakis, Steven 07/29/08 09:12 AM 5 5 5
Zoumadakis, Steven 08/05/08 05:07 PM 5 5 3
Zoumadakis, Steven 08/07/08 09:06 AM 5 5 5
Zoumadakis, Steven 08/07/08 03:24 PM 5 4 5
Zoumadakis, Steven 08/11/08 08:08 AM 4 4 4
Zoumadakis, Steven 08/11/08 03:13 PM 5 5 5
Zoumadakis, Steven 08/12/08 02:28 PM 5 5 5
Zoumadakis, Steven 08/13/08 03:20 PM 5 5 5
Zoumadakis, Steven 08/14/08 03:13 PM 5 5 5
Zoumadakis, Steven 08/15/08 01:13 PM 5 5 5
Zoumadakis, Steven 08/19/08 07:41 AM 4 3 4
Zoumadakis, Steven 08/21/08 01:50 PM 5 5 5
Vasquez, Richard 08/11/08 09:23 AM 5 5 5
Vasquez, Richard 08/13/08 01:21 PM 4 3 4
Vasquez, Richard 08/20/08 06:56 AM 2 1 2
Vasquez, Richard 08/21/08 08:16 AM 4 3 4
Vasquez, Richard 08/25/08 09:29 AM 4 3 3
Vasquez, Richard 09/10/08 01:05 PM 5 4 5
Vasquez, Richard 09/15/08 09:04 AM 4 4 4