Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel - Formulas
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. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel - Formulas
=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. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel - Formulas
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. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
CELLS NOT CALC FORMULAS - VALUES STAY SME FORMULAS CORRECT?? HELP | Excel Worksheet Functions | |||
Copying formulas from Excel 2003 to Excel 2007 | Excel Discussion (Misc queries) | |||
Formulas not evaluated, Formulas treated as strings | Excel Discussion (Misc queries) | |||
Excel formulas | Excel Worksheet Functions | |||
Excel formulas | Excel Worksheet Functions |