| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Array with Sum and multiply
Wondering if there is a way to use an array formula for this?
I'm trying to multiply data for several months (data arranged in columns) by values contained in one column. In the example below I want to multiply .5 by 100, 125 and 75, .7 by 100, 125 and 75, etc. Then take the sum of these products. I was thinking {sum(b1:d6*a2:a6)} would work, but it returns an error. Multiplier Jan Feb Mar 0.5 100 125 75 0.7 100 125 75 0.9 100 125 75 0.4 100 125 75 0.3 100 125 75 Any help on this is greatly appreciated. Thanks |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Array with Sum and multiply
Change B1 to B2
"Eric" wrote: Wondering if there is a way to use an array formula for this? I'm trying to multiply data for several months (data arranged in columns) by values contained in one column. In the example below I want to multiply .5 by 100, 125 and 75, .7 by 100, 125 and 75, etc. Then take the sum of these products. I was thinking {sum(b1:d6*a2:a6)} would work, but it returns an error. Multiplier Jan Feb Mar 0.5 100 125 75 0.7 100 125 75 0.9 100 125 75 0.4 100 125 75 0.3 100 125 75 Any help on this is greatly appreciated. Thanks |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Array with Sum and multiply
Yes, sorry B1 should have been B2 in my question, but still need help on this.
"Joel" wrote: Change B1 to B2 "Eric" wrote: Wondering if there is a way to use an array formula for this? I'm trying to multiply data for several months (data arranged in columns) by values contained in one column. In the example below I want to multiply .5 by 100, 125 and 75, .7 by 100, 125 and 75, etc. Then take the sum of these products. I was thinking {sum(b1:d6*a2:a6)} would work, but it returns an error. Multiplier Jan Feb Mar 0.5 100 125 75 0.7 100 125 75 0.9 100 125 75 0.4 100 125 75 0.3 100 125 75 Any help on this is greatly appreciated. Thanks |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Array with Sum and multiply
Multipliers are in col A then
put this formula in Col E =SUM(A2*{100;125;75}) and drag it down On Dec 3, 11:51*pm, Eric wrote: Wondering if there is a way to use an array formula for this? I'm trying to multiply data for several months (data arranged in columns) by values contained in one column. *In the example below I want to multiply .5 by 100, 125 and 75, .7 by 100, 125 and 75, etc. Then take the sum of these products. *I was thinking {sum(b1:d6*a2:a6)} would work, but it returns an error. Multiplier * * * * * * *Jan * * Feb * * Mar 0.5 * * * * * * 100 * * 125 * * 75 0.7 * * * * * * 100 * * 125 * * 75 0.9 * * * * * * 100 * * 125 * * 75 0.4 * * * * * * 100 * * 125 * * 75 0.3 * * * * * * 100 * * 125 * * 75 Any help on this is greatly appreciated. Thanks |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Array with Sum and multiply
You can use either SUM or SUMPRODUCT. When you use SUM you need the equal
sign and DON'T type the Curly bracket. These must be enter by typying Shift- Cntl- Enter "Eric" wrote: Yes, sorry B1 should have been B2 in my question, but still need help on this. "Joel" wrote: Change B1 to B2 "Eric" wrote: Wondering if there is a way to use an array formula for this? I'm trying to multiply data for several months (data arranged in columns) by values contained in one column. In the example below I want to multiply .5 by 100, 125 and 75, .7 by 100, 125 and 75, etc. Then take the sum of these products. I was thinking {sum(b1:d6*a2:a6)} would work, but it returns an error. Multiplier Jan Feb Mar 0.5 100 125 75 0.7 100 125 75 0.9 100 125 75 0.4 100 125 75 0.3 100 125 75 Any help on this is greatly appreciated. Thanks |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Array with Sum and multiply
try this
=SUM(SUM(A1:A5)*{100;125;75}) On Dec 4, 12:13*am, muddan madhu wrote: Multipliers are in col A then put this formula in Col E =SUM(A2*{100;125;75}) and drag it down On Dec 3, 11:51*pm, Eric wrote: Wondering if there is a way to use an array formula for this? I'm trying to multiply data for several months (data arranged in columns) by values contained in one column. *In the example below I want to multiply .5 by 100, 125 and 75, .7 by 100, 125 and 75, etc. Then take the sum of these products. *I was thinking {sum(b1:d6*a2:a6)} would work, but it returns an error. Multiplier * * * * * * *Jan * * Feb * * Mar 0.5 * * * * * * 100 * * 125 * * 75 0.7 * * * * * * 100 * * 125 * * 75 0.9 * * * * * * 100 * * 125 * * 75 0.4 * * * * * * 100 * * 125 * * 75 0.3 * * * * * * 100 * * 125 * * 75 Any help on this is greatly appreciated. Thanks |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Array with Sum and multiply
try this
=SUMPRODUCT((B2:D2)*A2+(B3:D3)*A3+(B4:D4)*A4+(B5:D 5)*A5+(B6:D6)*A6) Is this what you want? Pls click Yes if this help cheers "Eric" wrote: Wondering if there is a way to use an array formula for this? I'm trying to multiply data for several months (data arranged in columns) by values contained in one column. In the example below I want to multiply .5 by 100, 125 and 75, .7 by 100, 125 and 75, etc. Then take the sum of these products. I was thinking {sum(b1:d6*a2:a6)} would work, but it returns an error. Multiplier Jan Feb Mar 0.5 100 125 75 0.7 100 125 75 0.9 100 125 75 0.4 100 125 75 0.3 100 125 75 Any help on this is greatly appreciated. Thanks |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Array with Sum and multiply
What about this way...
=SUMPRODUCT(A2:A6*B2:D6) -- Rick (MVP - Excel) "Eric" wrote in message ... Wondering if there is a way to use an array formula for this? I'm trying to multiply data for several months (data arranged in columns) by values contained in one column. In the example below I want to multiply .5 by 100, 125 and 75, .7 by 100, 125 and 75, etc. Then take the sum of these products. I was thinking {sum(b1:d6*a2:a6)} would work, but it returns an error. Multiplier Jan Feb Mar 0.5 100 125 75 0.7 100 125 75 0.9 100 125 75 0.4 100 125 75 0.3 100 125 75 Any help on this is greatly appreciated. Thanks |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Array with Sum and multiply
The function posted will work if the B1 is changed to B2. Smproduct works
much simplier than your example =sumproduct(b1:d6*a2:a6) "xlmate" wrote: try this =SUMPRODUCT((B2:D2)*A2+(B3:D3)*A3+(B4:D4)*A4+(B5:D 5)*A5+(B6:D6)*A6) Is this what you want? Pls click Yes if this help cheers "Eric" wrote: Wondering if there is a way to use an array formula for this? I'm trying to multiply data for several months (data arranged in columns) by values contained in one column. In the example below I want to multiply .5 by 100, 125 and 75, .7 by 100, 125 and 75, etc. Then take the sum of these products. I was thinking {sum(b1:d6*a2:a6)} would work, but it returns an error. Multiplier Jan Feb Mar 0.5 100 125 75 0.7 100 125 75 0.9 100 125 75 0.4 100 125 75 0.3 100 125 75 Any help on this is greatly appreciated. Thanks |
|
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Array with Sum and multiply
Thanks! xlmate's formula did work, but I have many more rows than I
illustrated in my example, so Joel, your formula will work very nicely. Thank you both for your help. "Joel" wrote: The function posted will work if the B1 is changed to B2. Smproduct works much simplier than your example =sumproduct(b1:d6*a2:a6) "xlmate" wrote: try this =SUMPRODUCT((B2:D2)*A2+(B3:D3)*A3+(B4:D4)*A4+(B5:D 5)*A5+(B6:D6)*A6) Is this what you want? Pls click Yes if this help cheers "Eric" wrote: Wondering if there is a way to use an array formula for this? I'm trying to multiply data for several months (data arranged in columns) by values contained in one column. In the example below I want to multiply .5 by 100, 125 and 75, .7 by 100, 125 and 75, etc. Then take the sum of these products. I was thinking {sum(b1:d6*a2:a6)} would work, but it returns an error. Multiplier Jan Feb Mar 0.5 100 125 75 0.7 100 125 75 0.9 100 125 75 0.4 100 125 75 0.3 100 125 75 Any help on this is greatly appreciated. Thanks |
|
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Array with Sum and multiply
"Rick Rothstein" wrote...
What about this way... =SUMPRODUCT(A2:A6*B2:D6) Or =SUMPRODUCT(A2:A6,B2:D6) |
|
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Array with Sum and multiply
I get a #VALUE! error with the comma form of the formula that you posted (whereas the multiplication form I posted works fine for me).
-- Rick (MVP - Excel) "Harlan Grove" wrote in message ... "Rick Rothstein" wrote... What about this way... =SUMPRODUCT(A2:A6*B2:D6) Or =SUMPRODUCT(A2:A6,B2:D6) |
|
#13
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Array with Sum and multiply
"Rick Rothstein" wrote...
I get a #VALUE! error with the comma form of the formula that you posted (whereas the multiplication form I posted works fine for me). My fault. I missed that the second spanned columns B to D. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Prevent cell/array references from changing when altering/moving thecell/array | Excel Discussion (Misc queries) | |||
| add value of 4 cells, multiply by 3 subtract 72 multiply by 80% | Excel Discussion (Misc queries) | |||
| meaning of : IF(Switch; Average(array A, array B); array A) | Excel Worksheet Functions | |||
| combining cells and array from different sheets into an array to pass to IRR() | Excel Discussion (Misc queries) | |||
| How to multiply all cells in array by factor | Excel Discussion (Misc queries) | |||
Linear Mode
