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. |
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. |
All times are GMT +1. The time now is 11:43 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com