Skew Normal Distribution

[nicedanmonkey]

I am trying to turn a formula for a skew normal distribution into an actual excel formula but am having trouble. I'm not sure how to implement integrals in excel. Here is a link to the formula and to the wikipedia article.

Formula: http://upload.wikimedia.org/math/6/2/8/628a96d51ba225eee1157849e8c52c9b.png
Wiki Article: http://en.wikipedia.org/wiki/Skew_normal_distribution

Basically I am going to have a series of X values in column A. In column B I will have the Skew Normal Distribution formula to give me a value for each X value.

I completed a similar spreadsheet with a Normal Distribution Formula

Formula: http://upload.wikimedia.org/math/e/9/9/e995ab18aed54262088171e9e8fc0d8b.png
Wiki Article: http://en.wikipedia.org/wiki/Normal_distribution

This is the excel formula I used
=((1/(SQRT(2*PI()*$I$1))*EXP(-((A2-$I$2)^2)/2*$I$1)))
I1 is the Variance and I2 is the Mean. A2 is the X value.

 

[joeu2004]

[.... deleted by me ....]

 

[kalak]

I haven't done much stats for a few years, but last work was investigating the Double Pareto distribution, which may be of some interest to you.

Look it up on Google if interested.

The stuff I did is buried in an old hard drive in the archives somewhere, so I can't offer much more info.

 

[DaveM123456789]

Alright, I'm just gonna go ahead and paste the data. Let me know if there's anything else I can add to help!

Thanks everyone.

246
99
178
138
234
235
211
197
25
10
188
26
10
244
261
289
152
12
13
144
28
167
276
194
121
157
52
94
134
121
248
103
154
229
256
80
2
67
33
82
84
58
21
108
4
174
316
337
316
87
304
212
114
283
122
75
116
162
13
93
50
285
106
190
143
141
138
247
100
191
157
236
12
207
183
256
199
74
21
25
259
277
130
254
268
73
73
130
187
130
212
142
201
122
19
354
90
229
91
93
171
141
261
375
7
347
108
323
6
326
248
86
272
17
52
527
207
81
64
182
242
251
198
67
235
194
199
144
187
52
190
368
196
152
23
369
212
99
347
312
212
212
53
45
90
143
114
76
152
218
172
303
102
271
96
59
156
312
12
80
227
355
38
26
113
91
117
164
178
138
221
48
218
170
23
201
75
142
137
136
150
149
133
219
221
129
243
208
317
142
163
137
324
183
309
162
151
116
220
226
271
47
226
33
232
131
297
276
222
228
61
184
169
170
226
333
361
178
225
352
346
129
95
182
262
124
184
334
5
80
366
123
223
229
209
239
211
218
247
298
88
30
270
238
166
79
169
245
214
100
163
85
68
67
236
298
263
89
99
16
331
277
258
109
24
59
205
264
235
30
215
171
88
73
282
53
339
613
151
150
411
306
49
541
353
102
354
95
64
150
185
247
122
169
256
157
162
269
268
183
263
357
193
170
128
116
204
284
228
187
159
184
254
261
211
138
185
220
472
215
155
331
401
232
178
184
417
239
344
253
275
256
205
226
270
193
357
297
213
267
534
257
248
269
250
333
232
339
340
236
338
313
318
353
436
445

<!--StartFragment--> <colgroup><col width="87" style="width:65pt"> </colgroup><tbody>
<!--EndFragment--></tbody>

 

[DaveM123456789]

Here's a little more info on what my issue is specifically. I've forecasted the number of job bookings my company will receive every month. I want to then distribute each month's forecasted bookings over the months the jobs themselves will happen (So, a table with month of booking on the x axis, month of job on the y axis). To do that, I'm using this normal distribution curve. But, about 2% goes negative. So, my distribution every month is only accounting for 98% of the total forecasted bookings for that month. Over the course of the year, that 2% adds up to a big discrepancy in total projected sales!

Here's an image of the heat map https://drive.google.com/open?id=0B4YBXwQLOLoYQWM0ZldTSHNER2c (The other factor in the heat map is that our work is seasonal, so along the bottom is the variance to the mean for each month, that variance is factored in, to make sure our monthly projections are properly weighted.)

And here's an image of the normal distribution curve, vs the historical percentile distribution https://drive.google.com/open?id=0B4YBXwQLOLoYTE1QblpOZno2TXM

 

[shg]

This is at the furthest reach of my ability to pretend I know anything about statistics ...

I thought a log-normal distribution sounded appropriate, but you're right, it's a terrible fit.

And skew-normal happily allows negative values.

What looks closer, though not great, is a beta distribution with alpha ~ 2.2 and beta ~ 4.7.

I don't think I can offer any value to this, sorry.

 

[DaveM123456789]

Thanks for trying at least!

How did you calculate the alpha and beta?

 

[shg]

I used Solver to match the PDF of your data and the beta distribution.

 

[DaveM123456789]

Thanks!

 

[shg]

You're welcome.

 

[Smora]

Hey shg,

Would you happen to know if there would be an inverse formulation in Excel for this formula? I am trying to use it for price trends, where I want to represent a bit the distribution around a price point but reflecting in some portion the trend of the curve.

Thanks
S

Maybe like this:



=2/w * NORMDIST((x-e)/w, 0, 1, FALSE) * NORMSDIST(a*(x-e)/w)