Multiply non-contigious arrays

[Rob Gould]

Hi all. Let's say that I have 10 columns and a row for each day of the
month. All odd columns contain a Dollar value and all even columns
contain a quantity. How can I multiply each Dollar amount by its
quantity and sum these for each day? An Array or sumproduct?

Example:
A1 = $2
B1 = 5
C1 = $5
D1 = 10

Answer in E1 must be 60 - (2*5 + 5*10)

Thanks.

Rob

 

[Jerry W. Lewis]

=60-(A1*B1+C1*D1)

If you are putting these formulas in E, it is not clear why you would
need either array or sumproduct formulas.

Jerry

 

[Rob Gould]

Thanks, but it's not that simple. The example I used was to give the
answer 60 - I see now that it appears that the 60 should be part of the
formula - sorry.. In my actual data, I have more than just 4 columns
and I want to be able to add more if need be. Yes, I could just
multiply and add as you have done in the bracketed section of your
formula, but I thought that arrays were meant to do that quicker (or at
least in a shorter formula).

 

[Jerry W. Lewis]

Array formulas can be faster in that they can allow you to avoid
repeating a calculation that is shared by several cells.

Array formulas can also allow you to perform complicated calculations
without using helper cells.

If my previous formula is not what you are looking for, then your
explanation of what you are trying to do is not adequate for me to
understand your intent sufficiently to suggest a formula.

Jerry

 

[Rob Gould]

Hi Jerry,

Sorry - let me try and explain clearer...

Example:
A1 = $2
B1 = 5
C1 = $5
D1 = 10

This goes on until COL AJ (15 multiplications added together), which is
why I don't really want a formula that just multiplies and adds
i.e.(A1*B1+C1*D1) - although it will work. In other words, is there a
shorter, more clever formula to use that will do the job?

Rob

 

[Jerry W. Lewis]

Much clearer, although I make that 18 multiplications instead of 15.

Try
=SUM(IF(MOD(COLUMN(B1:AJ1),2)=0,B1:AJ1,0)*IF(MOD(COLUMN(A1:AI1),2)=1,A1:AI1,0))
array entered (Ctrl-Shift-Enter, or Apple-enter if you have a Mac). If
you do it properly, the formula bar will then display
{=SUM(IF(MOD(COLUMN(B1:AJ1),2)=0,B1:AJ1,0)*IF(MOD(COLUMN(A1:AI1),2)=1,A1:AI1,0))}
even though you did not type the surrounding curly brackets. Note that
cell ranges are not identical in the two embedded IF()s; that is to the
values aligned that you want to multiply.

This would be much easier if you set it up as
A1 = $2
A2 = 5
B1 = $5
B2 = 10
....
In that case, you could simply use
=SUMPRODUCT(A1:R1,A2:R2)


OPEN QUESTION:

=SUM(IF(MOD(COLUMN(B11),2)=0,B11,0)*IF(MOD(COLUMN(A1:C1),2)=1,A1:C1,0))

returns 60, as expected, but

=SUMPRODUCT(IF(MOD(COLUMN(B11),2)=0,B11,0)*IF(MOD(COLUMN(A1:C1),2)=1,A1:C1,0))
=SUMPRODUCT(IF(MOD(COLUMN(B11),2)=0,B11,0),IF(MOD(COLUMN(A1:C1),2)=1,A1:C1,0))
=SUMPRODUCT(IF(MOD(COLUMN(B11),2)=0,B11),IF(MOD(COLUMN(A1:C1),2)=1,A1:C1))

all return 85 instead of the expected 60 (Excel 2002 SP-2).
Does anybody know why?

Jerry

 

[Rob Gould]

That works fine - thanks for all your help and patience Jerry.

 

[Jerry W. Lewis]

You're welcome. Glad it helped.

Jerry