calculate consecutive days

[xeven_]

I have % data in column F. I want to calculate only the consecutive positive or negative days adjacently in column G as shown in the illustration, Is there a formula I can enter as I have to go back quite a number of years/days and this is very time consuming doing it manually.
Thanks in advance.


[TABLE="width: 201"]
<colgroup><col width="67" style="width:51pt"> <col width="67" style="width:51pt"> <col width="67" style="width:51pt"> </colgroup><tbody>[TR]
[TD="class: xl84, width: 67"]45[/TD]
[TD="class: xl83, width: 67"]1.00%[/TD]
[TD="class: xl83, width: 67"]1.02%[/TD]
[/TR]
[TR]
[TD="class: xl84"]45[/TD]
[TD="class: xl83"]-0.46%[/TD]
[TD="class: xl83"] [/TD]
[/TR]
[TR]
[TD="class: xl84"]45[/TD]
[TD="class: xl83"]0.38%[/TD]
[TD="class: xl83"] [/TD]
[/TR]
[TR]
[TD="class: xl84"]45[/TD]
[TD="class: xl83"]-0.02%[/TD]
[TD="class: xl83"] [/TD]
[/TR]
[TR]
[TD="class: xl84"]45[/TD]
[TD="class: xl83"]0.02%[/TD]
[TD="class: xl83"] [/TD]
[/TR]
[TR]
[TD="class: xl84"]46[/TD]
[TD="class: xl83"]0.99%[/TD]
[TD="class: xl83"] [/TD]
[/TR]
[TR]
[TD="class: xl84"]46[/TD]
[TD="class: xl83"]1.16%[/TD]
[TD="class: xl83"] [/TD]
[/TR]
[TR]
[TD="class: xl84"]46[/TD]
[TD="class: xl83"]0.09%[/TD]
[TD="class: xl83"] [/TD]
[/TR]
[TR]
[TD="class: xl84"]47[/TD]
[TD="class: xl83"]0.47%[/TD]
[TD="class: xl83"]2.73%[/TD]
[/TR]
[TR]
[TD="class: xl84"]47[/TD]
[TD="class: xl83"]-0.04%[/TD]
[TD="class: xl83"] [/TD]
[/TR]
[TR]
[TD="class: xl84"]47[/TD]
[TD="class: xl83"]0.94%[/TD]
[TD="class: xl83"] [/TD]
[/TR]
[TR]
[TD="class: xl84"]47[/TD]
[TD="class: xl83"]0.13%[/TD]
[TD="class: xl83"]1.07%[/TD]
[/TR]
[TR]
[TD="class: xl84"]45[/TD]
[TD="class: xl83"]-4.39%[/TD]
[TD="class: xl83"] [/TD]
[/TR]
[TR]
[TD="class: xl84"]45[/TD]
[TD="class: xl83"]-0.31%[/TD]
[TD="class: xl83"] [/TD]
[/TR]
[TR]
[TD="class: xl84"]45[/TD]
[TD="class: xl83"]-0.69%[/TD]
[TD="class: xl83"] [/TD]
[/TR]
[TR]
[TD="class: xl84"]44[/TD]
[TD="class: xl83"]-0.90%[/TD]
[TD="class: xl83"] [/TD]
[/TR]
[TR]
[TD="class: xl84"]44[/TD]
[TD="class: xl83"]-0.48%[/TD]
[TD="class: xl83"] [/TD]
[/TR]
[TR]
[TD="class: xl84"]44[/TD]
[TD="class: xl83"]0.05%[/TD]
[TD="class: xl83"] [/TD]
[/TR]
[TR]
[TD="class: xl84"]44[/TD]
[TD="class: xl83"]0.50%[/TD]
[TD="class: xl83"]0.55%[/TD]
[/TR]
[TR]
[TD="class: xl84"] [/TD]
[TD="class: xl82"] [/TD]
[TD="class: xl83"] [/TD]
[/TR]
[TR]
[TD="class: xl84"] [/TD]
[TD="class: xl82"] [/TD]
[TD="class: xl83"] [/TD]
[/TR]
</tbody>[/TABLE]

 

[wideboydixon]

Select cells F1:H19 (for example) and go to Conditional Formatting > New Rule where you select Use a formula. This is where you enter the formula above and then select the appropriate formatting (green in this case). Replace F1 in the formula with the first cell in your selection so, for example, if you selected F5:H100 use:

=AND(F5>0,F5<>"")

WBD

 

[Peter_SSs]

I have another suggestion without using the volatile function OFFSET. My suggestion does assume that there is at least one header row above the actual data.

For the values in columns G:J, try the cell formulas shown below, copied down.

For the Conditional Formatting, select from cell F2 down to K?? and enter the Conditional Formatting formulas shown.

Excel Workbook
	F	G	H	I	J	K
1	Value	Sum	Count	Count	Sum	Value
2	0.02%					0.02%
3	1.00%	1.02%	2			1.00%
4	-0.46%					-0.46%
5	0.38%					0.38%
6	-0.02%					-0.02%
7	0.02%					0.02%
8	0.99%					0.99%
9	1.16%					1.16%
10	0.09%					0.09%
11	0.47%	2.73%	5			0.47%
12	-0.04%					-0.04%
13	0.94%					0.94%
14	0.13%	1.07%	2			0.13%
15	-4.39%					-4.39%
16	-0.31%					-0.31%
17	-0.69%					-0.69%
18	-0.90%					-0.90%
19	-0.48%			5	-6.77%	-0.48%
20	0.05%					0.05%
21	0.50%	0.55%	2			0.50%
22
Consecutive

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
F2	1. / Formula is =AND($F2<0,N(F2))	Abc
F2	2. / Formula is =AND($F2>0,N(F2))	Abc

 

[xeven_]

Peter that is just about perfect!!! Hey is there anyway to make the empty or blank cells not count when highlighting them? As it is the bottom information bar gives me the correct average but gives me a false count as in counting even the cells that are blank but contains the formula.

Thanks again (AWESOME HELP)

 

[Peter_SSs]

Peter that is just about perfect!!! Hey is there anyway to make the empty or blank cells not count when highlighting them? As it is the bottom information bar gives me the correct average but gives me a false count as in counting even the cells that are blank but contains the formula.

That is because the cells are not blank. A formula cannot leave a cell truly blank - the best it can do is put a null string ("") in it, which is what the formula does.
"Count:" at the bottom right is meant to count all cells that have anything in them, so it is giving the correct count in that regard.

Right click in that bottom right information area and you should have a choice to tick "Numerical Count" or perhaps "Count Numbers" and that should give the count that you are looking for

 

[xeven_]

all the way awesome thank you!!!!!!!

 

[Peter_SSs]

all the way awesome thank you!!!!!!!

Glad to help.