Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Multiply Corresponding Values in Range
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)+(A E7*AE$2)+(AF7*AF$2)+ (AG7*AG$2)+(AH7*AH$2)+(AI7*AI$2)+(AJ7*AJ$2)+(AK7*A K$2)+(AL7*AL$2)+ (AM7*AM$2)+(AN7*AN$2)+(AO7*AO$2)+(AP7*AP$2)+(AQ7*A Q$2)+(AR7*AR$2)+ (AS7*AS$2)+(AT7*AT$2)+(AU7*AU$2)+(AV7*AV$2)+(AW7*A W$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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Multiply Corresponding Values in Range
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" 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)+(A E7*AE$2)+(AF7*AF$2)+ (AG7*AG$2)+(AH7*AH$2)+(AI7*AI$2)+(AJ7*AJ$2)+(AK7*A K$2)+(AL7*AL$2)+ (AM7*AM$2)+(AN7*AN$2)+(AO7*AO$2)+(AP7*AP$2)+(AQ7*A Q$2)+(AR7*AR$2)+ (AS7*AS$2)+(AT7*AT$2)+(AU7*AU$2)+(AV7*AV$2)+(AW7*A W$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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Multiply Corresponding Values in Range
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Multiply Corresponding Values in Range
You are welcome.
-- Jim Cone .. .. "Chad" 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Multiply values from VLookup | Excel Worksheet Functions | |||
how do i make a formula to multiply two values? | Excel Worksheet Functions | |||
Multiply range values with Lookup-value? | Excel Programming | |||
Multiply all values by 10 | Excel Discussion (Misc queries) | |||
Multiply All values by a cell | Excel Programming |