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

Kevin Barton

New Member
Joined
Jan 8, 2021
Messages
5
Office Version
  1. 365
Platform
  1. MacOS
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.:
1610148424629.gif

Many thanks.
 

Some videos you may like

Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK

Kevin Barton

New Member
Joined
Jan 8, 2021
Messages
5
Office Version
  1. 365
Platform
  1. MacOS
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

Well-known Member
Joined
Oct 22, 2012
Messages
1,347
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
I don't think excel has this capability. Maybe others have best answers or option.
 

joeu2004

Well-known Member
Joined
Mar 2, 2014
Messages
2,975
Office Version
  1. 2010
Platform
  1. Windows

ADVERTISEMENT

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
ABCDEFGHIJKL
1duration120minDuration120minDuration120minDuration120min
2avg rate50mm/hrAvg rate50mm/hrAvg rate50mm/hrAvg rate50mm/hr
3peak rate125mm/hrPeak rate100mm/hrPeak rate75mm/hrPeak rate150mm/hr
4peak at...60minPeak at...60minPeak at...60minPeak at...60min
5peak amt10.41666666666670mmPeak amt8.33333333333333mmPeak amt6.25000000000000mmPeak amt12.50000000000000mm
6total100mmTotal100mmTotal100mmTotal100mm
7alpha/beta5.24987709478519alpha/beta3.41720623275479alpha/beta2.00835395577078alpha/beta7.53039699303080
810.41666666666670solver8.33333333333334solver6.24999999999978solver12.50000000000000solver
9
10time%totalrainfall (mm)time%totalrainfall (mm)time%totalrainfall (mm)time%totalrainfall (mm)
1150.000849%0.00084950.029804%0.02980450.497793%0.49779350.000011%0.000011
12100.026799%0.026799100.263651%0.263651101.447171%1.447171100.001559%0.001559
13150.170040%0.170040150.784856%0.784856152.315662%2.315662150.024018%0.024018
14200.559667%0.559667201.563862%1.563862203.099749%3.099749200.144540%0.144540
15251.298013%1.298013252.535617%2.535617253.798139%3.798139250.518488%0.518488
16302.418497%2.418497303.620914%3.620914304.410121%4.410121301.338818%1.338818
17353.870254%3.870254354.737079%4.737079354.935245%4.935245352.745755%2.745755
18405.525302%5.525302405.804645%5.804645405.373207%5.373207404.733379%4.733379
19457.201714%7.201714456.751911%6.751911455.723792%5.723792457.101439%7.101439
20508.695760%8.695760507.518192%7.518192505.986848%5.986848509.478685%9.478685
21559.816438%9.816438558.056136%8.056136556.162272%6.1622725511.413309%11.413309
226010.416667%10.416667608.333333%8.333333606.250000%6.2500006012.500000%12.500000
236510.416667%10.416667658.333333%8.333333656.250000%6.2500006512.500000%12.500000
24709.816438%9.816438708.056136%8.056136706.162272%6.1622727011.413309%11.413309
25758.695760%8.695760757.518192%7.518192755.986848%5.986848759.478685%9.478685
26807.201714%7.201714806.751911%6.751911805.723792%5.723792807.101439%7.101439
27855.525302%5.525302855.804645%5.804645855.373207%5.373207854.733379%4.733379
28903.870254%3.870254904.737079%4.737079904.935245%4.935245902.745755%2.745755
29952.418497%2.418497953.620914%3.620914954.410121%4.410121951.338818%1.338818
301001.298013%1.2980131002.535617%2.5356171003.798139%3.7981391000.518488%0.518488
311050.559667%0.5596671051.563862%1.5638621053.099749%3.0997491050.144540%0.144540
321100.170040%0.1700401100.784856%0.7848561102.315662%2.3156621100.024018%0.024018
331150.026799%0.0267991150.263651%0.2636511151.447171%1.4471711150.001559%0.001559
341200.000849%0.0008491200.029804%0.0298041200.497793%0.4977931200.000011%0.000011
35total100.000000%100.000000Total100.000000%100.000000Total100.000000%100.000000Total100.000000%100.000000
Sheet1
Rich (BB code):
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.

rainfall chart 75-125mm.jpg


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.

rainfall chart 100-150mm.jpg
 
Solution

Kevin Barton

New Member
Joined
Jan 8, 2021
Messages
5
Office Version
  1. 365
Platform
  1. MacOS

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

Well-known Member
Joined
Mar 2, 2014
Messages
2,975
Office Version
  1. 2010
Platform
  1. Windows

ADVERTISEMENT

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

New Member
Joined
Jan 8, 2021
Messages
5
Office Version
  1. 365
Platform
  1. MacOS
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

Well-known Member
Joined
Mar 2, 2014
Messages
2,975
Office Version
  1. 2010
Platform
  1. Windows
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

New Member
Joined
Jan 8, 2021
Messages
5
Office Version
  1. 365
Platform
  1. MacOS
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
 

Watch MrExcel Video

Forum statistics

Threads
1,122,977
Messages
5,599,151
Members
414,292
Latest member
kingshuk963

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top