Counting with dates and other criteria

[Roman]

Hoping you can help me.

I have Column A with a start date and Column B with an end date. Column C
has "New". Column D has the name "Jane". Column E has the number of days
between A and B. I want to:

1. See the average number of days (e1:e50) for Jane only
2. See the average number of days (e1:e50) for only Jane that are only New

 

[T. Valko]

Try these array formulas** :

=AVERAGE(IF(D150="Jane",E1:E50))

=AVERAGE(IF((C1:C50="new")*(D150="Jane"),E1:E50))

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

 

[Roman]

Thanks...That worked.

Cheers

Try these array formulas** :

=AVERAGE(IF(D150="Jane",E1:E50))

=AVERAGE(IF((C1:C50="new")*(D150="Jane"),E1:E50))

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

 

[T. Valko]

You're welcome. Thanks for the feedback!

 

[Roman]

What happens in this formula if there is no number in e1:e:50. Does the
average skip it or does it take a 0. If so, how would this formula look
different if you ask it to skip cells that are blank or have "0".
Thanks

 

[T. Valko]

To exclude 0 and empty cells:

Array entered

=AVERAGE(IF((C1:C50="new")*(D150="Jane")*(E1:E50<>0),E1:E50))

 

[Roman]

2 questions:

1. Somehow my numbers do not add up using the formula you recommended.

When I manually do the avg 276/6=46.
blank
57
107
22
0
55
blank
12
Blank
14

When I do the formula
=AVERAGE(IF('Domestic #''s '!C3:C152="Valarie B.", ('Domestic #''s '!P3152
<>0),'Domestic #''s '!P3152)) I get 59 when hitting enter and 26 when I
do cntrl,shift,enter

What am I not seeing? Thank you for educating me....

2. when you have 2 dates and you want to know the days in-between I use
=b1-a1. But when b1 is blank a negative number appears. is there a better
formula? or can I put something that if a2 is blank to put "0" otherwise put
the number between the 2 cells.

Thanks

 

[T. Valko]

=AVERAGE(IF('Domestic #''s '!C3:C152="Valarie B.", ('Domestic #''s

'!P3152
<>0),'Domestic #''s '!P3152))

Your formula syntax doesn't look anything like the one I suggested, now does
is it?

Try it like this (array entered):

=AVERAGE(IF(('Domestic #''s '!C3:C152="Valarie B.")*('Domestic #''s
'!P3152 <>0),'Domestic #''s '!P3152))

For the date subtraction question, one way is to test and make sure there
are 2 entries:

=IF(COUNT(A1:B1)<2,0,B1-A1)

 

[Roman]

No it does not..... Everything now works. Thank you for your help and
patience...

 

[T. Valko]

You're welcome. Thanks for the feedback!