Sumproduct on Table with two criteria

[Matthew]

I'm trying to calculate the weighted average interested rate for a subset of
the loans in my table. For example, only loans for the month of May.

I can easily calculate the weighted average for the entire table:

weighted average =
sumproduct(Table[Loan_Value],Table[Interest_Rate])/sum(Table[Loan_Value])

Assume the field in the table with the loan date is called "Loan_Date". How
do I modify my sumproduct to calculate the weighted averages for only those
loans with month(Loan_Date) = specific month?

Thanks,

 

[Herbert Seidenberg]

Excel 2007 Tables
Weighted average interest rate and
using your indicated Structured References.
http://www.mediafire.com/file/geczzfmdfiz/08_25_09b.xlsx

 

[Matthew]

Thank you. That worked.

Add-on question: I have another field in my table Loan_Code (e.g., 50, 100,
110, etc.) And I have a table which maps Loan_Code to Loan_Type (e.g.,
Government, Conventional, etc.). How would I modify the weighted average to
only use loans of a specific type?

Unfortunately, the Loan_Code to Loan_Type is random. E.g., 50 could be
Government, 100 could be Conventional, 110 could be Government, etc.

I can easily get the Loan_Type from the Loan_Code using Vlookup, but I
cannot figure out how to use Vlookup in a sumproduct calculation.

Also, adding a field to the table isn't really an option, as the Table is an
ODBC pull from another database.

Thanks!

--
Matthew

I'm trying to calculate the weighted average interested rate for a subset
of the loans in my table. For example, only loans for the month of May.

I can easily calculate the weighted average for the entire table:

weighted average =
sumproduct(Table[Loan_Value],Table[Interest_Rate])/sum(Table[Loan_Value])

Assume the field in the table with the loan date is called "Loan_Date".
How do I modify my sumproduct to calculate the weighted averages for only
those loans with month(Loan_Date) = specific month?

Thanks,

Try this out:

=SUMPRODUCT((DATE(YEAR(Loan_Date),MONTH(Loan_Date),1)=Z2)*Loan_Value*Interest_Rate)/SUMPRODUCT((DATE(YEAR(Loan_Date),MONTH(Loan_Date),1)=Z2)*Loan_Value)

Where Z2 is the cell that contains the date (month) you want to check, and
is of the form (not format) mm/1/yyyy.