For example, I have a quasi-random series (340 numbers on 340 cells of a column). Its numbers are integers (0 to 31). It is periodic but its period (N < 340) is unknown.
Is there a way to find out its period other than doing it manually?
Thank you.
Kerim
Editted.
Last edited by KerimF; 06-17-2022 at 09:35 AM.
Welcome to the forum.
Are you really still using Excel 2007?
There are instructions at the top of the page explaining how to attach your sample workbook.
A good sample workbook has just 10-20 rows of representative data that has been desensitised. It also has expected results mocked up, relevant cells highlighted and a few explanatory notes.
Ali
Enthusiastic self-taught user of MS Excel who's always learning!
Don't forget to say "thank you" in your thread to anyone who has offered you help. It's a universal courtesy.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
NB: as a Moderator, I never accept friendship requests.
Forum Rules (updated August 2023): please read them here.
Thank you for the welcome.
Please answer my question.
Oh sorry, I thought you will find out the answer after downloading the file.
Yes, I am. Unfortunately, it is the case, not because of my will but the will of the world's Elite
I'm not interested as to why - just need to know which version you are using. Thanks.
Sorry, it seems my English is no more good.
You asked:
"Are you really still using Excel 2007?"
I answered with:
"Yes, I am".
Yes, I am still using Excel 2007.
Should I add something else?
No - that's fine. Thanks.
How much is a math/trig/algorithm question, and how much is specific to Excel? We are usually very good at programming known algorithms into Excel, but not always knowledgeable in your specific field to know or derive appropriate algorithms.
Your file indicates that the period is 256. My expectation would be that period would represent the horizontal distance for one wavelength (starts at 15, goes up and comes back down to 15, then continues down and comes back up to 15), in which case I would have expected the period/wavelength to be about 20. Help us understand how you get about 256 for the period, and we should be able to help you program that algorithm into Excel.
Originally Posted by shg
Thank you for replying.
Your remark is right. The period would be 20 if we consider the series as generating a consecutive sine wave cycles. So even if the second cycle starts with 17 instead of 15 it is similar to the first one, speaking practically. The same applies on all subsequent cycles.
So I am sorry for not being clearer on my first post.
My question is rather mathematical. And I consider the series as being consecutive integers forming a periodic pattern. So perhaps I had to list more than 340 numbers to cover more than one period, two in the least.
On my uploaded sheet, the start of the series on the yellow cells is repeated 'exactly' on the pink ones (the first 84 numbers, from row 261 to 344).
For instance, I understand it is natural that not every algorithm of interest could be implemented by a general purpose tool though when it is real necessary some new algorithms could be added by its developers (This is why there is no final version of any useful product; unless it is halted).
So it's more about finding where the exact sequence of numbers. 15,20,24,28,30,30,30,27,... (for however far you require) repeats. Off the top of my head, I cannot think of anything better than a brute force "find every 15 and check the sequence of numbers following it to see if it matches the original sequence" kind of approach. It also feels like something that would be better suited for another programming language (that has convenient loop structures) rather than a spreadsheet. Perhaps the new LET() or SEQUENCE() functions would be useful, if one decided to do this in the spreadsheet, but your version and my version do not have access to those functions.
Maybe try this. It still requires some manual work, but it should be fairly quick and easy. Assuming that 20 or so rows is enough to determine that the sequence is an exact match:
0) Delete the chart (it doesn't seem to help understand or analyze the problem). Move E3 out of the way.
1) In F5:Z5, enter the formula =TRANSPOSE(E5:E25) (remember this is an array function and must be entered with ctrl-shift-enter). Note the relative references and copy/paste/fill to the bottom of the data set. Now each row shows the sequence of 20 numbers that immediately follow the first entry for that row.
2) Select a cell somewhere in the data set and set up Autofilter for the table (Data -> Filter).
3) In the dropdown for column F, select "15" to only show rows where 15 is in column F.
4) In the dropdown for column G, select "20" to only show rows where column G is 20.
5) Continue across until you only have 2 rows showing (this data set only takes 3 column). Check to see if those two rows represent the same sequence.
If full automation is important, then we can talk further about automating the filter steps (using lookup functions or VBA or other programming language).
Thank you MrShorty for your time and support.
I will try your steps. After all, a semi-automation is also much better than no automation at all. Thank you.
A bit of a guess:
=IF(((E5:E340=E5)*(E6:E341=E6)*(E7:E342=E7)*(E8:E343=E8))>0,"Start","")
In your Excel version, this is probably an array formula (CTRL-SHIFT-Enter to set).
It >>seems<< to work, inserting "Start" at the beginning of each sequence.
Glenn
None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU
Indeed, your solution is good.
Thanks a lot.
Added: How to mark that the problem is solved and the post on which the solution is?
Last edited by KerimF; 06-18-2022 at 02:34 PM.
You're welcome.
It would be very nice if you were to just click the Add Reputation button at the foot of any of the posts of members who helped you reach a solution.
Finally, if that takes care of your original question, please select "Thread Tools" from the menu link (just above the first post in the thread) and mark this thread as SOLVED.
Thank you.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks