How can I count consecutive cells with positive or negative values in?

Albert_

New Member
Joined
Sep 27, 2014
Messages
2
Dear Experts,

I have a table with daily values that are updated Monday to Friday.

I would like to have a colum where I can see how many consecutive days with positive values or negative values are in.

The table that I work with is daily updated with positive and negative values .

In a separate column I would like to see how many consecutive days with negative or positve values has this week/month.

Only consecutive days.

i.e. if Monday =0.3,Tuesday= 1.3,Wednesday =0.7 has positive values in a separate column should reflect as = 3

How can I count the consecutive days with positive or negative values in?

Any help will be very appreciated.
Thank you.
 
Last edited:

Some videos you may like

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.

MickG

MrExcel MVP
Joined
Jan 9, 2008
Messages
14,841
For Data in column "A", try this for results in column "B"
Code:
[COLOR=Navy]Sub[/COLOR] MG27Sep55
[COLOR=Navy]Dim[/COLOR] Rng [COLOR=Navy]As[/COLOR] Range
[COLOR=Navy]Dim[/COLOR] Dn [COLOR=Navy]As[/COLOR] Range
[COLOR=Navy]Dim[/COLOR] c [COLOR=Navy]As[/COLOR] [COLOR=Navy]Long[/COLOR]
[COLOR=Navy]Dim[/COLOR] cc [COLOR=Navy]As[/COLOR] [COLOR=Navy]Long[/COLOR]
[COLOR=Navy]Set[/COLOR] Rng = Range(Range("A1"), Range("A" & Rows.Count).End(xlUp))
[COLOR=Navy]For[/COLOR] [COLOR=Navy]Each[/COLOR] Dn [COLOR=Navy]In[/COLOR] Rng
    [COLOR=Navy]If[/COLOR] Dn > 0 [COLOR=Navy]Then[/COLOR]
         c = c + 1
    [COLOR=Navy]Else[/COLOR]
         [COLOR=Navy]If[/COLOR] Not c = 0 [COLOR=Navy]Then[/COLOR] Dn.Offset(-1, 1) = c & " +"
       c = 0
    [COLOR=Navy]End[/COLOR] If
[COLOR=Navy]    If[/COLOR] Dn < 0 [COLOR=Navy]Then[/COLOR]
        cc = cc + 1
    [COLOR=Navy]Else[/COLOR]
        [COLOR=Navy]If[/COLOR] Not cc = 0 [COLOR=Navy]Then[/COLOR] Dn.Offset(-1, 1) = cc & " -"
        cc = 0
    [COLOR=Navy]End[/COLOR] If
[COLOR=Navy]Next[/COLOR] Dn
[COLOR=Navy]If[/COLOR] c > 0 [COLOR=Navy]Then[/COLOR]
    Rng(Rng.Count).Offset(, 1) = c & " +"
[COLOR=Navy]ElseIf[/COLOR] cc > 0 [COLOR=Navy]Then[/COLOR]
    Rng(Rng.Count).Offset(, 1) = cc & " -"
[COLOR=Navy]End[/COLOR] [COLOR=Navy]If[/COLOR]
[COLOR=Navy]End[/COLOR] [COLOR=Navy]Sub[/COLOR]
Regards Mick
 

Watch MrExcel Video

Forum statistics

Threads
1,123,270
Messages
5,600,638
Members
414,398
Latest member
dhune

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top