Excel - Formulas
One way:
Use a cell to hold the department name that you'll want to average, say D1.
With department in B2 to B100,
And days in C2 to C100,
Try this:
=Sumif(B2:B100,D1,C2:C100)/Countif(B2:B100,D1)
--
HTH,
RD
---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Mike" wrote in message
...
Bob
I don't think this works. I don't have a specfic "Report Name" that I am
searching, only a "Department name"...here is an example:
Report ID DEPT # OF DAYS
5xt Admin 2
5xz HR 5
4rv Admin 7
I just want to average the amount of days for each department - so for
example average the Admin Department above...does this make sense?
"Bob Phillips" wrote:
=AVERAGE(IF((report_range="report
name")*(dept_range="department_name"),days_range))
which is an array formula, it should be committed with Ctrl-Shift-Enter,
not
just Enter.
Excel will automatically enclose the formula in braces (curly brackets),
do
not try to do this manually.
When editing the formula, it must again be array-entered.
--
---
HTH
Bob
(there's no email, no snail mail, but somewhere should be gmail in my
addy)
"Mike" wrote in message
...
Ok. I am having trouble devising a formula for the following problem:
I have a table that has a column that identifies a report name (key), a
column entitled DEPARTMENT (report is assigned to), and the number of
days
it
took for the report to be completed.
How can I average the amount of days it took reports to be completed
for
each department, without actually sorting the table and creating a new
table?
Is there some LOOKUP formula or ARRAY formula that I can use to look
for a
specific DEPARTMENT name, take a number in adajcent cell and add it
into
an
average function for the rest of the cells that correspond to that
department
name?
Thanks for any help.
|