Average IF

[Fester]

I want to average the amount of time someone spends on a specific date
with customers. I have it average the person by user for the entire
time (1 week), but I want to average it per day. I am looking to
create a formula that looks at 2 columns (Column D has dates, Column I
has Usernames). It should then calculate the average time spent if
the date and username match.

D E I R
3 07/18/07 09:30 beckerm
4 07/18/07 09:39 beckerm :09
5 07/18/07 09:46 beckerm :07

S21 T20 T21 (Average)
07/18/07 beckerm :08

Is there an easy way to do this or am I kidding myself.

B.

 

[Bob Phillips]

=AVERAGE(IF((D35=--"2007-07-18")*(I3:I5="beckerm")*(R3:R5<>""),R3:R5))

which is an array formula, it should be committed with Ctrl-Shift-Enter, not
just Enter.
Excel will automatically enclose the formula in braces (curly brackets), do
not try to do this manually.
When editing the formula, it must again be array-entered.

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Fester]

I tried utilizing the entire range and each cell that has the name to
match and all if gives me is 0:00. Here is the formula that I have in
the cell.

{=AVERAGE(IF(AND($D$2:$D$2000=S21,$I$2:$I$2000=T20),$R$3:$R$2000))}

So if anything in Column D matches S21(07/31/07) and Anything in
Column I matches T20(beckerm), then average the time (Column R).

Does this make more sense?

 

[Bob Phillips]

Not quite. You should read what I gave you

=AVERAGE(IF(($D$2:$D$2000=S21)*($I$2:$I$2000=T20)*($R$3:$R$2000<>""),$R$3:$R$2000))


--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Fester]

Sorry, I should have clarified. I tried what you wrote initially,
modified to include the cell range I was looking for.

Now, after utilizing the correct formula that you gave the second
time, it seemed to work on one date, but then gives me no average time
for the rest of them. Also, I made one modification (T20 to $T$20) so
that it stayed in the same cell.

I appreciate your help on this. Sometimes I just thoroughly
confused. Also, what does the "*" between each AND do?

 

[Don Guillett]

Your formula should work if you entered in the first cell desired>did ctrl
shift enter> then copied down.
Assumes your dates ARE dates
--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(e-mail address removed)
Sorry, I should have clarified. I tried what you wrote initially,
modified to include the cell range I was looking for.

Now, after utilizing the correct formula that you gave the second
time, it seemed to work on one date, but then gives me no average time
for the rest of them. Also, I made one modification (T20 to $T$20) so
that it stayed in the same cell.

I appreciate your help on this. Sometimes I just thoroughly
confused. Also, what does the "*" between each AND do?