Multiply Corresponding Values in Range

[Chad]

Hi All

I have picked up a very heavy spreadsheet with formula like that
listed below. All it is doing is multiplying a cell by a
corresponding rate. Only two or three cells in a range have values in
them, the rest contain zero. I was thinking of getting a custom
function to Multiply those cells with values above zero with the
corresponding rate.

=(D7*D$2)+(E7*E$2)+(F7*F$2)+(G7*G$2)+(H7*H$2)+(I7*I$2)+(J7*J$2)+(K7*K
$2)+(L7*L$2)+(M7*M$2)+(N7*N$2)+(O7*O$2)+(P7*P$2)+(Q7*Q$2)+(R7*R$2)+
(S7*S$2)+(T7*T$2)+(U7*U$2)+(V7*V$2)+(W7*W$2)+(X7*X$2)+(Y7*Y$2)+(Z7*Z
$2)+(AA7*AA$2)+(AB7*AB$2)+(AC7*AC$2)+(AD7*AD$2)+(AE7*AE$2)+(AF7*AF$2)+
(AG7*AG$2)+(AH7*AH$2)+(AI7*AI$2)+(AJ7*AJ$2)+(AK7*AK$2)+(AL7*AL$2)+
(AM7*AM$2)+(AN7*AN$2)+(AO7*AO$2)+(AP7*AP$2)+(AQ7*AQ$2)+(AR7*AR$2)+
(AS7*AS$2)+(AT7*AT$2)+(AU7*AU$2)+(AV7*AV$2)+(AW7*AW$2)+(AX7*AX$2)+
(AY7*AY$2)+(AZ7*AZ$2)+(BA7*BA$2)
Yes very long and very clunky.

The following function does half what I want but the bit where is say
' Multiplied by Corresponding Element rate I can't get to work. Needs
an offset or a second range to refer to.

Function SumAb(Rng As Range) As Double
Dim Element As Variant
Dim Result As Double

Result = 0
On Error GoTo Done
For Each Element In Rng
Result = Result + Element ' Multiplied by Corresponding
Element rate
Next Element
Done:
SumAb = Result
End Function


Can anyone out there help with extending this?

Chad

 

[Jim Cone]

Range("B2").Value = Application.WorksheetFunction.SumProduct(Range("D7:BA7"), Range("D2:BA2"))
--
Jim Cone
Portland, Oregon USA
http://www.contextures.com/excel-sort-addin.html
(30 + ways to sort in Excel)
..
..
..

"Chad" <[email protected]>
wrote in message
Hi All

I have picked up a very heavy spreadsheet with formula like that
listed below. All it is doing is multiplying a cell by a
corresponding rate. Only two or three cells in a range have values in
them, the rest contain zero. I was thinking of getting a custom
function to Multiply those cells with values above zero with the
corresponding rate.

=(D7*D$2)+(E7*E$2)+(F7*F$2)+(G7*G$2)+(H7*H$2)+(I7*I$2)+(J7*J$2)+(K7*K
$2)+(L7*L$2)+(M7*M$2)+(N7*N$2)+(O7*O$2)+(P7*P$2)+(Q7*Q$2)+(R7*R$2)+
(S7*S$2)+(T7*T$2)+(U7*U$2)+(V7*V$2)+(W7*W$2)+(X7*X$2)+(Y7*Y$2)+(Z7*Z
$2)+(AA7*AA$2)+(AB7*AB$2)+(AC7*AC$2)+(AD7*AD$2)+(AE7*AE$2)+(AF7*AF$2)+
(AG7*AG$2)+(AH7*AH$2)+(AI7*AI$2)+(AJ7*AJ$2)+(AK7*AK$2)+(AL7*AL$2)+
(AM7*AM$2)+(AN7*AN$2)+(AO7*AO$2)+(AP7*AP$2)+(AQ7*AQ$2)+(AR7*AR$2)+
(AS7*AS$2)+(AT7*AT$2)+(AU7*AU$2)+(AV7*AV$2)+(AW7*AW$2)+(AX7*AX$2)+
(AY7*AY$2)+(AZ7*AZ$2)+(BA7*BA$2)
Yes very long and very clunky.

The following function does half what I want but the bit where is say
' Multiplied by Corresponding Element rate I can't get to work. Needs
an offset or a second range to refer to.

Function SumAb(Rng As Range) As Double
Dim Element As Variant
Dim Result As Double

Result = 0
On Error GoTo Done
For Each Element In Rng
Result = Result + Element ' Multiplied by Corresponding
Element rate
Next Element
Done:
SumAb = Result
End Function
Can anyone out there help with extending this?
Chad

 

[Chad]

Hi Jim

When turned into normal excel formula this is a cracking solution.
Well done Jim! Thanks so much for your help.

Take care

Chad

 

[Jim Cone]

You are welcome.
--
Jim Cone

..
..

"Chad" <[email protected]>
wrote in message
Hi Jim
When turned into normal excel formula this is a cracking solution.
Well done Jim! Thanks so much for your help.
Take care
Chad