- **Excel Worksheet Functions**
(*https://www.excelbanter.com/excel-worksheet-functions/*)

- - **evenly distribute numbers across a range**
(*https://www.excelbanter.com/excel-worksheet-functions/214490-evenly-distribute-numbers-across-range.html*)

evenly distribute numbers across a rangeI'm trying to create a formula or script that will accomplish the
following Lets say I'm going to sell 7 apples over the next 12 months I'm trying to create a script that will evenly distribute the 7 apple sales across the 12 months I cannot sell part of an apple so the numbers must be zero or one(this is what has me jammed up) The script will allow for more than one apple to be sold in a month if I were to reduce the months from 12 to 6. I would like a way to 'weight' the distribution having more sales occur at the beginning or the end of the time frame. Any suggestions would be greatly appreciated!! |

evenly distribute numbers across a rangeIn A1 enter the amount of items:
7 In B1 enter the number of months: 12 In C1 enter: =ROUNDUP(A1/B1,0) this is the "nominal" amount per month displays 1 In D1 enter: =C1 In D2 enter: =IF(SUM($D$1:D1)=$A$1,"",IF(SUM($D$1:D1)+$C$1$A$1 ,$A$1-SUM($D$1:D1),$C$1)) and copy down. -- Gary''s Student - gsnu200822 " wrote: I'm trying to create a formula or script that will accomplish the following Lets say I'm going to sell 7 apples over the next 12 months I'm trying to create a script that will evenly distribute the 7 apple sales across the 12 months I cannot sell part of an apple so the numbers must be zero or one(this is what has me jammed up) The script will allow for more than one apple to be sold in a month if I were to reduce the months from 12 to 6. I would like a way to 'weight' the distribution having more sales occur at the beginning or the end of the time frame. Any suggestions would be greatly appreciated!! |

evenly distribute numbers across a rangeThe result I got was seven 1s in cells D1-D7. I'm trying to get seven
1s distributed as evenly as possible across cells D1-D12. I really appreciate you taking the time to help! |

evenly distribute numbers across a rangeColumn D represents an even spread value-to-value, but an even spread in time.
In addition to the formulas I posted, In E1 enter: =RAND() and copy down thru E12 In F1 enter: =INDEX($D$1:$D$12,RANK(E1,$E$1:$E$12)) and copy down thru F12 Column F should be what you need. It takes column D and "randomizes" it over the 12 months -- Gary''s Student - gsnu2007k " wrote: The result I got was seven 1s in cells D1-D7. I'm trying to get seven 1s distributed as evenly as possible across cells D1-D12. I really appreciate you taking the time to help! |

evenly distribute numbers across a range |

UpgradeHi,
a great example. How can I upgrade it so that I can sell additional 5 oranges and 3 pears in a way, that I can only sell one fruit at a time (I can only sell oranges or apples or pears on different days never any of them together)? Best regards, Urban |

All times are GMT +1. The time now is 09:26 PM. |

Powered by vBulletin® Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.

ExcelBanter.com