what the hell?

  • Thread starter Thread starter gonzalez
  • Start date Start date
G

gonzalez

I am trying to do the simple thing - summarize the column's in every
third cell (in row 2,5,8, etc) starting from row 2. My expression is

=SUMPRODUCT((MOD(ROW($C$2:$C$178);3)=2)*($C$2:$C$178))

i tried all the ways - excelljust does not calculate it.. the result
is always 0... I think that it's excell's bug, because on the other
W2K, Office 2000 - it works ok. How can I wite this expression other
way so it will work on older versions of office..

Thanks!
 
The formula works fine. The bug is that your data was input as Text, not
numbers.

One way to coerce text to numbers: Copy an empty cell, select C2:C178.
Choose Edit/Paste Special, selecting the Add and Values radio buttons.
Click OK.
 
Thanks a lot. I hanged cells format to "number" and all works fine now
on my Office 97.
 
I'm sorry to disturb you with my problem again, but I looked at the
wrong place, and actually the cell format change had no effect...
Excell still doesnt calculate this formula.

=SUMPRODUCT((MOD(ROW($C$2:$C$178);3)=2)*($C$2:$C$178))

It is as simle as summarize every 2nd, 5th, 8th, etc.. row... Is there
any workaround? Can I write this formula otherway? Or to meka some
button to force excell to calculate it.. Thanks a lot.
 
The posted formula is equivalent to

=C2+C5+C8+C11+C14+C17+C20+C23+C26+C29+C32+C35+C38+C41+C44+C47+C50+C53
+C56+C59+C62+C65+C68+C71+C74+C77+C80+C83+C86+C89+C92+C95+C98+C101+C104
+C107+C110+C113+C116+C119+C122+C125+C128+C131+C134+C137+C140+C143+C146
+C149+C152+C155+C158+C161+C164+C167+C170+C173+C176

but (assuming that ";" is the correct argument separator based on your
regional settings) there is no reason that the posted formula should not
work in any version of Excel from 4.0 through 2003. If Excel is
returning the formula text instead of evaluating the formula, then the
cell contains a text constant instead of a live formula. It is not
sufficient to just change the cell format. You must then re-enter the
formula to change the cell contents from text to something else
(changing the format does not change the value in the cell). It is not
necessary to re-type in order to re-enter; you can simply click on the
formula in the formula bar and press enter.

Jerry
 
Back
Top