Cumulative sum, based on previous data

[downhillsheep]

Ok, I'm sure this is pretty easy to figure out but this has been bugging me for a while.

If you look at the list below, I have twelve months of data of various amounts. What I would like to do is to report ZERO for Jan as the amount if less than 0.5; report ZERO for Feb as the total of Jan and Feb is still less than 0.5. However, for Mar I'd like to report 0.7 as that is the total of Jan, Feb, and Mar and is over 0.5. For Apr I would like to report 1.2 as that amount is already higher than 0.5; May ZERO and Jun 1.3 and so on and so fourth

	Jan	Feb	Mar	Apr	May	Jun	Jul	Aug	Sept	Oct	Nov
Data	0.2	0.1	0.4	1.2	0.3	1	0.2	0.2	0.2	0.2	0.2
What I'd like to achieve	0.0	0.0	0.7	1.2	0.0	1.3	0.0	0.0	0.0	0.0	1.0

<colgroup><col><col span="11"></colgroup><tbody>
</tbody>

I have to do this for over 10k lines of data and would love an easier way of formulating it so I know what needs to be reported on any given month

Thank you so much for your help​

 

[jamtay317]

----deleted

 

[jamtay317]

I just read what you wanted again sorry, can you please tell me how your comming up with the what needs to be over.5 because i notace that may august sept, oct are all zero and they are under .5, but Mar under .5 and it is 1.9

 

[FDibbins]

Cross-posted at....
Cumulative sum, based on previous data

 

[MickG]

Try this:-
Nb:- Actual Data to start in row (2)
The code assumes that if the Final numbers in any row do not add up to 0.5 then the data stays as is.
NB:- This code overwrites the data !!!!

[COLOR=navy]Sub[/COLOR] MG29May35
[COLOR=navy]Dim[/COLOR] Rng [COLOR=navy]As[/COLOR] Range, Dn [COLOR=navy]As[/COLOR] Range
[COLOR=navy]Dim[/COLOR] Ac [COLOR=navy]As[/COLOR] [COLOR=navy]Integer[/COLOR]
[COLOR=navy]Dim[/COLOR] aRng [COLOR=navy]As[/COLOR] Range
[COLOR=navy]Dim[/COLOR] oSum [COLOR=navy]As[/COLOR] Double
[COLOR=navy]Set[/COLOR] Rng = Range(Range("A2"), Range("A" & Rows.Count).End(xlUp))
[COLOR=navy]
For[/COLOR] [COLOR=navy]Each[/COLOR] Dn [COLOR=navy]In[/COLOR] Rng
[COLOR=navy]Set[/COLOR] aRng = Nothing
[COLOR=navy]For[/COLOR] Ac = 1 To 12
    oSum = oSum + Dn(, Ac)
        [COLOR=navy]If[/COLOR] aRng [COLOR=navy]Is[/COLOR] Nothing [COLOR=navy]Then[/COLOR]
            [COLOR=navy]Set[/COLOR] aRng = Dn(, Ac)
        [COLOR=navy]Else[/COLOR]
            [COLOR=navy]Set[/COLOR] aRng = Union(aRng, Dn(, Ac))
        [COLOR=navy]End[/COLOR] If
 
        [COLOR=navy]If[/COLOR] oSum >= 0.5 [COLOR=navy]Then[/COLOR]
            aRng.Value = 0
            [COLOR=navy]Set[/COLOR] aRng = Nothing
            Dn(, Ac) = oSum
            oSum = 0
        [COLOR=navy]End[/COLOR] If
    [COLOR=navy]Next[/COLOR] Ac
[COLOR=navy]Next[/COLOR] Dn
[COLOR=navy]End[/COLOR] [COLOR=navy]Sub[/COLOR]

Regards Mick

 

[Joe4]

Please note our rules on Cross-Posting. While we do not prohibit Cross-Posting on this site, we do ask that you please mention you are doing so and provide links in each of the threads pointing to the other thread (see rule #10 here: Forum Rules).

This way, other members can see what has already been done in regards to a question, and do not waste time working on a question that may already be answered. For a more complete explanation on cross-posting, see here: Forum Rules).