Construction Budget distribution using GAMMADIST Function

[ibrahimdaas]

Hi Guys

I am creating a Construction Budget for one of the real estate development project, but I wanted to use distribution for the payments to not to follow the usual normal distribution ( a.k.a : S Curve distribution ) so for me the solution was to use the Function GAMMADIST in order to have a curve that is front loaded or back loaded with cost – is in attached Excel file- , but my problem is that I will get always more than 100% , and that is not the correct way to go for distributing the entire number of cost and not to have any leftover.

To understand my problem, you can chose to set the distribution to be : S-curve and will see that the total cost amount is entirely distributed , but when you try to use one of the : Right Skewed or Left Skewed options , you will have a remaining difference in Red Color at the cells B27,B28, & B29.

How can I make both Right Skewed or Left Skewed formula be like the s-curve and not to have any remaining numbers out.

 

[Fluff13]

x posted https://www.mrexcel.com/board/threa...istribution-using-gammadist-function.1255570/
and https://www.excelforum.com/excel-ge...et-distribution-using-gammadist-function.html

 

[vletm]

ibrahimdaas​

As You've read from ... many Forums:

Site Rules - New Users - Please Read

Hi all, Welcome to the Chandoo.org Forums. Posting Rules & Etiquette The Chandoo.org Forums is a collaborative and happy place to learn and expand your Excel knowledge. The Chandoo.org Forums consist of several Sub-Forums based on the type of question/area of Excel you are interested in...

chandoo.org

• Cross-Posting. Generally, it is considered poor practice to cross post. That is to post the same question on several forums in the hope of getting a response quicker.
• If you do cross-post, please put that in your post.
• Also if you have cross-posted and get an Solution elsewhere, have the courtesy of posting the Solution here so other readers can learn from the answer also, as well as stopping people wasting their time on your answered question.

 

[ibrahimdaas]

What is requered from me to do ??

 

[p45cal]

The attached may go some way to help.
Deceptively simple formula in cell G27 and below, with hints:



The result will Spill to the right, so you will only need to copy down (not across).

Derivation of formula (find in defined Names) can be seen in cells G43:G45. These are not necessary and should be deleted; they're there for your info only.
It's a bit dodgy to use the same value for SD in NORMDIST as the beta argument in GAMMA.DIST, but I was only following what you do. I don't know anything about GAMMA.DIST so there's a bit of guesswork of what you want, but it looks good even though it may be mathematical nonsense!

 

[vletm]

ibrahimdaas​

Your What is requered from me to do ??
#1 before posting, You read Forum Rules
#2 post Your thread
#3 follow rules
#4 be patient, others will reply then they'll have time

Your thread
It would be possible ( You could test with Your used functions ) to get differences to zero like below.
Instead to solve Monthly values ...
#1 solve 'total values' from the beginning
#2 solve Monthly values as below ... row 45

... then there won't be any more differences ( =Your named left column ).

 

[Luke M]

What is requered from me to do ??

• Cross-Posting. Generally, it is considered poor practice to cross post. That is to post the same question on several forums in the hope of getting a response quicker.
• If you do cross-post, please put that in your post.
• Also if you have cross-posted and get an Solution elsewhere, have the courtesy of posting the Solution here so other readers can learn from the answer also, as well as stopping people wasting their time on your answered question.