Every other column ratioale

  • Thread starter Thread starter GerryK
  • Start date Start date
G

GerryK

Hi

In D2 I have
=SUMPRODUCT((MOD(COLUMN($I$2:INDEX(2:2,32))-CELL
("Col",$I$2)+0,2)=0)*($I$2:INDEX(2:2,32)))
which I thought would sum every other column from H2 up to
BS2 (or a total of 32 entries on an 'every other column
basis').
But the last entry the formula sums or returns is at AE2,
(12 sums of the required 32).

By adjusting 32 to 74 takes me to BS2!

I am looking for why this is so as I'll need the logic for
another piece of my workbook!

TIA
 
Because it takes a field width of 74 to yield 32 entries
when choosing every other one???

If you have 100 pennies lined up in a ROW and you select
every other ('column') one, how many will you get? = 50!
If you have 100 pennies lined up in a ROW and you select
every other one BUT LIMIT YOUR FIELD (horizontal width) to
50 pennies, how many will you get? = 25

That's my observation... I'll dig and see if I can come up
with an Excel reason.

GL
 
The $I$2:INDEX(2:2,32) bit means the range from I2 to the 32th column in row
2. The 32th native column is AF. Thus we get:

I2:AF2.

The formula wil sum I2,K2,M2,O2,Q2..., that is, every second value in
I2:AF2.

Also,

$I$2:INDEX(2:2,74)

means

I2:BV2.

INDEX is an accessor function, that is, it can return a value at some
specified position in a range or it can return a reference in a range
specifying expression like $I$2:INDEX(2:2,32).
 
Back
Top