I'm trying to create a formula that will give me the Average number of Days
Activities early (-) or late(+) based upon the due date. Column B represents
the dye date and column C is the actual date delivered. In the example there
may be dates in column B and none in column C these are to ignored. I also
need the count of Activities thsat are early and those that are late. I have
thousands of rows so I don't want to add a formuls in another column that
determines each one separatly and than sum them up. I need to do it in one
step.

A B C
Act 1 2/3/09 2/5/09
Act 2 6/7/09 7/6/09
Act 3 1/5/09 3/4/09
Act 4 4/3/09 8/9/09

Robert K

Try these:

=SUMPRODUCT(--(B1:B100C1:C100),--(C1:C100<""))

to give you a count of earlies, and this:

=SUMPRODUCT(--(B1:B100<C1:C100),--(C1:C100<""))

to give you a count of lates (adjust the ranges to suit).

If you want the total number of days that are early you can use:

=SUMPRODUCT(--(B1:B100C1:C100),--(C1:C100<""),(B1:B100-C1:C100))

and the total number of late days is given by:

=SUMPRODUCT(--(B1:B100<C1:C100),--(C1:C100<""),(C1:C100-B1:B100))

Averages can be obtained by dividing the totals by the counts.

Hope this helps.

Pete

On Sep 18, 11:10*pm, RobertK
wrote:
I'm trying to create a formula that will give me the Average number of Days
Activities early (-) or late(+) based upon the due date. *Column B represents
the dye date and column C is the actual date delivered. *In the example there
may be dates in column B and none in column C these are to ignored. I also
need the count of Activities thsat are early and those that are late. *I have
thousands of rows so I don't want to add a formuls in another column that
determines each one separatly and than sum them up. I need to do it in one
step.

A * * * * *B * * * * * * * *C
Act * * * 1 2/3/09 * * *2/5/09
Act 2 * *6/7/09 * * * * 7/6/09
Act * * * 3 1/5/09 * * *3/4/09
Act 4 * *4/3/09 * * * * 8/9/09

Robert K

Thanks Pete, that did the trick.
--
Robert K


"Pete_UK" wrote:

Try these:

=SUMPRODUCT(--(B1:B100C1:C100),--(C1:C100<""))

to give you a count of earlies, and this:

=SUMPRODUCT(--(B1:B100<C1:C100),--(C1:C100<""))

to give you a count of lates (adjust the ranges to suit).

If you want the total number of days that are early you can use:

=SUMPRODUCT(--(B1:B100C1:C100),--(C1:C100<""),(B1:B100-C1:C100))

and the total number of late days is given by:

=SUMPRODUCT(--(B1:B100<C1:C100),--(C1:C100<""),(C1:C100-B1:B100))

Averages can be obtained by dividing the totals by the counts.

Hope this helps.

Pete

On Sep 18, 11:10 pm, RobertK
wrote:
I'm trying to create a formula that will give me the Average number of Days
Activities early (-) or late(+) based upon the due date. Column B represents
the dye date and column C is the actual date delivered. In the example there
may be dates in column B and none in column C these are to ignored. I also
need the count of Activities thsat are early and those that are late. I have
thousands of rows so I don't want to add a formuls in another column that
determines each one separatly and than sum them up. I need to do it in one
step.

A B C
Act 1 2/3/09 2/5/09
Act 2 6/7/09 7/6/09
Act 3 1/5/09 3/4/09
Act 4 4/3/09 8/9/09

Robert K

You're welcome, Robert - thanks for feeding back.

Pete

On Sep 19, 2:50*am, RobertK wrote:
Thanks Pete, that did the trick.
--
Robert K



"Pete_UK" wrote:
Try these:

=SUMPRODUCT(--(B1:B100C1:C100),--(C1:C100<""))

to give you a count of earlies, and this:

=SUMPRODUCT(--(B1:B100<C1:C100),--(C1:C100<""))

to give you a count of lates (adjust the ranges to suit).

If you want the total number of days that are early you can use:

=SUMPRODUCT(--(B1:B100C1:C100),--(C1:C100<""),(B1:B100-C1:C100))

and the total number of late days is given by:

=SUMPRODUCT(--(B1:B100<C1:C100),--(C1:C100<""),(C1:C100-B1:B100))

Averages can be obtained by dividing the totals by the counts.

Hope this helps.

Pete

On Sep 18, 11:10 pm, RobertK
wrote:
I'm trying to create a formula that will give me the Average number of Days
Activities early (-) or late(+) based upon the due date. *Column B represents
the dye date and column C is the actual date delivered. *In the example there
may be dates in column B and none in column C these are to ignored. I also
need the count of Activities thsat are early and those that are late. *I have
thousands of rows so I don't want to add a formuls in another column that
determines each one separatly and than sum them up. I need to do it in one
step.

A * * * * *B * * * * * * * *C
Act * * * 1 2/3/09 * * *2/5/09
Act 2 * *6/7/09 * * * * 7/6/09
Act * * * 3 1/5/09 * * *3/4/09
Act 4 * *4/3/09 * * * * 8/9/09

Robert K- Hide quoted text -

- Show quoted text -