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

=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 wrote:

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

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 wrote:
=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 wrote:

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

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 wrote:

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 wrote:

=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 wrote:


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

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 wrote:
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 wrote:

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 wrote:

=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 wrote:


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

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(C OLUMN(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(B1:D1),2)=0,B1:D1,0)*IF(MOD(COL UMN(A1:C1),2)=1,A1:C1,0))

returns 60, as expected, but

=SUMPRODUCT(IF(MOD(COLUMN(B1:D1),2)=0,B1:D1,0)*IF( MOD(COLUMN(A1:C1),2)=1,A1:C1,0))
=SUMPRODUCT(IF(MOD(COLUMN(B1:D1),2)=0,B1:D1,0),IF( MOD(COLUMN(A1:C1),2)=1,A1:C1,0))
=SUMPRODUCT(IF(MOD(COLUMN(B1:D1),2)=0,B1:D1),IF(MO D(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 wrote:

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 wrote:

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 wrote:


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 wrote:


=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 wrote:



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

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

Jerry W. Lewis wrote:
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(C OLUMN(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(B1:D1),2)=0,B1:D1,0)*IF(MOD(COL UMN(A1:C1),2)=1,A1:C1,0))

returns 60, as expected, but


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

=SUMPRODUCT(IF(MOD(COLUMN(B1:D1),2)=0,B1:D1,0),IF( MOD(COLUMN(A1:C1),2)=1,A1:C1,0))

=SUMPRODUCT(IF(MOD(COLUMN(B1:D1),2)=0,B1:D1),IF(MO D(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 wrote:

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 wrote:

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 wrote:


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 wrote:


=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 wrote:



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

You're welcome. Glad it helped.

Jerry

Rob Gould wrote:

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