Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formulas help
I need to do summary tables for worksheets and am having trouble.
My home data: SqFt YR OLP LP SP COE 3,067 2000 899,000 750,000 760,000 7/11/2007 3,067 1999 1,499,000 1,499,000 1,300,000 4/24/2007 2,975 1999 1,150,000 1,150,000 1,150,000 4/15/2006 2,413 1991 845,000 799,000 760,000 3/4/2008 I need to count how many "homes" close in a year, i.e., count number for 2007, then need to avg sp for all homes that had a coe in 2007. How do I write a statement that looks for a coe with the same year then averages the sp for closings in that year. Thanks. I am ready to pull my hair out. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formulas help
To count the number of homes sold in 2007, use:
=SUMPRODUCT(--(YEAR(F2:F1000)=2007)) Adjust your range to suit. Better yet, put 2007 into a cell (like H1), so you can change it at will, and use: =SUMPRODUCT(--(YEAR(F2:F1000)=H1)) Assuming this is in H2, to get the average SP: =SUMPRODUCT(--(YEAR(F2:F1000)=2007),E2:E1000)/H2 If you want to make life easier for you, and impress your friends and co-workers, look into Pivot Tables. They will do everything you want, and more. Regards, Fred. "Stacey" wrote in message ... I need to do summary tables for worksheets and am having trouble. My home data: SqFt YR OLP LP SP COE 3,067 2000 899,000 750,000 760,000 7/11/2007 3,067 1999 1,499,000 1,499,000 1,300,000 4/24/2007 2,975 1999 1,150,000 1,150,000 1,150,000 4/15/2006 2,413 1991 845,000 799,000 760,000 3/4/2008 I need to count how many "homes" close in a year, i.e., count number for 2007, then need to avg sp for all homes that had a coe in 2007. How do I write a statement that looks for a coe with the same year then averages the sp for closings in that year. Thanks. I am ready to pull my hair out. |
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 | |||
lookup formulas dependent upon lookup formulas | Excel Worksheet Functions | |||
automatically copy formulas down columns or copy formulas all the | Excel Worksheet Functions | |||
Formulas not evaluated, Formulas treated as strings | Excel Discussion (Misc queries) | |||
formulas for changing formulas? | Excel Discussion (Misc queries) |