# vLookup with dates

J

#### 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

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

J

#### 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

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

J

#### 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

#### T. Valko

Good deal. Thanks for the feedback!

J

#### 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

#### 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!

J

#### 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

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

J

#### 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

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

J

#### 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

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

J

#### 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

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

J

#### 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

#### T. Valko

You're welcome. Thanks for the feedback!