Finding the smoothest, flattest curve from a combination of various site values.

Off the top of my head, I cannot immediately think of a good algorithm. Something like this seems the most obvious approach, though it seems to be a sort of "brute force" approach:

1) Compute cumulative curve for each site. I'm not exactly sure how you intend to compute this. Will it be a simple "running sum" that can be computed with a SUM() function, or is there more to this quantity?
2) Compute "smoothness" and/or "flatness" score for the cumulative curve for each site. Again, I am not sure exactly what constitutes "smooth" and "flat", or how best to measure that. Perhaps come up with a curve that is "perfectly flat" (as best fits the scenario you are trying to understand here), then measure how different your actual data is from this "perfect" curve.
3) Rank the resulting "smoothness or flatness" scores.

You might spend some time with how you want to measure "smoothness" and "flatness". Once one understands how these are to be measured, it might be possible to avoid step 1, and infer those scores directly from the raw data.

I know that this is a rather generic and broad overview. Perhaps with a better, more detailed explanation of how you want to perform these calculations, we can better help provide specific suggestions.

In terms of the cells and their values, how do you calculate "smoothness".

I'm guessing that row 1 is your site numbers and that column A is time (in days)

I still do not understand exactly what you want to do. Making some assumptions:

0) I deleted all but three data sets. If we can understand how you want to analyze these three, and choose the smoothest, then we should be able to figure out how to extend it to more data sets.
1) I computed simple running summations =sum(b$2:b2) copied down and across. These cumulative sums are plotted in the chart.
2) I have no idea how you want to measure "smoothness". Looking at the original data, perhaps something measuring "amplitude" (difference between max and min), but I don't really know what you are wanting to do here.
3) This should be fairly straightforward once 1 and 2 are figured out.

Limiting to these three data sets, how would you rank these three?

Now I think I am getting a clearer understanding of what you want to do. Here's the basic "brute force" algorithm that I am seeing:

1) Compute sums for all possible combinations.
1a) This starts with a combinatorial generator for generating combinations 1,2,3 -- 1,2,4 -- 1,2,5 and so on
1b) Sum for each combination for each point.
2) Measure smoothness.
2a) extract max and min for each combination, take difference.
3) Identify which combination has the smallest difference, ignoring those combinations that should not be considered.

I have illustrated this on a smaller subset of your data set. Study the formulas so you can see how I am implementing each step described above. Then apply the same steps to your larger data set. I have left step 3 undone, as it should be a simple min and lookup function.
I will emphasize that this is a simple brute force algorithm. As one understands the nature of the problem, there me be a more efficient algorithm that does not require generating every possible combination.

Yes that's almost it, except it's for any number of sites combined, and sites 1 and 2 don't necessarily have to be used.

The spreadsheet I uploaded, when expanded, should not be limited to 1,2,x combinations only. It should apply to all combinations, if you expand the formulas until they generate other combinations. I kept the spreadsheet small so that you could see how the procedure basically works. I was kind of leaving it up to you to expand it beyond the example conditions I have listed.

I think the best way of measuring smoothness is to find the maximum gradient across all combinations, and whichever one is the lowest is the smoothest.

I am not exactly clear on what you mean by 'gradient', but, if the basic idea seems right to you, then it seems right to me as well. I would probably still use the same basic "layout"/algorithm -- generate all possible combinations across the top, compute "gradient" for each combination, identify which "gradient" is smallest.

The only problem I would have is finding a quick way to this.

Without a better understanding of the properties of the problem, all I can think of is the "brute force" algorithm. We have some very clever people on this forum as far as Excel programming goes. If you explain an algorithm to us, we can usually help you implement that algorithm in Excel. I do not know how many people who are good at the more generic "algorithm development" part of these problems. I will ask around, but do some research to help us out. It would probably help if you upload a "small" sample that easily illustrates the properties of the system you are studying. A smaller sample will allow someone who wanted to explore the behavior of the problem something manageable to test and try different approaches. Perhaps someone can come up with an algorithm that will be more efficient than "brute force".

es20g13 I'll have a look through the thread but it would help if you could explain why you want to do this. ie what is the purpose of the calculation. It helps me to get my mind round the problem.

From Mr. Shorty's three curves it appears that the lowest curve must also be the smoothest

But the curves are apparently waves and sinusoidal

So you have one of the combinations, find the gradient between adjacent points. So the gradient between x values of 0 and 0.035, then 0.035 and 0.075, then so forth. With this maybe finding the average of all gradient of all these points for each site? and whichevhat er combination has the lowest average can be classed as the smoothest profile?

Recognizing that some of this could be difference in terminology from across the pond -- are you using "gradient" the way I use the word "slope"? m=(y2-y1)/(x2-x1)?

If so, I would assume that "average gradient/slope" should be the same as "slope between final and starting point", would it not? Or, when you say "average gradient/slope" are you thinking of taking the average of the absolute value of the slopes/gradients so that positive and negative don't just cancel each other out?

Due to the intermittent nature of tides, other generators, in particular coal and gas, need to be dialled up or down in correspondence to fluctuations in generation from tides in order to match power demand, as the total generation always has to match the total power demand. What I'm trying to do is produce the flat and stable profile.

Seeing this as a "power demand" problem where you are using tides to generate power helps put the problem in context. As an engineer, I would wonder how other power companies and their engineers have solved this problem, as I find it a little difficult to believe that this is the first time that engineers have had to consider how to smooth out the fluctuations in tidal generation (or other power sources that are not consistent like wind).

Sorry, I don't have much more to add at this point. It looks like an interesting problem. I might ask if this is a real world problem, or if you are a student studying this problem in an academic setting. In either case, I would probably be asking myself how other engineers solve these problems, and see if that provides insight into my problem.

You could get the regression line for each and compare the maximum displacement from point to line for each and the lowest maximum translates to the smoothest series

As common as solar and wind are around here, I am surprised that no one has published anything on this kind of problem, yet. Breaking new ground, I guess.

As I have thought about it, I wonder if a good starting place for developing this algorithm would be one of the "find which combinations of numbers in a list add up to a constant" problems that are so common in accounting. It seems like that would be the smoothest possible output for the tidal generators -- a constant output. I recognize that this result may technically be impossible, but I wonder if it would make a good starting point for the algorithm you are looking for. I might suggest researching the algorithms used for that sort of problem, then see how you might adapt those algorithms to this problem. Of course, this is a common question on this forum, so you might run through this site's search engine.

Most of the "find the combination that adds up to k" problems in Excel are approached using Excel's Solver utility. I find it to be somewhat inefficient, so it may not work well for your problem. You may need to look deeper into the algorithms to see exactly how they work. If you are not careful, this may take you into concepts of "linear programming". This may be a good opportunity to find someone in the Computer Science department and discuss algorithms for solving those kinds of problems. It seems unlikely that someone in the computer science department will want to use a spreadsheet for this. He/She will want to use a different programming language, but it would seem that the choice of programming language is not important at this point. Developing the algorithm is what is important.

Originally Posted by es20g13

There are 53 or so sites (when excluding the ones in orange) so there's 1000s of different combinations.

If there are 53 signals, then there are 2^53 ways to combine them -- a number well beyond astronomical. Even limiting the selection to 3 gives 23,000-odd possibilities.

At a glance, the numbers all appear to be rectified sinusoids (absolutes of tidal gradients, right?).

If they are are all the same frequency (I didn't check), and ignoring the envelope modulation, then each can be characterized by a single number: its phase. And if that's true, the problem reduces to picking equally-spaced phases (e.g., 0, 120, and 240 degrees), no?

Data Range

	J	K	L	M	N	O	P	Q
23		1	2	3		1	2	3
24	Gradient	0.023	0.016	0.012		43.7	31.1	22.8
25
26
27	Exp	0.46	0.32	0.24	20 days	874	622	456
28	Obs	0.173828	0.123609	0.090501		942.32	670.0835	490.6055
29	¥	0.286172	0.196391	0.149499		68.32002	48.0835	34.60547
30
31	Exp	0.23	0.16	0.12	10 days	437	311	228
32	Obs	0.102726	0.073049	0.053483		375.2263	266.8233	195.3562
33	¥	0.127274	0.086951	0.066517		61.77375	44.17672	32.6438

To get the gradient I used:

Formula:

Please Login or Register  to view this content.

They are all indeed the same frequency (a period of 12:39:10), and they all have the same phase of triangular envelope modulation. I see that you also have them ranked left to right descending by total energy (RMS value).

Alas, the most energetic have the same phase:

Signal	Energy	Phase [deg]
1	0.552	84.6
2	0.392	84.6
3	0.287	84.6
4	0.227	84.6
5	0.215	84.6
6	0.195	84.6
7	0.187	136.4
8	0.163	84.6
9	0.118	110.5
10	0.110	84.6
11	0.068	110.5
12	0.052	110.5

A comment: These are surely calculated values; there is way too much precision to be measurements.

These are all in fact calculations. I was about to post up a list of the offsets. I'm very impressed you managed to work that out!

Originally Posted by es20g13

These are all in fact measurements.

What instrument gives 15-digit measurements?

EDIT: The first several signals (the few that I checked) are exactly the same data with different scaling, e.g., signal2 = signal1 * 0.711099718021. How likely is that?

So they are not measurements, not real data, and this is just an exercise in ... what?

I was wondering how they could all have the same nodes

If you had posted actual data, as you could have found in published tide tables, rather than a rectal extraction with no relation to reality, someone might have offered useful assistance. You should be more creative in manufacturing data for your dissertation.

Originally Posted by es20g13

... using many assumptions suggested to me by specialists in tidal power.

I would put that statement in the same bin as this one:

These are all in fact measurements.

Thank you for that.

My suggestion is unchanged from post #21; select components based on spreading out the phases, with approximately equal power for each phase. But for that purpose, your data is useless, because it doesn't begin to resemble actual tides. If you ran down some real data, this could actually be an interesting exercise. As it is, it's just a waste of time.

There are tide gauges all over Britain, and surely both logs and prediction data for every one of them. It would make your paper a lot more credible and interesting if you got one or the other.

This is what I was doing:


TideWoes.PNG

Please Login or Register  to view this content.

Then post some real data instead of manufactured data.

What difference would it make if my data was manufactured or measured if it's still in the same excel sheet format I initially attached? I also need the intervals of 0.01 so I wouldn't be able to use real data either.

Hi ES,

I was looking for the midpoint between nodes - from them I would get the amplitudes, although I hadn't worked out how many minutes to advance or retard the observed values. I found nodes at 7.62,13.82,23.75 and 27.15 days (approxomately) and I was testing with 18.79 and 25.46 day values.

Data Range

	K	L	M	N	O	P	Q
23	1	2	3		1	2	3
24	0.023	0.016	0.012		43.7	31.1	22.8
25
26
27	0.58558	0.40736	0.30552	25.46	1112.602	791.806	580.488
28	0.63174	0.44923	0.328906		1060.013	753.7746	551.8804
29	0.04616	0.04187	0.023386		52.58947	38.03139	28.6076
30
31	0.43217	0.30064	0.22548	18.79	821.123	584.369	428.412
32	0.477354	0.339446	0.248528		900.3122	640.2117	468.7347
33	0.045184	0.038806	0.023048		79.18915	55.84272	40.32268

The little routine got me close to the nodal value - it just tested the product of the day times the gradient against the observed day value

And, Thanks for the rep!

BTW - Have you a Bowditch?

It looks like we can get the actual amplitude by just dividing by the tangent of the gradient - for this data anyway

Amendment - Make that the Cosine of the gradient.

I hate to do a hit and run, and I wish I had more time to dig into this one.

It doesn't look like anyone has suggested to look into a Linear/Non-Linear programming solution; however if you do the size of your data set is way outside of the range of the free version of the solver addin that comes with excel.

I take it back Mr Shorty mentioned LP first, and it does appear as though the generic solver may do the task.

However the problem I'm comming up with in your data model is that either through your attempt to standardize and or manufacture data points you've forced the stations to be more or less linear functions of the first one, so S₁=k*S₂, and S₁+S₂=(1+k)S₁ which will be less smooth than just S₁ alone. Given that, then the "smoothest" curve would be the single one with the smallest k value ie S₅₄. (solver agrees)

How I set up my solver: Removed colums for sites not needed, insert row above for x values, B1=1, C1:AW1=0, AX1=sum(B1:AW1), AX3:AX2956=sumproduct($B$1:$AW$1,B3:AW3), AY3:AY2955=(AX4-AX3)/0.01, AZ3:AZ2954=AY4-AY3, AZ2=sumsq(AZ3:AZ2954), Solver: objective: AZ2, min, changeing:B1:AW1, B1:AW1 = Binary, AX1>=1, Method: Evolutionary

Although after you fix your data so that every station doesn't peak in the same direction at the same time, you may want to consider changing the binary constrant so that B1:AW1 is between 0 and 1 inclusively, as a true smoothing may be based on a percentage of each site. Moreover talk with your local math department to see what may be needed to add in a seccondary goal of min/max the station count as well.

hmm, i may stand corrected try stations: {23,24,25,27,32,39,40,42,43,52} and let me know if that's more what you're looking for.