Posted to microsoft.public.excel.worksheet.functions
|
|
Formula for sum of alternate cells
Hi Mike,
This formula help me also for sum of the products at alternate columns. I just wanna know the impact of '--' in a formula for better understanding...
Thnx,
Vibhor
On Thursday, September 25, 2008 5:29 AM JohnBlackwel wrote:
Folks,
I'm trying to find a formula for summing the contents of alternate cells in
a row?
I have a large workbook and want to calculate the contents of row 23 - i.e.
E23, G23, I23, K23, M23 etc etc, up to KK23. Is there a simple formula for
this?
John Blackwell
On Thursday, September 25, 2008 5:41 AM Mike wrote:
John,
=SUMPRODUCT(--(MOD(COLUMN(E23:KK23),2)=1),E23:KK23)
Mike
"John Blackwell" wrote:
On Thursday, September 25, 2008 5:44 AM Ashish Mathur wrote:
Hi,
Please array enter (Ctrl+Shift+Enter) the following formula
SUM(IF((MOD(COLUMN(E23:I23),2)<0),E23:I23))
What this formula does is that if the column number/2<0, then it sums up
the numbers from those columns.
--
Regards,
Ashsih Mathur
Microsoft Excel MVP
www.ashishmathur.com
"John Blackwell" wrote in message
...
On Thursday, September 25, 2008 6:06 AM JohnBlackwel wrote:
Fantastic Mike - thank you.
Sorry to be a pain, but how do I sum the other cells - i.e. D23, F23, H23,
J23, L23 etc etc, up to KK23?
John
"Mike H" wrote:
On Thursday, September 25, 2008 6:13 AM Mike wrote:
Hi,
Glad I could help for d23 etc use this
=SUMPRODUCT(--(MOD(COLUMN(D23:KK23),2)=0),D23:KK23)
Note all Ive done is change the range and altered the modulus to zero.
Columns D, F etc are even numbered so for D the formula days 2 mod 2 = 0
Mike
"John Blackwell" wrote:
On Thursday, September 25, 2008 10:31 AM HARSHAWARDHANSSHASTR wrote:
Mike,
What is the significance of -- in formula.
Harshawardhan Shastri
"Mike H" wrote:
On Thursday, September 25, 2008 12:59 PM David Biddulph wrote:
For an explanation of the double unary minus, see
http://www.mcgimpsey.com/excel/formulae/doubleneg.html
--
David Biddulph
"HARSHAWARDHAN. S .SHASTRI"
On Thursday, September 25, 2008 1:06 PM Lor wrote:
You could also try these two:
=SUM(NPV({0,-2},D23:K23))/2
=SUM(NPV({0,-2},,D23:K23))/2
"John Blackwell" wrote:
On Thursday, September 25, 2008 1:17 PM Peo Sjoblom wrote:
Clever. How do you find these Lori?
--
Regards,
Peo Sjoblom
On Thursday, September 25, 2008 3:49 PM Lor wrote:
Thanks Peo, i make them up :-) i figure there are many functions out there
which can be put to good use as long as you're aware of the limitations.
NPV is a good example - other useful values for rate a
1/-0.5: for a binary sum
9/-0.9 : for a decimal sum
Big/small: for the first or last value
eg: =NPV(1e20,A1:K1)*1e20
returns the first non-blank value in the row. Or...in Excel 2007 (cse):
=NPV(-0.9,,IFERROR(MID(A1,51-ROW(1:50),1)%,""))&""
extracts the numeric portion of a string such as
"apple 123 banana 345 pear 567 orange 678" - 123345567678
"Peo Sjoblom" wrote:
On Thursday, September 25, 2008 4:28 PM Peo Sjoblom wrote:
Thanks for the info Lori, very interesting.
I love the last one and I know how to get
numbers from a string as long as they are in one place.
--
Regards,
Peo Sjoblom
On Monday, July 26, 2010 2:52 AM Punnoose Mammen wrote:
Can someone please help me with a excel work sheet formula for sum total of even number cells if the odd number cells value is 1 or 0.
Example:1 if a1=1,a3=1,a5=0,a7=1,a9=0,a11=1, then a2+a4+a8+a12
Example:2 if a1=1,a3=1,a5=0,a7=1,a9=0,a11=1, then a6+a10
|