Generate curved data set based on min., max. and average values

[Kevin Barton]

I want to generate a series of values that would form a classic bell-curve shape if plotted on a graph, starting at zero rising to a max and descending again to zero, in a curve that maintains a prescribed average.

The application is to generate a series of rainfall data sets using a defined duration of storm (eg. 120 minutes) and average rainfall intensity (eg. 50mm per hour) with each set having a different maximum intensity (Eg. 75mm/hr; 100mm/hr; 125mm/hr, etc).

The data set will express the rainfall intensity for each 5 minute interval of the storm.
The first and last 5 minute interval will be near 0mm/hr of rainfall as it’s just starting or about to stop raining, whilst the peak rainfall intensity will be at the centre of the data/duration at the 60 minute mark.

The input variables will be:

• Duration
• Average rainfall intensity
• Maximum rainfall intensity

In the 120 minute example, 24 cells defining 5 minute intervals will be generated with each having a neighbouring cell with a rainfall intensity value.

If plotted on a graph three versions of this storm data with differing maximum intensities will look something like this (but with all lines starting and finishing on zero). Ignore X and Y axes/values - it illustrates the different shapes of curve generated by achieving different maximums whilst maintaining the same average - thinner pronounced curve for higher max. and flatter curve for lower max.:

Many thanks.

 

[maabadi]

Please Check this address
How to Make a Bell Curve in Excel (Step-by-step Guide)

 

[Kevin Barton]

Please Check this address
How to Make a Bell Curve in Excel (Step-by-step Guide)

Hi Maabadi. Yes, I’ve looked at lots of web pages about Bell Curves and Normal Distribution Curves however this is not what I need to do.
This appears to be a way of analysing a set of data to find how it is distributed whereas I need to create a data set.

I need to create a set of data based on the parameters outlined above (Duration, average and max.), that if you plotted that data on a graph, would form a bell curve with the data values starting at zero, rising to maximum and descending again to zero, whilst maintaining an average.

 

[maabadi]

I don't think excel has this capability. Maybe others have best answers or option.

 

[joeu2004]

I want to generate a series of values that would form a classic bell-curve shape

There are many kinds of distributions that result in a "bell-shaped" curve. I am most familiar with the normal distribution. But I agree with you: it probably does not have the flexibility/control that you require. (I do not know how to use kurtosis to change the shape of a normal distribution. But even if we did, I'm not sure it can meet all of the requirements.)

Aside.... That said, I want to point out that the method used by the article that maabadi cites is incorrect, notwithstanding that it is a common approach. It is important that the area under the portion of the curve within our limits (+/-3sd in the article) equals the total of the amount that is being distributed (rainfall of 100mm). I could demonstrate the correct method. But it is moot at this point, since we agree that it is not a solution for you.

You might consider the beta distribution. I am not very familiar with it myself. But I believe the following demonstrates how to apply it to your problem. You can also download the file "rainfall beta distrib.xslx" (click here). Ignore any preview errors and request to login; just proceed to download.

The beta distribution requires two "shape" parameters -- alpha and beta. Unfortunately, I do not know any formulas for determinng their values directly. Off-hand, I am not able to apply the method described in the wikipage. So, I must resort to using Solver for that purpose.

rainfall beta distrib.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L
1	duration	120	min	Duration	120	min	Duration	120	min	Duration	120	min
2	avg rate	50	mm/hr	Avg rate	50	mm/hr	Avg rate	50	mm/hr	Avg rate	50	mm/hr
3	peak rate	125	mm/hr	Peak rate	100	mm/hr	Peak rate	75	mm/hr	Peak rate	150	mm/hr
4	peak at...	60	min	Peak at...	60	min	Peak at...	60	min	Peak at...	60	min
5	peak amt	10.41666666666670	mm	Peak amt	8.33333333333333	mm	Peak amt	6.25000000000000	mm	Peak amt	12.50000000000000	mm
6	total	100	mm	Total	100	mm	Total	100	mm	Total	100	mm
7	alpha/beta	5.24987709478519		alpha/beta	3.41720623275479		alpha/beta	2.00835395577078		alpha/beta	7.53039699303080
8		10.41666666666670	solver		8.33333333333334	solver		6.24999999999978	solver		12.50000000000000	solver
9
10	time	%total	rainfall (mm)	time	%total	rainfall (mm)	time	%total	rainfall (mm)	time	%total	rainfall (mm)
11	5	0.000849%	0.000849	5	0.029804%	0.029804	5	0.497793%	0.497793	5	0.000011%	0.000011
12	10	0.026799%	0.026799	10	0.263651%	0.263651	10	1.447171%	1.447171	10	0.001559%	0.001559
13	15	0.170040%	0.170040	15	0.784856%	0.784856	15	2.315662%	2.315662	15	0.024018%	0.024018
14	20	0.559667%	0.559667	20	1.563862%	1.563862	20	3.099749%	3.099749	20	0.144540%	0.144540
15	25	1.298013%	1.298013	25	2.535617%	2.535617	25	3.798139%	3.798139	25	0.518488%	0.518488
16	30	2.418497%	2.418497	30	3.620914%	3.620914	30	4.410121%	4.410121	30	1.338818%	1.338818
17	35	3.870254%	3.870254	35	4.737079%	4.737079	35	4.935245%	4.935245	35	2.745755%	2.745755
18	40	5.525302%	5.525302	40	5.804645%	5.804645	40	5.373207%	5.373207	40	4.733379%	4.733379
19	45	7.201714%	7.201714	45	6.751911%	6.751911	45	5.723792%	5.723792	45	7.101439%	7.101439
20	50	8.695760%	8.695760	50	7.518192%	7.518192	50	5.986848%	5.986848	50	9.478685%	9.478685
21	55	9.816438%	9.816438	55	8.056136%	8.056136	55	6.162272%	6.162272	55	11.413309%	11.413309
22	60	10.416667%	10.416667	60	8.333333%	8.333333	60	6.250000%	6.250000	60	12.500000%	12.500000
23	65	10.416667%	10.416667	65	8.333333%	8.333333	65	6.250000%	6.250000	65	12.500000%	12.500000
24	70	9.816438%	9.816438	70	8.056136%	8.056136	70	6.162272%	6.162272	70	11.413309%	11.413309
25	75	8.695760%	8.695760	75	7.518192%	7.518192	75	5.986848%	5.986848	75	9.478685%	9.478685
26	80	7.201714%	7.201714	80	6.751911%	6.751911	80	5.723792%	5.723792	80	7.101439%	7.101439
27	85	5.525302%	5.525302	85	5.804645%	5.804645	85	5.373207%	5.373207	85	4.733379%	4.733379
28	90	3.870254%	3.870254	90	4.737079%	4.737079	90	4.935245%	4.935245	90	2.745755%	2.745755
29	95	2.418497%	2.418497	95	3.620914%	3.620914	95	4.410121%	4.410121	95	1.338818%	1.338818
30	100	1.298013%	1.298013	100	2.535617%	2.535617	100	3.798139%	3.798139	100	0.518488%	0.518488
31	105	0.559667%	0.559667	105	1.563862%	1.563862	105	3.099749%	3.099749	105	0.144540%	0.144540
32	110	0.170040%	0.170040	110	0.784856%	0.784856	110	2.315662%	2.315662	110	0.024018%	0.024018
33	115	0.026799%	0.026799	115	0.263651%	0.263651	115	1.447171%	1.447171	115	0.001559%	0.001559
34	120	0.000849%	0.000849	120	0.029804%	0.029804	120	0.497793%	0.497793	120	0.000011%	0.000011
35	total	100.000000%	100.000000	Total	100.000000%	100.000000	Total	100.000000%	100.000000	Total	100.000000%	100.000000
Sheet1

Formulas:
B4: =B1/2
B5: =B3/12
B6: =B2*B1/60
B8: =B6*(BETA.DIST(B4, B7, B7, 1, 0, B1) - BETA.DIST(B4-5, B7, B7, 1, 0, B1))
B11: =BETA.DIST(A11, B$7, B$7, 1, 0, B$1) - BETA.DIST(A11-5, B$7, B$7, 1, 0, B$1)
C11: =B$6*B11
B35: =SUM(B11:B34)
C35: =SUM(C11:C34)
Copy B11:C11 into B12:C34
Copy formulas in B:C into similar columns (e.g. E:F)
-----
Solver set-up:
Set objective: B8
To value of: 10.4166666666667 (B5)
By changing: B7
Method: GRG Nonlinear
Options:
All Methods > Constraint Precision: 0.000000000001 (arbitrary)
GRG Nonlinear > Convergence: 0.000000000001 (arbitrary)

You can see formulas by selecting or hovering the cursor over each cell. Also note the horizontal and vertical XL2BB scrollbars below and to the right of the table.

The formula in B8 is used by Solver to derive alpha and beta (B7) such that B8 equals the value in B5, which is the peak rainfall in the middle 5min interval.

The formulas in B11:B34 generate the probability distribution, which s used as percentages of the total in column C11:C34.

By setting alpha and beta to the same value (B7), we are assured that the area under half the curve (C11:C20) is indeed 50% of the total.

A chart of the first 3 distributions in columns A:I for peak amounts of 125mm, 100mm and 75mm is shown below.

Note that the green curve for the 75mm peak is not what you expected. Nevertheless, I believe it is correct and necessary. With the lower peak amount, the area under the curve must spread out in order to equal the total amount.

For demonstration purposes, it might be better to choose a peak of 150mm (columns J:L) in addition to 125mm and 100mm. Then the chart of the 3 distributions is similar to your expections.

 

[Kevin Barton]

​

Wow joeu2004 - this is looking extremely promising.
I haven't had chance to dig around and play with it to see if I can get it to automatically update when I change Peak Rate value or extend range when updating Duration, but it definitely has potential!
Out of interest, why doesn't the data update when the peak amt value is changed?

 

[joeu2004]

why doesn't the data update when the peak amt value is changed?

I'm not sure I understand the question.

Peak amt per se (e.g. B5) is derived from peak rate (B3).

So I would not change peak amt directly. (Although I guess we could, since only B5 depends on B3.)

However, if we change peak rate, nothing else (except peak amt) changes because everything depends on alpha/beta in B7, which is derived using Solver.

(Errata: the tag "solver" should be C7, not C8. C8 might read "solver obj".)

So, after changing peak rate (or peak amt, arguably), we must re-run Solver, specifying the correct set of cells.

For example, if we change B3 from 125 to 200, click Data > Solver and make the following changes:

Set objective: B8
To value: 16.6666666666667 (copy from B5)
By changing: B7
Click Solve

Then the data in columns B11:C34 and the charts do change.

You might want to make the charts longer, by dragging the lower border, in order to exaggerate the differences in the curves. They appear to be flatter because the limits of the y-axis change automatically.

-----

That said, my design is lazy insofar as we can not change duration (B1) easily.

More correctly, we can change it, and the subsequent changes (after re-running Solver) are correct.

But the data in B11:C34 and the charts are incomplete because I hardcode the limit in B34.

Do you need that to be more flexible?

 

[Kevin Barton]

I'm not sure I understand the question.

Peak amt per se (e.g. B5) is derived from peak rate (B3).

So I would not change peak amt directly. (Although I guess we could, since only B5 depends on B3.)

However, if we change peak rate, nothing else (except peak amt) changes because everything depends on alpha/beta in B7, which is derived using Solver.

(Errata: the tag "solver" should be C7, not C8. C8 might read "solver obj".)

So, after changing peak rate (or peak amt, arguably), we must re-run Solver, specifying the correct set of cells.

For example, if we change B3 from 125 to 200, click Data > Solver and make the following changes:

Set objective: B8
To value: 16.6666666666667 (copy from B5)
By changing: B7
Click Solve

Then the data in columns B11:C34 and the charts do change.

You might want to make the charts longer, by dragging the lower border, in order to exaggerate the differences in the curves. They appear to be flatter because the limits of the y-axis change automatically.

-----

That said, my design is lazy insofar as we can not change duration (B1) easily.

More correctly, we can change it, and the subsequent changes (after re-running Solver) are correct.

But the data in B11:C34 and the charts are incomplete because I hardcode the limit in B34.

Do you need that to be more flexible?

Thanks joeu2004
I'm not familiar with Solver so will dig into that to better understand this part of the tool. I hadn't realised it was something that required running and presumably it's not something that can be made to run automatically when a variable is changed?

Changing duration
Ultimately, yes, I am trying to create a tool whereby we can generate, say, three sets of data based on core variables of duration, ave. intensity, with three different peak intensities applied.
We will need to vary both the duration and average intensity to generate data for different sites and storms, so I had assumed I would need to extend the charts to allow more duration intervals for longer storms and change the hard-coded limit as you point out.

I'll have a play around with it some more once I've got to grips with Solver and whether this can be automated. As this data generator will sit in the middle of a bigger tool that generates the three variables and then takes the resulting data and processes it some more, automating the solver part is pretty critical.

 

[joeu2004]

I'm not familiar with Solver [....] presumably it's not something that can be made to run automatically when a variable is changed?

Yes, it could -- with some difficulty. We would need a Worksheet_Change event macro that looks for changes in particular cells and runs Solver if they occur.

I'm afraid that I do not have time to implement that. And I don't know how you feel about VBA code.

-----

I would need to extend the charts to allow more duration intervals for longer storms and change the hard-coded limit as you point out. [.... This] data generator will sit in the middle of a bigger tool that generates the three variables [.... And] automating the solver part is pretty critical.

As we say in the business, all of that is SMOP (a "simple matter of programming"). And the details would depend on the specifics of the "bigger tool" that you implement.

Unfortunately, again, I do not have time to generalize my design. Consider it a "proof of concept" -- an indication of the direction that you might want to go. I suggest that you hire an Excel "programmer" to generalize the concept, if no one else here has the time to do it. (It's a big ask.)

Good luck!

 

[Kevin Barton]

Yes, it could -- with some difficulty. We would need a Worksheet_Change event macro that looks for changes in particular cells and runs Solver if they occur.

I'm afraid that I do not have time to implement that. And I don't know how you feel about VBA code.

-----



As we say in the business, all of that is SMOP (a "simple matter of programming"). And the details would depend on the specifics of the "bigger tool" that you implement.

Unfortunately, again, I do not have time to generalize my design. Consider it a "proof of concept" -- an indication of the direction that you might want to go. I suggest that you hire an Excel "programmer" to generalize the concept, if no one else here has the time to do it. (It's a big ask.)

Good luck!

I've done some VBA coding on previous projects - I'm novice but it sounds like I should be able to get the change event macro up and running.
Thanks so much for what you've created and the advice - it's massive help.
I want to play around with the 'tool' some more myself to get a clearer idea of how it needs to work before reaching out to a programmer.
Thanks again