Gaussian curve

paul higginson

New Member
Joined
Sep 6, 2023
Messages
4
Office Version
  1. 365
Platform
  1. Windows
Hello,

I trying to realistically estimate capital expenditure for a building project.
For example if the project was to cost £12M and last 12 months the expenditure would be highest over months 3/4/5/6 etc but much less over 10/11/12.
Can anyone help me take this data and distribute it over a Gaussian curve?

Thank you.

Paul
 

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.
It has been over 15 years since I've done anything with with statistics.

So don't look too hard to me for an explanation on the math or how to adjust it. If you bring the amplitude closer to 0, it will increase the amount of money being allocated to the later months. If you bring the amplitude further from 0, the less money will be allocated to those months.

EXCEL
ABCDEFGH
1MonthMapNormX$ Per MonthTotal:12,000,000
21-10.2419712903649$924,278.42 Amplitude-1
32-0.727270.3062353674824$1,169,756.09 XSUM:37,698,364
43-0.454550.3597874317439$1,374,310.68 DIFF:0.3183162
54-0.181820.3924024708829$1,498,896.45
650.0909090.3972974767566$1,517,593.46
760.3636360.3734194481028$1,426,383.70
870.6363640.3258173909810$1,244,555.86
980.9090910.2639063166874$1,008,067.29
1091.1818180.1984372381239$757,987.00
11101.4545450.1385131662158$529,091.88
12111.7272730.0897551077057$342,844.78
131220.053991647891.6$206,234.39
14SUM:$12,000,000.00
Sheet3
Cell Formulas
RangeFormula
A2:A13A2=SEQUENCE(12)
B2:B13B2=$H$2+(($H$2*-3)/(MAX($A$2#)-MIN($A$2#))*(A2-MIN($A$2#)))
C2:C13C2=NORM.S.DIST(B2,FALSE)
D2:D13D2=C2:C13*H1
E2:E13E2=D2#*H4
H3H3=SUM(D2#)
H4H4=H1/H3
E14E14=SUM(E2#)
Dynamic array formulas.
 
Upvote 0
Dear lrobbo314,
Thank you, that's really helpful.
I can see that this plots a graph close to what I'm after. See below
I can see how to adjust the periods and the overall value together with the peak point, but is it possible to adjust this to obtain a start and finish at zero or near to zero?

1694076648517.png
 
Upvote 0
Like I said, I'm not great with statistics, so I'm just playing around with numbers here. But, how about this...?

Gaussian Distribution
ABCDEFGH
1MonthMapNormX$ Per MonthTotal:12,000,000
2197.31.07E-121.28E-05$0.00 Amplitude-1.7
31-1.70.0940491128589$579,009.30 XSUM:23,390,068
42-1.20.1941862330233$1,195,498.51 DIFF:0.5130383
53-0.70.3122543747047$1,922,378.58
64-0.20.3910434692512$2,407,438.36
750.30.3813884576654$2,347,998.49
860.80.2896923476299$1,783,474.20
971.30.1713692056423$1,055,023.73
1081.80.07895947401.9$486,053.42
1192.30.028327339924.5$174,394.25
12102.80.00791594985.42$48,731.15
13197.31.07E-121.28E-05$0.00
14SUM:$12,000,000.00
Sheet3
Cell Formulas
RangeFormula
B2:B13B2=$H$2+((9)/(MAX($A$2:$A$13)-MIN($A$2:$A$13))*($A$2:$A$13-MIN($A$2:$A$13)))
C2:C13C2=NORM.S.DIST(B2#,FALSE)
D2:D13D2=C2#*H1
E2:E13E2=D2#*H4
H3H3=SUM(D2#)
H4H4=H1/H3
E14E14=SUM(E2#)
Dynamic array formulas.
 
Upvote 1
Solution

Forum statistics

Threads
1,223,236
Messages
6,170,912
Members
452,366
Latest member
TePunaBloke

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
Back
Top