This is probably an Array Formula?

[Dan the Man]

In Column C of my spreadhseet I have a listing of elapsed days (the
difference between a date established in Column A and Column B (where column
A is a discharge date, and column B is the file closing date). Below is the
formula which exists in Column C, and it works just fine:

=DATEDIF(MIN(A4,B4),MAX(A4,B4),"D"))

I am also using the average function (=AVERAGE) to calculate the average
days between discharge date, and file closing (identified in Column C) for
all clients, which also works just fine, and gives me information about the
"average" closing of files (from discharge to closing) on all client cases
posted on the spread sheet:

=AVERAGE(C4:C500)

For enhanced clarity in my overall analysis of data, I would also like to
use the "average" function to obtain information by "progam" and by
"counselor". For formula purposes, "Elapsed Days" are identified in Column C,
"Program" type is listed in Column F, and "Counselor" is listed in Column G.
The following are the formula outcomes by counselor and program that I'd like
to obtain:

1) What are the average days between discharge and file closing by program?
2) What are the average days between discharge and file closing by counselor?

I'll assume that the above 2 are "array" formulas, but I couldn't wrap
myself around how it should look with use of the "average" functcion. Perhaps
something like the following (which I got an error value for):

=Average(C4:C500)*(F4:F500,"OP")
=Average(C4:C500)*(F4:F500,"Jen")

The first formula above should provide the outcome by our various programs
(OP,TP,ARC,IOP), and the second formula should provide outcomes by the
various counselors (Jen, Ernest, Lynn, etc).

Any help would be appreciated!
Thanks in Advance,

Dan

 

[Don Guillett]

An array formula that must be entered using ctrl+shift+enter
=AVERAGE(IF(a1:m1>0,a1:m1))
=AVERAGE(IF(A2:A13="alex",B2:E13))
so
=Average(C4:C500)*(F4:F500,"OP")
=Average(if(F4:F500="OP",c4:c500))

 

[Dan the Man]

Thank You Don. I do know how to turn a formula into an Array, but I was
struggling with the specifics of the formulas themselves. You gave me what I
needed, and I appreciate that very much! Have a nice weekend.

Dan

 

[Don Guillett]

Glad it helped

 

[Dan the Man]

The formulas below that Don helped me with work PERFECTLY:

=AVERAGE(IF('Quarter 1 Data'!$G$4:$G$500="Allen",'Quarter 1
Data'!$C$4:$C$500))

=AVERAGE(IF('Quarter 1 Data'!$F$4:$F$500="OP",'Quarter 1 Data'!$C$4:$C$500))

I have one additional question however, and I'll use the following as an
example to describe what I am looking for:

If there is no input data yet for Row C or Row G, I would understadably see:
#DIV/0! in the cell referencing the first formula above (which I do). I
realize that as soon as I input any data into Rows C or G, the #DIV/0! error
will be replaced with the actual numeric data generated by the formula. This
would also be true of the second formula in the absence of any data in Rows C
or F.

Is there any way of using an IF statement to keep the cells blank, until the
relevant data is input. It's probably more of a cosmetic issue, but I just
hate seeing that "Division" error sign (even if the only assoicated error is
the lack of data in the specific cells requiring data).

Thanks!

Dan