Auto Cumulate

[help needed yes please]

Hi, I havea column ‘A’ which is numerical. I <o></o>
I: want tohave a cell in another column which will auto increase by ‘1’ each time anentry is made in column ‘A’<o></o>
I Calculatecolumn ‘A’ in 30 day cycles. So:<o></o>
IE: A103should show number in designated cell (Example G50) ‘13’<o></o>
When entry placed in A104 shows in G50 ‘14’ I am lost here so any help be great. Thanks.Frank<o></o>

 

[help needed yes please]

Hi I have been working on formula and if I change date in formula by a day can make days in cycle increase by a day. IE: Formula =15/5/2018 shows result of 12 days into cycle on 12/5/2018 which is true. Formula changed to 16/5/2018 shows result of 13 days into cycle on 13/5/2018 which is true.
At present formula does not auto change day after day? I Need to have it so that if an entry is placed into Column E on a day later than the prior entries into Column E the Cell into which the formula is entered will automatically increase along the 30 day cyclical rotation. I Know its easy to say. (Enough to drive a bloke to drink?) Hope not, if this makes it clearer great, if you can help, wonderful. Ta Frank

 

[help needed yes please]

Frank again, I am a ding a ling. Just eliminated forward date entries and found it WORKS, auto updates figure OK. Only problem is that it will update when a duplicate date is entered. It is treating every entry as a new day. Can this be overcome? Thanks for your hard work. F

 

[help needed yes please]

frank here, Dont know why but number is not changing whatever date is added. Cant figure! Going bonkers, please disregard last post. This is formula entered: =IF(INDEX(A:A,COUNTA(A:A))-DATE(2018,5,17)<>0,IF(MOD(INDEX(A:A,COUNTA(A:A))-DATE(2018,5,17),30)>0,31-MOD(INDEX(A:A,COUNTA(A:A))-DATE(2018,5,17),30),1),"")

In cell F88 it shows 8 days which is correct for balance of 30 Day cycle. Rest is As per 2 posts back. Ta F getting a bit down with this little lot.

 

[help needed yes please]

Hi shknbk2, If you get a chance could you cast an eye on post 23, Ta f

 

[shknbk2]

The formula does 3 thing:
1. Find which cell in column A to look at
2. Subtract that date from May 17, 2018
3. Calculate the 30-day cycle number

To find the cell to look at, the INDEX formula return the cell in column A that is the ROW of the number of non-blank cells in column A. If there are 3 non-blank cells in column A, the INDEX formula returns A3, and that is the date that is used in step 2. The formula is not set up to determing where the dates start if there are blank cells mixed in. If the number of non-blank cells does not match the ROW number of the last cell, the formula won't work.

The formula also does not look to see if that date is the same as any other cell. If the dates in column A are chronological, adding a duplicate date at the end does not change the result number assuming that A3, for example, is the last date entered, is the same date as A2, and that A1-A3 are non-blank. If the dates in column A are not chronological, then the result in F88 will be jumping around when the last cell in column A (when there are no blank cells) jumps around. If you need to enter a new entry for a date before the last date value in column A and cannot insert that date next to its mate or re-sort the cells to be in date order, this formula won't return the correct value.

I'm not sure what you mean by the Column E stuff. Can you post a worksheet with sample data?

 

[help needed yes please]

hello shknbk2, I have been busy on the midnight oil. All Data has been placed int a new book, new page and your formula works brilliantly. Thank you for persevering with me. I came to the conclusion you were so good it had to be something my end. There must have been some gremlins?
Oh i don't know how i can send a work page . Other than inet address. Anyway really feel blessed you came to the rescue, so thanks heaps. Ta Frank We are all carried on the shoulders of those with whom we are graced to have share our journey?

 

[help needed yes please]

hi shknbk2, frank again. Just realized that it wont allow multiple entries on the same date, IE: if 24/4/2018 shows days left in cycle as 10, whatever entry placed in the date column will change figure down to 9.
Is that something fixable? Ta F

 

[shknbk2]

24/4/2018 is earlier than the date of 17/5/2018 that you are using in the formula. The dates in column A should be later than the formula date.

Even so, I created a new workbook and copied the formula above with the 17/5/2018 date into D1 and in A1 put 24/4/2018. The number it gave me was 20. It stayed 20 when I put 24/4/2018 into A2 as well. So I'm not sure what your data is doing.

 

[help needed yes please]

hi shknbk2, Good morning, the above date was just for example and the actual date is backdated more than a month. Formula is =IF(INDEX(A:A,COUNTA(A:A))-DATE(2018,4,28)<>0,IF(MOD(INDEX(A:A,COUNTA(A:A))-DATE(2018,4,28),30)>0,31-MOD(INDEX(A:A,COUNTA(A:A))-DATE(2018,4,28),30),1),"") I originally made a simple list of dates in column 'A' of a new page. Thats all just dates. I adjusted the dates in formula to reflect dates entered and put your formula into several cells in other columns and the formula worked in every cell the same correct answer.. So proving problem was within the records.
I also got funny things happening before i made a new page then it cycled properly. So my friend it simply means i shall have to make a separate page of special events that have multiple entries on the same day. If that's the worst? no worries mate. Thanks for your help, I would have been like a lizard? 'Flat out going nowhere' without you. Ta, Frank