If and offset

[pzulfugarli]

Hi,

I need to write function that will calculate the number of elements in the sample based on criteria linked to previous cell.
for example, we have sample:

1 -2 3 4 -3 6

is it possible to write any function that will count the number of elements which comes after negative in our case 3 and 6.
I wanted to use "countif" and put criteria with "offset" however as offset requires to reference particular cell.. this function didnot work.

Any usefull comments are highly appreciated!!

 

[ajm]

Hi,

I need to write function that will calculate the number of elements in the sample based on criteria linked to previous cell.
for example, we have sample:

1 -2 3 4 -3 6

is it possible to write any function that will count the number of elements which comes after negative in our case 3 and 6.
I wanted to use "countif" and put criteria with "offset" however as offset requires to reference particular cell.. this function didnot work.

Any usefull comments are highly appreciated!!

it might help your cause if you were to post a sample of your data so that the gurus can see exactly what you are working with. That way , they can tell if all characters are in the one cell, or each in its own cell, etc etc

if you are unfamiliar with how to post a snapshot of your data, have a look at the stickies at the start of the Excel Questions forum. check out the one called "How to display your sheet...."

http://www.mrexcel.com/forum/showthread.php?t=89356

 

[T. Valko]

Hi,

I need to write function that will calculate the number of elements in the sample based on criteria linked to previous cell.
for example, we have sample:

1 -2 3 4 -3 6

is it possible to write any function that will count the number of elements which comes after negative in our case 3 and 6.
I wanted to use "countif" and put criteria with "offset" however as offset requires to reference particular cell.. this function didnot work.

Any usefull comments are highly appreciated!!

Maybe this...

Book1
	A	B	C
2	-3	_	3
3	-1	_	_
4	0	_	_
5	0	_	_
6	5	_	_
7	-3	_	_
8	-4	_	_
9	-1	_	_
10	1	_	_
11	1	_	_
12	-1	_	_
13	2	_	_
14	2	_	_
15	-4	_	_
Sheet1

This formula entered in C2:

=SUMPRODUCT(--(A2:A14<0),--(A3:A15>=0))

Note how the ranges are offset.

 

[pzulfugarli]

Thank you for your quick reply, it really works..

Highly appreciated!!!!


Pasha

Maybe this...

Sheet1

<TABLE style="BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 2pt; PADDING-RIGHT: 2pt; FONT-FAMILY: Verdana,Arial; FONT-SIZE: 10pt" border=0 cellSpacing=0 cellPadding=0><COLGROUP><COL style="WIDTH: 30px; FONT-WEIGHT: bold"><COL style="WIDTH: 29px"><COL style="WIDTH: 29px"><COL style="WIDTH: 29px"></COLGROUP><TBODY><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt; FONT-WEIGHT: bold"><TD></TD><TD>A</TD><TD>B</TD><TD>C</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">2</TD><TD style="BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: right; BORDER-LEFT: #000000 1px solid; BORDER-TOP: #000000 1px solid; BORDER-RIGHT: #000000 1px solid">-3</TD><TD style="BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center; COLOR: #ffffff; BORDER-LEFT-COLOR: #000000; BORDER-TOP: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid">_</TD><TD style="BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center; BORDER-LEFT-COLOR: #000000; BORDER-TOP: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid">3</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">3</TD><TD style="BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: right; BORDER-LEFT: #000000 1px solid; BORDER-TOP-COLOR: #000000; BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid">-1</TD><TD style="BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center; BORDER-TOP-COLOR: #000000; BORDER-TOP-WIDTH: 1px; COLOR: #ffffff; BORDER-LEFT-COLOR: #000000; BORDER-LEFT-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid">_</TD><TD style="BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center; BORDER-TOP-COLOR: #000000; BORDER-TOP-WIDTH: 1px; COLOR: #ffffff; BORDER-LEFT-COLOR: #000000; BORDER-LEFT-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid">_</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">4</TD><TD style="BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: right; BORDER-LEFT: #000000 1px solid; BORDER-TOP-COLOR: #000000; BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid">0</TD><TD style="BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center; BORDER-TOP-COLOR: #000000; BORDER-TOP-WIDTH: 1px; COLOR: #ffffff; BORDER-LEFT-COLOR: #000000; BORDER-LEFT-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid">_</TD><TD style="BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center; BORDER-TOP-COLOR: #000000; BORDER-TOP-WIDTH: 1px; COLOR: #ffffff; BORDER-LEFT-COLOR: #000000; BORDER-LEFT-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid">_</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">5</TD><TD style="BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: right; BORDER-LEFT: #000000 1px solid; BORDER-TOP-COLOR: #000000; BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid">0</TD><TD style="BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center; BORDER-TOP-COLOR: #000000; BORDER-TOP-WIDTH: 1px; COLOR: #ffffff; BORDER-LEFT-COLOR: #000000; BORDER-LEFT-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid">_</TD><TD style="BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center; BORDER-TOP-COLOR: #000000; BORDER-TOP-WIDTH: 1px; COLOR: #ffffff; BORDER-LEFT-COLOR: #000000; BORDER-LEFT-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid">_</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">6</TD><TD style="BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: right; BORDER-LEFT: #000000 1px solid; BORDER-TOP-COLOR: #000000; BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid">5</TD><TD style="BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center; BORDER-TOP-COLOR: #000000; BORDER-TOP-WIDTH: 1px; COLOR: #ffffff; BORDER-LEFT-COLOR: #000000; BORDER-LEFT-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid">_</TD><TD style="BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center; BORDER-TOP-COLOR: #000000; BORDER-TOP-WIDTH: 1px; COLOR: #ffffff; BORDER-LEFT-COLOR: #000000; BORDER-LEFT-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid">_</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">7</TD><TD style="BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: right; BORDER-LEFT: #000000 1px solid; BORDER-TOP-COLOR: #000000; BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid">-3</TD><TD style="BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center; BORDER-TOP-COLOR: #000000; BORDER-TOP-WIDTH: 1px; COLOR: #ffffff; BORDER-LEFT-COLOR: #000000; BORDER-LEFT-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid">_</TD><TD style="BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center; BORDER-TOP-COLOR: #000000; BORDER-TOP-WIDTH: 1px; COLOR: #ffffff; BORDER-LEFT-COLOR: #000000; BORDER-LEFT-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid">_</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">8</TD><TD style="BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: right; BORDER-LEFT: #000000 1px solid; BORDER-TOP-COLOR: #000000; BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid">-4</TD><TD style="BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center; BORDER-TOP-COLOR: #000000; BORDER-TOP-WIDTH: 1px; COLOR: #ffffff; BORDER-LEFT-COLOR: #000000; BORDER-LEFT-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid">_</TD><TD style="BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center; BORDER-TOP-COLOR: #000000; BORDER-TOP-WIDTH: 1px; COLOR: #ffffff; BORDER-LEFT-COLOR: #000000; BORDER-LEFT-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid">_</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">9</TD><TD style="BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: right; BORDER-LEFT: #000000 1px solid; BORDER-TOP-COLOR: #000000; BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid">-1</TD><TD style="BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center; BORDER-TOP-COLOR: #000000; BORDER-TOP-WIDTH: 1px; COLOR: #ffffff; BORDER-LEFT-COLOR: #000000; BORDER-LEFT-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid">_</TD><TD style="BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center; BORDER-TOP-COLOR: #000000; BORDER-TOP-WIDTH: 1px; COLOR: #ffffff; BORDER-LEFT-COLOR: #000000; BORDER-LEFT-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid">_</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">10</TD><TD style="BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: right; BORDER-LEFT: #000000 1px solid; BORDER-TOP-COLOR: #000000; BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid">1</TD><TD style="BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center; BORDER-TOP-COLOR: #000000; BORDER-TOP-WIDTH: 1px; COLOR: #ffffff; BORDER-LEFT-COLOR: #000000; BORDER-LEFT-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid">_</TD><TD style="BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center; BORDER-TOP-COLOR: #000000; BORDER-TOP-WIDTH: 1px; COLOR: #ffffff; BORDER-LEFT-COLOR: #000000; BORDER-LEFT-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid">_</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">11</TD><TD style="BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: right; BORDER-LEFT: #000000 1px solid; BORDER-TOP-COLOR: #000000; BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid">1</TD><TD style="BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center; BORDER-TOP-COLOR: #000000; BORDER-TOP-WIDTH: 1px; COLOR: #ffffff; BORDER-LEFT-COLOR: #000000; BORDER-LEFT-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid">_</TD><TD style="BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center; BORDER-TOP-COLOR: #000000; BORDER-TOP-WIDTH: 1px; COLOR: #ffffff; BORDER-LEFT-COLOR: #000000; BORDER-LEFT-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid">_</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">12</TD><TD style="BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: right; BORDER-LEFT: #000000 1px solid; BORDER-TOP-COLOR: #000000; BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid">-1</TD><TD style="BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center; BORDER-TOP-COLOR: #000000; BORDER-TOP-WIDTH: 1px; COLOR: #ffffff; BORDER-LEFT-COLOR: #000000; BORDER-LEFT-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid">_</TD><TD style="BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center; BORDER-TOP-COLOR: #000000; BORDER-TOP-WIDTH: 1px; COLOR: #ffffff; BORDER-LEFT-COLOR: #000000; BORDER-LEFT-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid">_</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">13</TD><TD style="BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: right; BORDER-LEFT: #000000 1px solid; BORDER-TOP-COLOR: #000000; BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid">2</TD><TD style="BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center; BORDER-TOP-COLOR: #000000; BORDER-TOP-WIDTH: 1px; COLOR: #ffffff; BORDER-LEFT-COLOR: #000000; BORDER-LEFT-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid">_</TD><TD style="BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center; BORDER-TOP-COLOR: #000000; BORDER-TOP-WIDTH: 1px; COLOR: #ffffff; BORDER-LEFT-COLOR: #000000; BORDER-LEFT-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid">_</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">14</TD><TD style="BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: right; BORDER-LEFT: #000000 1px solid; BORDER-TOP-COLOR: #000000; BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid">2</TD><TD style="BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center; BORDER-TOP-COLOR: #000000; BORDER-TOP-WIDTH: 1px; COLOR: #ffffff; BORDER-LEFT-COLOR: #000000; BORDER-LEFT-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid">_</TD><TD style="BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center; BORDER-TOP-COLOR: #000000; BORDER-TOP-WIDTH: 1px; COLOR: #ffffff; BORDER-LEFT-COLOR: #000000; BORDER-LEFT-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid">_</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">15</TD><TD style="BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: right; BORDER-LEFT: #000000 1px solid; BORDER-TOP-COLOR: #000000; BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid">-4</TD><TD style="BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center; BORDER-TOP-COLOR: #000000; BORDER-TOP-WIDTH: 1px; COLOR: #ffffff; BORDER-LEFT-COLOR: #000000; BORDER-LEFT-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid">_</TD><TD style="BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center; BORDER-TOP-COLOR: #000000; BORDER-TOP-WIDTH: 1px; COLOR: #ffffff; BORDER-LEFT-COLOR: #000000; BORDER-LEFT-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid">_</TD></TR></TBODY></TABLE>


This formula entered in C2:

=SUMPRODUCT(--(A2:A14<0),--(A3:A15>=0))

Note how the ranges are offset.

 

[T. Valko]

Thank you for your quick reply, it really works..

Highly appreciated!!!!


Pasha

You're welcome. Thanks for the feedback!

See this for an explanation on the use of the double unary "--" :

http://xldynamic.com/source/xld.SUMPRODUCT.html