Average # of Days by Name

[Guest]

I submitted this yesterday but I have another question. If I change #N/A to 0/0/04 and do the subtraction (C-A), is there a way to average the work by Name without using a pivot table? Thanks!

PROJECT DATE Name last worked
5/17/2004 Carleen #N/A
5/17/2004 Lorri #N/A
5/15/2004 Lorri #N/A
5/15/2004 Debbie G 6/29/04
5/15/2004 Janice #N/A
4/30/2002 Dave 6/22/04
5/22/2002 Lorri 6/28/04
5/26/2002 Janice 6/21/04
7/13/2002 Lorri #N/A
9/3/2002 Debbie G #N/A
10/15/2002 Debbie G 6/29/04

 

[Domenic]

I'm assuming that #N/A means no date is available and that they're not
included in your calculation. With that in mind, you could keep the
#N/A in Column C, if you want, and try the following:

D2, copied down:

=IF(ISNA(C2),#N/A,C2-A2)

Then, use the following formula to calculate the average:

=AVERAGE(IF((B2:B12=F2)*ISNUMBER(D212),D212))
entered using CTRL+SHIFT+ENTER

where F2 contains the name of interest

Hope this helps!

 

[Domenic]

I could not get this formula to work. If I leave the column as #N/A then I
get an error message.

That's right. For those where the date for "last worked" is not
available, it will return #N/A. For those with a date for "last
worked", it will return that date. Then the Average formula will
average those that fit the criteria (name and "last worked" date).

On the formula it won't even calculate. Any suggestions I what I might be
doing wrong?

Make sure that when you enter the Average formula that you do so by
pressing CONTROL+SHIFT+ENTER. Excel will automatically add braces {}
around the formula.

Having said that, you may want to use the following formula, which
wouldn't require the addition of another column...

=AVERAGE(IF((B2:B12="Debbie G")*(ISNUMBER(C2:C12)),(C2:C12-A2:A12)))

entered using CTRL+SHIFT+ENTER

If you want a list of the average days worked for all of the names, then
enter your list of names in Column F, starting at F2, and then enter the
following formula in G2 and copy down:

=AVERAGE(IF(($B$2:$B$12=F2)*(ISNUMBER($C$2:$C$12)),($C$2:$C$12-$A$2:$A$12
)))

entered using CTRL+SHIFT+ENTER

Hope this helps!