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]

Thanks shknbk2, I did put that onto the pages 'View code' popup but I could not see how to assign it to a specific cell or even how to get off the popup. Which is why I asked above for more help. You are not the problem my friend . My ignorance of subject matter is weighing me down. Yes I need help so don't give up on me please. Thanks, frank

 

[shknbk2]

No problem. I want to try to understand the numbers. Let me know if this is right or wrong.

You are looking to use the last number in row D as the number of the 30-day cycle to put into F30. It is always F30 that has the 30-day cycle number.

Starting with low numbers, the corresponding F30 cell is what results from the D cell:

D	F30
1	30
2	29
29	2
30	1
31	30

<tbody>
</tbody>

Are these numbers correct for F30?

Also, to save another back and forth, if the cycle above is correct, does 4881 (which is 21 days past a multiple of 30) result in F30 being 10?

 

[help needed yes please]

Thanks, the above looks good. If it is only a 30day formula I can simply repeat the thing on a 30 day cycle. It would be even better if the formula was open ended but auto siphoned of the data to each ongoing 30 day like F30,F60,F90 and so on. Thanks, frank

 

[shknbk2]

Okay. This is the formula you can use in cell F30. No need to use the VBA programming code provided earlier.

=IF(INDEX(D:D,COUNTA(D:D))<> 0,IF(MOD(INDEX(D:D,COUNTA(D:D)),30)>0,31-MOD(INDEX(D:D,COUNTA(D:D)),30),1),"")

In F30, this results in the following for the number in the last cell in column D. However, this also assumes no blank cells in the middle. If there are, then the result will be off, and a different formula would have to be used.

• column D is completely blank: Nothing shows (the result of the first IF)
• the value of the number in D1 results in a 30-day cycle value of 1 to 30: 30 to 1 show, respectively

This formula can be used in each individual cell in F that is the multiple of 30, leaving the other cells (F1-F29, etc.) empty.

However, because you suggested F30, F60, etc. showing that cell's respective block of 30, the formula above can be put inside another IF that shows the result based on column D if the row is a multiple of 30, or is blank otherwise. Using this method allows you to autofill down to where you need it. The only difference with the first method is that every cell including F1-F29, etc will have a formula and won't be empty.

=IF(MOD(ROW(),30)=0,IF(INDEX(D:D,COUNTA(D:D))<> 0,IF(MOD(INDEX(D:D,COUNTA(D:D)),30)>0,31-MOD(INDEX(D:D,COUNTA(D:D)),30),1),""),"")

 

[help needed yes please]

Hi shknbk2, Looks like zero in on solution. What you said about placing formula in any cell in ‘column F’ while leaving no formula in other cells in ‘F’ was good because many of the other cells in ‘F’ have alpha numeric data in them. I have been looking at historic data and found that there are special events from time to time which results in multiple lines used for a single day. This leads me to conclude the only reliable way I can make 30 day cycles is to use ‘Column A’ which is ‘Date’ it will not matter if there are 6 entries on a specific date if we use 30 elapsed days for the cycle. Column ‘E’ is net figures. As an update is entered into this column on a NEW day the hope is F86 will count down the days. Currently this page has A88 as the 30th day and would show in F86 as 1day. Will it matter that column ‘E’ has a formula elsewhere that includes a rage of cells I column ‘E’ Based on my new understanding ‘D118’ may well not be 30 elapsed days of a cycle. So ‘Date’ seems best option? I did enter the top formula into F86 (Because I can’t put formula into all cells in F)No surprise nothing shows up in F86.Well done you for coming so far with it, Hopefully I have got it right for you this time, Thanks Frank

 

[shknbk2]

Using the date information leads to more information needed. The formulas above won't be useful. What is the start date that should be used to start counting the 30-day cycle. Is it in one of the cells? The 30-day cycle doesn't seem to be able to follow calendar days, so is that okay? Does the date re-start the next 30-day cycle immediately after the end of the previous cycle regardless of the date on the calendar?

It doesn't look like the data in column D or E has anything to do with determining the 30-day cycle day, so I don't think it matter what is in those cells.

 

[help needed yes please]

Hi shknbk2,
If date is concern perhaps moving across to Column C which has days of week (Alpha) so any designated cell range in ‘Column C’ could be referenced as a block in a given cell elsewhere.
Yes just restarts after cycle. Cycle 30 down to 1 then starts again 30 down to 1. Hope my friend this helps, Thanks, Frank.

 

[shknbk2]

You can use this updated formula. It uses a base date from which to subtract the last date in column A. Since it works by date, duplicate dates are no problem -- they merely generate the same number as they are entered in. You need to still be aware that this will not work unless the dates start in A1 and there are no blanks in the middle.

This is the formula. You will need to change the DATE part of the formula to put in the year, month, and day for the base date. The base date is the day BEFORE the first day in A1. That way the date in A1 will already have a subtraction value of 1, which is the first day of the cycle.

=IF(INDEX(A:A,COUNTA(A:A))-DATE(2018,5,19)<> 0,IF(MOD(INDEX(A:A,COUNTA(A:A))-DATE(2018,5,19),30)>0,31-MOD(INDEX(A:A,COUNTA(A:A))-DATE(2018,5,19),30),1),"")

Alternatively, rather than using the DATE formula, you could have that base date in another cell and reference that instead.

 

[help needed yes please]

Hi, That was pretty good, well done. I think DATE is best option if possible. I entered the formula into cell previously mentioned of 'F86' Which should give reading of 1 when entry in E88 is entered. It currently shows 16 days to go in the cycle. Reality is should show 9 days left in cycle. (E89 Starts back at 30 to commence cycle)
I need that when the entry of data into column 'E' on a NEW DAY, for that to be the trigger that cycles down the 30 days. It does not matter which cell is chosen to put the formula in as the formula should detail the cells to be acted upon. YOUR GOING GREAT GUNS, WISH I KNEW YOUR MAGIC, thanks,Frank,

 

[help needed yes please]

Hi shknbm2, I have been working with formula and below are results.
Cycle day 1 is on 1/5/2018 and shows result of 28 days.
30 30/4/2018 27
29 29/4/2018 26 days. Cycle 30 days = 30/4/2018 30 days back from day 1.
As shown above if I change date in formula by a day this shows an ascending result.(OK) Base cell into which formula is entered shows result '16' days Nothing changes automatically and i have to change the date to show the ascending number of days within the cycle. I don't know where the number 16 comes from, it is 19 days into the cycle with 11 days to go.
I need when an entry is put into Column ‘E’ on a NEW day this changes the result cell (Which could be any clear cell somewhere) by 1 day. At the moment nothing triggers the cyclical period. I can’t have formula in columns that have data as it would get wiped with any entry. Also dates extend many cycles into the future. Formula needs to reference data. Hope this clears the mud? Ta , Frank