Multiply 1 Cell by Every Cell in Column and Add up Result

[deebster]

I've got a list of 1 value each showing how much someone has been underpaid for every month going back to May 2006. What I need to do is take this value and multiply it by a factor on another worksheet to get to the current value of this underpayment as of now, repeat the process for each subsequent month's factor, then total up all the multiplications into one lump sum, placing the result next to the basic underpayment figure.

The underpayment figure is constant but the factor changes depending on the time difference between the date of underpayment and now.

So, in table form it's like this

Worksheet 1
A1 = 100.00 (static recurring monthly underpayment)
B1 = Required place for result of total underpayment calculation

Worksheet 2
C1 = 1.1445 (factor to apply to underpayment from May 2006)
C2 = 1.1419 (factor to apply to underpayment from June 2006)
C3 = 1.1382 (factor to apply to underpayment from July 2006)

... and so on up to July 2011.

I could write a long and ugly multiplication formula such as (A1*C1)+(A1*C2)+(A1*C3) but I'm sure there's a far sexier way than that, I just can't find it.

Added to that some of the underpayments don't go back as far as May 2006 or continue to as recently as now, so ideally I'd like a way to dictate a range of factors to multiply against and sum, based on the earliest and latest underpayment date. I can live without this bit though.

Can anyone help me out here please?

 

[mirabeau]

If you're not averse to using VBA, the following VBA code will multiply all entries in Column C by the value in cell A1.

The multiplied entries are in Column E and the sum of these in Column F.

Sub multiply()
Dim x&, col
x = Cells(Rows.Count, "c").End(3).Row
col = Evaluate(Range("A1").Address & "*" & Range("C1:C" & x).Address)
Range("E1").Resize(x) = col
Range("F1") = Application.Sum(col)
End Sub