vLookup with dates

[Jodi Macy]

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

 

[T. Valko]

Try this array formula** :

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

Where:

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)

 

[Jodi Macy]

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]

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.

 

[Jodi Macy]

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

 

[T. Valko]

Good deal. Thanks for the feedback!

 

[Jodi Macy]

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),DataRange,"-"))}

 

[T. Valko]

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(submitdate)=7),datarange))),"-",AVERAGE(IF((agents=I2)*(MONTH(submitdate)=7),datarange)))

Both are array entered!

 

[Jodi]

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(SubmitDate)),DataRange))),"-",AVERAGE(IF((Agents=L5)*(MONTH(SubmitDate)),DataRange)))

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

 

[T. Valko]

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)...

 

[Jodi]

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),DataRange)))

 

[T. Valko]

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)...

 

[Jodi]

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]

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,DataRange))),"-",AVERAGE(IF(MONTH(SubmitDate)=10,DataRange)))

 

[Jodi]

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]

For a date range...

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

The date boundaries can be expressed several different ways:

Using the DATE function:

=AVERAGE(IF((SubmitDate>=DATE(2008,10,1))*(SubmitDate<=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)*(SubmitDate<=B1),DataRange))

All formulas are array entered.

 

[Jodi]

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]

You're welcome. Thanks for the feedback!