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]

 

[xeven_]

 

[Peter_SSs]

It isn't clear to me.

1. In post 2, are you trying to calculate the figures in column G, or column H, or both?

2. In your original post you mentioned consecutive positive or negative, but your screen shot only seems to be about positives. Please clarify.

3. Would it matter if the single positive (or negative) value rows were also calculated?

 

[wideboydixon]

Excel 2016 (Windows) 32 bit
	F	G	H
1	1.00%
2	-0.46%
3	0.38%
4	-0.02%
5	0.02%
6	0.99%
7	1.16%
8	0.09%
9	0.47%	2.73%	5
10	-0.04%
11	0.94%
12	0.13%	1.07%	2
13	-4.39%
14	-0.31%
15	-0.69%
16	-0.90%
17	-0.48%
18	0.05%
19	0.50%	0.55%	2
Sheet1

Cell Formulas
Range	Formula
G1	=IF($H1="","",SUM(OFFSET($F1,1-$H1,0,$H1)))
H1	=IF($F1<0,"",IF(OR($F2<0,$F2=""),IF(ROW()>1,IF(OFFSET($F1,-1,0)<0,"",ROW()-LOOKUP(2,1/($F$1:$F1<0),ROW($F$1:$F1))),""),""))

WBD

 

[xeven_]

Peter..yes I should have been more precise. Primarily calculate the clustered percent changes in G and the days in H as shown in the screen shot. I want to do the negatives as well but on the right hand side of the page. so the final hoped for result will be able to do this. See screen shot below:

 

[xeven_]

Wideboydixon that is awesome.

Hey I tried to color code it via the conditional formatting and it it turning even my empty cells green. I wonder if there is a ways to make it not do that?

 

[xeven_]

 

[xeven_]

Also it is tallying/counting all highlighted cells.Is there a way to make it not do that? See screenshot:

 

[wideboydixon]

Select a contiguous range and use a formula to format the cells. For example, I selected $F$1:$H$19 and used this formula:

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

WBD

 

[xeven_]

I dont understand how to enter it. sorry I am still learning this stuff