formula to add up EVERY OTHER cell in a row
 

Can I set up a formula in an xls to add up EVERY OTHER cell in a row? And how
long can a string in a formula be? (are there limitations?)

I'm getting an error when I manually type in the list of cells to add up.

Try the following formula. Change the cell range as desired.

=SUM(A1:A10*MOD(A1:A10,2))

A cell can contain up to 32K characters, although only about 1000
will display unless some trickery is used.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com




"Task Lead Nicole"
wrote in message
...
Can I set up a formula in an xls to add up EVERY OTHER cell in
a row? And how
long can a string in a formula be? (are there limitations?)

I'm getting an error when I manually type in the list of cells
to add up.

Can you explain what that formula means?
Sum of cells A1 thru A10, then what?

Thank you!

"Chip Pearson" wrote:

Try the following formula. Change the cell range as desired.

=SUM(A1:A10*MOD(A1:A10,2))

A cell can contain up to 32K characters, although only about 1000
will display unless some trickery is used.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com




"Task Lead Nicole"
wrote in message
...
Can I set up a formula in an xls to add up EVERY OTHER cell in
a row? And how
long can a string in a formula be? (are there limitations?)

I'm getting an error when I manually type in the list of cells
to add up.

So, using your formula, with my range, here's what I came up with and Excel
yelled at me -- it's not generating the correct result.

=SUM(E8:DI8*MOD(E8:DI8,2))

Do I need different parentheses or something?

"Chip Pearson" wrote:

Try the following formula. Change the cell range as desired.

=SUM(A1:A10*MOD(A1:A10,2))

A cell can contain up to 32K characters, although only about 1000
will display unless some trickery is used.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com




"Task Lead Nicole"
wrote in message
...
Can I set up a formula in an xls to add up EVERY OTHER cell in
a row? And how
long can a string in a formula be? (are there limitations?)

I'm getting an error when I manually type in the list of cells
to add up.

One way normally entered

=SUMPRODUCT(--(MOD(COLUMN(E8:DI8),2)=1),E8:DI8)

will sum E8, G8, I8 and so on


Regards,

Peo Sjoblom

"Nicole L." wrote:

So, using your formula, with my range, here's what I came up with and Excel
yelled at me -- it's not generating the correct result.

=SUM(E8:DI8*MOD(E8:DI8,2))

Do I need different parentheses or something?

"Chip Pearson" wrote:

Try the following formula. Change the cell range as desired.

=SUM(A1:A10*MOD(A1:A10,2))

A cell can contain up to 32K characters, although only about 1000
will display unless some trickery is used.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com




"Task Lead Nicole"
wrote in message
...
Can I set up a formula in an xls to add up EVERY OTHER cell in
a row? And how
long can a string in a formula be? (are there limitations?)

I'm getting an error when I manually type in the list of cells
to add up.

So far, that has worked - Thanks!
But what do the double-hyphens mean?
Can you translate the whole string for me, verbatim?
I just like to know, so I can tweak in future for future reference, ya know.

Thanks again.

"Peo Sjoblom" wrote:

One way normally entered

=SUMPRODUCT(--(MOD(COLUMN(E8:DI8),2)=1),E8:DI8)

will sum E8, G8, I8 and so on


Regards,

Peo Sjoblom

"Nicole L." wrote:

So, using your formula, with my range, here's what I came up with and Excel
yelled at me -- it's not generating the correct result.

=SUM(E8:DI8*MOD(E8:DI8,2))

Do I need different parentheses or something?

"Chip Pearson" wrote:

Try the following formula. Change the cell range as desired.

=SUM(A1:A10*MOD(A1:A10,2))

A cell can contain up to 32K characters, although only about 1000
will display unless some trickery is used.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com




"Task Lead Nicole"
wrote in message
...
Can I set up a formula in an xls to add up EVERY OTHER cell in
a row? And how
long can a string in a formula be? (are there limitations?)

I'm getting an error when I manually type in the list of cells
to add up.

To sum every 2nd number in a vertical range...

=SUMPRODUCT(--(MOD(ROW(A3:A200)-CELL("Row",A3)+0,2)=0),A3:A200)

To sum every 2nd number in a horizontal range...

=SUMPRODUCT(--(MOD(COLUMN(A3:Z3)-CELL("Col",A3)+0,2)=0),A3:Z3)

Task Lead Nicole wrote:
Can I set up a formula in an xls to add up EVERY OTHER cell in a row? And how
long can a string in a formula be? (are there limitations?)

I'm getting an error when I manually type in the list of cells to add up.

Take a look at http://www.xldynamic.com/source/xld.SUMPRODUCT.html

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Nicole L." wrote in message
...
So far, that has worked - Thanks!
But what do the double-hyphens mean?
Can you translate the whole string for me, verbatim?
I just like to know, so I can tweak in future for future reference, ya
know.

Thanks again.

"Peo Sjoblom" wrote:

One way normally entered

=SUMPRODUCT(--(MOD(COLUMN(E8:DI8),2)=1),E8:DI8)

will sum E8, G8, I8 and so on


Regards,

Peo Sjoblom

"Nicole L." wrote:

So, using your formula, with my range, here's what I came up with and
Excel
yelled at me -- it's not generating the correct result.

=SUM(E8:DI8*MOD(E8:DI8,2))

Do I need different parentheses or something?

"Chip Pearson" wrote:

Try the following formula. Change the cell range as desired.

=SUM(A1:A10*MOD(A1:A10,2))

A cell can contain up to 32K characters, although only about 1000
will display unless some trickery is used.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com




"Task Lead Nicole"
wrote in message
...
Can I set up a formula in an xls to add up EVERY OTHER cell in
a row? And how
long can a string in a formula be? (are there limitations?)

I'm getting an error when I manually type in the list of cells
to add up.

Nicole,

I should have added that this is an array formula. When you type
in the formula, you must press CTRL+SHIFT+ENTER rather than just
Enter. If you do this properly, Excel will display the formula
enclosed in curly braces.

The formula works by creating two array. The first is simply A1,
A2, A3, ... A10. The second is the result of MOD(A1,2), MOD(A2,
2), MOD(A3,2)...MOD(A10,2). The MOD function with an argument of
2 will return either a 0 if the number is even or 1 if the number
is odd. Multiplying these two arrays together (multiplying each
argument in the first array by the corresponding element in the
second array) returns an array like A1*1, A2*0, A3*1,...A10*0.
The SUM function simply sums these elements.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com



"Nicole L." wrote in message
...
Can you explain what that formula means?
Sum of cells A1 thru A10, then what?

Thank you!

"Chip Pearson" wrote:

Try the following formula. Change the cell range as desired.

=SUM(A1:A10*MOD(A1:A10,2))

A cell can contain up to 32K characters, although only about
1000
will display unless some trickery is used.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com




"Task Lead Nicole"
wrote in message
...
Can I set up a formula in an xls to add up EVERY OTHER cell
in
a row? And how
long can a string in a formula be? (are there limitations?)

I'm getting an error when I manually type in the list of
cells
to add up.