Working out if data is regularly periodic

Format column-U as "Number"

In U5 copied down to U9:

Formula:

Please Login or Register  to view this content.

This gives the correct answer for monthly, quarterly and semi-annually and for non-periodic(for which U8 will be non-integer). The result for annual is 9 and is incorrect because your chosen 18 month time period is not a multiple of years.

Hope this helps

Hi -

Thanks, however, that formula doesn't capture regular periodicity - if you, for example, move one of the quarterly values forward by a month, or postpone it by a month, you still get 3.0 - as the total number of filled cells is still the same. I accept that the non-periodic one with a non-divisible number of filled cells provides a non-integer, but if you simply delete one of those cells, then there are 6 filled cells and you get an answer of 3.0, which would imply quarterly values, even though in reality these are not equally distributed.

Any additional thoughts?

Thanks,

Joachim

How much of this is specific to Excel and how much is a broader algorithm development question -- independent of programming language? If you know the algorithm you want to use and can help us understand it, we should be able to help you program that algorithm into the spreadsheet. If you are more in the algorithm development stage, we can probably help with that, too, but it is a little bit different question.

If this is more about algorithm development, it seems to me that we need more details about what you are trying to do. For example:

if you, for example, move one of the quarterly values forward by a month, or postpone it by a month, you still get 3.0 - as the total number of filled cells is still the same.

implies that you will tolerate no deviation from a pattern in detecting that pattern. However, If I saw a set where all of the payments but one were quarterly, and the exception was only 1 month early/late, I would be inclined to call that a quarterly pattern, but your statement suggests that even one payment being one month early/late makes an otherwise quarterly pattern have no pattern. If that's what you want, that's fine, but I think it will be important to be very clear about what you want to do in order to develop a robust algorithm (and then program).

If there are only a few very specific patterns that need to be detected, I could see this somewhat "brute force" algorithm working (in more outline/pseudocode form):

1) a sign function to detect which months have an entry, then join into a single "binary number" string. CONCAT(SIGN(range)) kind of thing.
2) in a convenient place in the spreadsheet, build a lookup table with the desired "binary number" patterns you want to detect and the pattern they represent.
3) use the result of (1) in a lookup function to return the correct pattern output. If the result of (1) is not found in the lookup table, then return a no pattern indicator. VLOOKUP(result of 1,lookup table,2,FALSE)

That should work as long as the number of patterns you wish to detect is small and your matching algorithm is exact. It will probably become unwieldy if you have a lot of different patterns to detect and/or if your desired matching algorithm is more fuzzy.

It looks fine to me. My mentor commented in some of his programs that he sometimes programmed things to be easy for the programmer even if they weren't necessarily the easiest for the computer. If you understand what you did well enough to use it, change it (when needed), and adapt it to other scenarios, then it might just be good enough, and might be better than someone else's "better" solution that you don't understand or cannot change or adapt.