How to Insert cells with time Increment

Hi,
Will appreciate your expertise on these,

1) There's a series of data I have . each cell has time in hour/ minute/seconds- -------- second cell has a Value in number

Now the 1st problem is that not every second has been recorded in this data . 1.e. 12 hrs. 23 minutes 15 second is 1st cell. next cell should be 12 hrs. 23 minutes and 16 seconds.
However, the second cell is not always showing an increment per second. sometimes it looks per second increment , sometimes it shows two second or five seconds or even eight seconds increment.
what I would like to do here is that automatically , a new cell should be inserted if the next cell has an increment more than 1 second. i.e. 12.23.16 second should be followed by 12.23.17 seconds and so on.
same way, if the cell shows an increment of 3 seconds or so on, so the same number of cells should be inserted.

The Value for each cell should be the same as of the last cell ( in case of new cells inserted).
That's first problem.

2) second is that we have a random series of numbers either ascending or descending. What I need to find is a method that the data shows only 20 points reversals, either upside or downside. I.e., starting from first value, if the data is ascending, then it should identify a point where the numbers started descending and descended a minimum of 20 points. there again, it has to identify another point where the number again started ascending and ascended atleast 20 points.

If this can be done, then half of my problem is solved. will appreciate if you'd guide

with thanks,

Administrative Note:

Welcome to the forum.

We would very much like to help you with your query, however the thread title does not really convey what your request is about.

Please take a moment to amend your thread title. Make sure that the title properly explains your request. Your title should be explicit and not be generic (this includes function names used without an indication of what you are trying to achieve).

Please see Forum Rule #1 about proper thread titles and adjust accordingly. To edit the thread title, open the original post to edit and then click on Go Advanced (bottom right) to access the area where you can edit your title.

(Note: this change is not optional. As you are new, I have attempted to do it for you this time.)

Being new, you may not realize that you can illustrate your request by uploading a desensitized sample of your data along with what you want done to it (i.e. before and after).
To upload a sample workbook (not a picture or pasted copy) click on the GO ADVANCED button below the Quick Reply window and then scroll down to Manage Attachments to open the upload window.
The sample only needs to be large enough to illustrate the two parts of the request.
Let us know if you have any questions.

The date/time values, at least in column B, are text not numeric. To remedy that use the text-to-columns feature (Next > Next > select Date > YMD > Finish). Custom format yyyy-mm-dd hh:mm:ss
As to 1st problem modeled for SR1 in columns L:N down through row 100:
1. Populate column L using: =IF(M2<>"",A$2,"")
2. Populate column M using: =IF(ROW()=2,B2,IF(OR(M1="",SUM(M1,TIME(0,0,1))>B$27),"",MROUND(SUM(M1,TIME(0,0,1)),TIME(0,0,1))))
3. Populate column N using: =IF(M2="","",IFERROR(INDEX(C$2:C$27,MATCH(M2,B$2:B$27,0)),N1))
I am confused as to what is considered a difference of 20 points when looking at the numbers in column C. Perhaps further definition and/or illustration will make it clearer.
Let us know if you have any questions.

Please include some data that illustrates point #5. I believe that I understand what you are describing, however it would be good to see some data and manually include the desired outcomes, so that someone could attempt to write formulas/code that would replicate the outcome.
Let us know if you have any questions.