Return an average date

C

Christy

I'm trying to do the following:
Col E Col H
9/17/08 in
9/18/08 ti
9/19/08 ot
When H13:H36 = in and H13:H36 = ti, Then return the average date from E13:E36.
Thanks in advance for any help.
Christy
 
P

Peo Sjoblom

This statement

"When H13:H36 = in and H13:H36 = ti"

can never be true, if you meant OR as opposed to AND

=AVERAGE(IF((B1:B3="in")+(B1:B3="ti"),A1:A3))

entered with ctrl + shift & enter will return 09/17/2008 using your example
because there are no half days etc, you would need to involve time and then
it will be 9/17/2008 12:00 but if you have many entries with "in" or "ti" it
will be strange.

Maybe you should explain what you are trying to do?

--


Regards,


Peo Sjoblom
 
T

Teethless mama

=INT(AVERAGE(IF((H13:H36={"in","ti"}),E13:E36)))

ctrl+shift+enter, not just enter
 
T

T. Valko

=INT(AVERAGE(IF((H13:H36={"in","ti"}),E13:E36)))

Using ROUND would be more accurate.
 
C

Christy

How would I alter this using ROUND? Also, when cells are blank I would like
it if the cell that contained this formula didn't say #DID/O! but was just
blank.
Thanks.
 
T

T. Valko

If you're getting #DIV/0! then that means there are no entries in column H
that meet the criteria. Try this array formula** :

=IF(SUM(COUNTIF(H13:H36,{"in","ti"})),ROUND(AVERAGE(IF(H13:H36={"in","ti"},E13:E36)),0),"")

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

If there are no dates in column E then the result will be 0 (formatted as
date will display as 1/0/1900)
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads


Top