Select every other cell in a row for a formula

[cdpar]

Hi,

I have a row of numbers with more than 100 cells. I need to "Select" every other cell to use in a formula.

Example: SUM(A1+A3+A5+A7 ... GA1+GA3)

How can I automatically have Excel give me all of the cell addresses for every other cell in the row?

The number of cells in the row changes from week to week so I have to modify the resulting formula each week.

Thank you!

 

[James006]

Hello,

Your example is not very clear ...

To sum every other row ... you can test the following array formula :

=Sum(If(mod(row($A$1:$A$50),2)=0,$A$1:$A$50,0))

Hope this will help

 

[cdpar]

Hi,

I was told my previous question was not clear, so I am reposting:

• I have a single row of numbers in 100 cells (A1:A100)
• I want to "Select" every other cell (Odd) to use in a formula to place in cell A150.
• Currently I add the formula "=Sum(A1, A3, A5, A7, ... A99)"

Here is my challenge: Each week the number of cells in Row A can increase or decrease by more than 20 figures.

Currently I manually edit the formula in Cell A150 by either adding or removing the cell references for the additional cells. So my new formula might be "=Sum(A1, A3, A5, A7, ... A101, A103, A105, A107, A109)"

Is there a function I can add to my formula which will automatically add or subtract the cells in the new row so that I do not have to spend my time editing the references?

Please help. I hope this is clearer.

Thanks!

Chuck

 

[steve the fish]

This will do the odd numbered rows:

=SUMPRODUCT(A1:A100,MOD(ROW(A1:A100),2))

 

[Fluff]

How about

Excel 2013/2016
	A	B
1	1
2	2	8
3	3
4	4
5	1
6	2
7	3
8	4
Sheet1

Cell Formulas
Range	Formula
B2	{=SUM(IF(ISODD(ROW($A$1:$A$50)),$A$1:$A$50))}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[cdpar]

Hi,

I must not be making myself clear. I have ONLY ONE ROW.

I don't need the "odd" rows, I need the "odd" columns within 1 (one) row.

Row 1 = A1, A2, A3, A4, A5, A6, A7, A8, A9, A10

I need to sum A1, A3, A5, A7, A9 and I need the resulting formula to show the references such as "=Sum(A1, A3, A5, A7, A9)"

 

[Fluff]

A1, A2, A3 etc are cell references for different rows in Column A.
If you have A1, A2 etc as values in row1 then your sum formula will return Zero as they are not numeric values.
If you have values in A1, B1, C1 etc then try

Excel 2013/2016
	A	B	C	D	E	F	G	H
1	1	2	3	4	1	2	3	4
2		8
Sheet1

Cell Formulas
Range	Formula
B2	{=SUM(IF(ISODD(COLUMN($A1:$Z1)),$A$1:$Z$1))}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[cdpar]

Using your example (above), I would like the Formula in cell B2 to show the references, rather than the sum:

Instead of "8" "1A, 1C, 1E, !H"

Is that possible?

 

[Fluff]

If you have a 365 subscription you could look at TextJoin, which might help.
Why do you want the cell reference?

 

[cdpar]

My range of columns changes each week. Sometimes I have 100 columns in the one row, other weeks I have 130 columns, etc. Currently I am having to edit the formula each week to add or remove the columns that are new this week.