Multiply text values with numeric substitutions

**jheddings** · 07-11-2009, 04:15 PM

Hello-

I have created a simple cash flow Excel sheet to look at income and expenses. It doesn't do much, but I'm having trouble finding the right formula to complete it.

Since expenses and income happen at different intervals, I created a "Weights" tab where the frequency of a transaction maps to a monthly weighting. For example, getting paid twice a month would get a weighting of 2, and a bill that takes place ever other month would get a weighting of 0.5. I'm using a list to select the frequency when I enter the transactions, but I'd like to multiply the given amount by the weight and sum the weighted values on my "Summary" tab. I'd like to avoid a new column on the "Income" and "Expenses" tabs (which I know works, but it doesn't look as nice).

Basically, the formula would amount to SUMPRODUCT(A:A, C:C) if I could get the text in column A to be replaced by it's corresponding weight in column C.

I've attached a worksheet with sample values. Any ideas?

**oldchippy** · 07-11-2009, 04:40 PM

Hi,

Welcome to the forum, does this work for you?

In B1

=Income!C1*VLOOKUP(Income!A1,Weights!$A$2:$B$9,2,FALSE)

In B2

=Expenses!C1*VLOOKUP(Expenses!A1,Weights!$A$2:$B$9,2,FALSE)
+Expenses!C2*VLOOKUP(Expenses!A2,Weights!$A$2:$B$9,2,FALSE)
+Expenses!C3*VLOOKUP(Expenses!A3,Weights!$A$2:$B$9,2,FALSE)
+Expenses!C4*VLOOKUP(Expenses!A4,Weights!$A$2:$B$9,2,FALSE)