This is probably an Array Formula?
 

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

This is probably an Array Formula?
 

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

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Dan the Man" wrote in message
...
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

This is probably an Array Formula?
 

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" wrote:


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

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Dan the Man" wrote in message
...
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

This is probably an Array Formula?
 

Glad it helped

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Dan the Man" wrote in message
...
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" wrote:


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

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Dan the Man" wrote in message
...
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

This is probably an Array Formula?
 

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


"Don Guillett" wrote:


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

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Dan the Man" wrote in message
...
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