Thread: AVERAGE IF Function with Date and Characteristic

Hello there!

I am new to the forum and I am seeking guidance regarding a function for a spreadsheet I am working on. I will attempt to make this as easy as possible in explaining something I am looking to do. I am looking to Calculate the average lifespan of an individual based upon

1) They are "dead" (A status,either "Alive" or "Dead" found in Data!F2:F653)
2) They were born between 1/1/2007 and 12/31/2007 (Delivery Date provided in Data!C2:C653)


The lifespans were provided on Data!E2:E653

If someone could help me out that would be wonderful! If you have any other questions, feel free to ask!

Eric

Hmm, I tried that Function and got a result of zero, so here is the best I can provide (Note, the functions are on a separate sheet, so I added Data! before each cell range, I hope that didn't mess it up):


Delivery (C2:C653) Age (Days E2:E653) Status (F2:F653)

9/25/2007 85 Alive
12/5/2007 54 Dead
12/20/2007 66 Dead
12/20/2007 7 Alive
11/8/2007 56 Dead
12/28/2007 49 Dead
12/20/2007 14 Dead
12/17/2007 107 Dead
12/28/2007 14 Dead
12/5/2007 33 Alive
9/21/2007 62 Dead



Hope this is enough data to figure something out, to note, there are many points of delivery dates from 2006 and 2008.

Thanks so much!

A workbook, please?

I wish I could, but for legal reasons, I can only provide you with a fascimile w/ changes, here is the best I can do:

I created named ranges and used this formula:

=SUMPRODUCT( (Delivery >= "1/1/2007" + 0) * (Delivery <= "12/31/2007 "+ 0) * (Status = "Dead"), Age) /
SUMPRODUCT( (Delivery >= "1/1/2007" + 0) * (Delivery <= "12/31/2007" + 0) * (Status = "Dead") )

.. to get a result of 49.27... days.

That sounds right to me, Thank you soo much!!!

can you post the fascimile workbook so I can put that into my database?

Thanks
Eric

Attached ...

I attempted a conversion when adding to a separate "Report" Sheet on my database, with no luck:

=SUMPRODUCT( (Data!C2:C653 >= "1/1/2007" + 0) * (Data!C2:C653 <= "12/31/2007 "+ 0) * (Data!F2:F653 = "Dead"), Data!E2:E653) /
SUMPRODUCT( (Data!C2:C653 >= "1/1/2007" + 0) * (Data!C2:C653 <= "12/31/2007" + 0) * (Data!F2:F653 = "Dead") )


Am I missing something?

Thanks!
Eric

Dunno, I haven't seen the other sheet.

Off to bed -- I or someone else will help later.

Got it to work! Thanks so much!!

Good job, you're welcome.

Alright, I have returned with another question. I have been able to extrapolate the earlier formula to apply to alot of the work I am doing, however I have one more calculation I am trying to obtain, which is a "death rate" of sorts. If possible, I would like to avoid the COUNTIFS function, in order for non-Microsoft 2007 users can obtain this data. My paramaters are such:

If they were delivered b/t 1/15/2008 and 12/1/2008 AND if they are "Dead", I would like to divide that by the Total Population delivered b/t 1/15/2008 and 12/1/2008 in order to get the average of the dead individuals.

Any guidance would be greatly appreciated.

Thanks so much!
Eric