Hi,
I am building a model that calculate condo sales. one of the tabs takes the total sales and distributes it accross a set number of months (from another tab). I want to be able to have the sheet calculate automatically several sales senarios, flat distribution, normal distribution (inverted U), and a U shape, as well as a sine curve if anyone is feeling ambitious. The model already adjusts the correct number of months and total sales based on other tabs, the number of months changes and so the flow changes, so there has to be a standard formula that will do this automatically without doing it in a specific box.
I am having the following problems:
1. Flat distribution: I am distributing the sales accross each month, but rounding up to get whole units, when there are an odd number of total sales the model will show sales above the total number. I was trying to use an if number to say when sum of sales is greater than the total sales, subtract the difference between the sum and the total from the last month. I can't seem to do this without creating a circular reference.
2. normal distribution: Same thing as for the flat distribution.
3. U - Distribution: I can't seem to find the formula that will invert the numbers from a normal distribution inversely.
4. Sine: I am not sure if anyone will want to takle this, but I would like to create a sine distribution that alternates from high points/low points with the user being able to define what constitues a high point(say 5 sales a month) and a low point (1 sale a month) and have them define in which month the low/high points occure and ahve the model alternate between them gradually over the span of the total months.
I have attached the file I am working with if anyoen wants to take a look.
Thanks
Bookmarks