# Multiply Corresponding Values in Range

C

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
(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?

J

#### Jim Cone

Range("B2").Value = Application.WorksheetFunction.SumProduct(Range("D7:BA7"), Range("D2:BA2"))
--
Jim Cone
Portland, Oregon USA
(30 + ways to sort in Excel)
..
..
..

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
(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?

C

Hi Jim

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

Take care

J

You are welcome.
--
Jim Cone

..
..