ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   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)

[email protected]

evenly distribute numbers across a range
 
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!!

ExcelBanter AI

Answer: evenly distribute numbers across a range
 
To evenly distribute 7 apples across 12 months, you can use the following steps:
  1. Determine the base number of apples to sell in each month by dividing the total number of apples by the number of months. In this case,
    Code:

    7 apples divided by 12 months equals 0.58 apples per month
    .
  2. Round the base number of apples to either 0 or 1, depending on whether you can sell part of an apple or not. In this case, since you cannot sell part of an apple, you would round 0.58 down to 0.
  3. Determine the number of months where you will sell 1 apple instead of 0. In this case, since you have 7 apples to sell and you rounded down to 0, you will need to sell 1 apple in 7 months.
  4. Choose which months you want to sell 1 apple in. If you want to weight the distribution towards the beginning or end of the time frame, you can choose to sell more apples in those months. For example, you could sell 2 apples in the first month and 1 apple in each of the next 6 months, or you could sell 1 apple in each of the first 6 months and 2 apples in the last month.
  5. Enter the number of apples to sell in each month into a spreadsheet or other tool. You can use a simple table with one column for the month and one column for the number of apples to sell.
  6. Adjust the distribution as needed. If you want to sell more apples in certain months, you can adjust the numbers accordingly. Just make sure that the total number of apples sold still adds up to 7.

By following these steps, you can evenly distribute a set number of apples across a range of months, while also weighting the distribution towards certain months if desired.

Gary''s Student

evenly distribute numbers across a range
 
In 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!!


[email protected]

evenly distribute numbers across a range
 
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!

Gary''s Student

evenly distribute numbers across a range
 
Column 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!


Glenn

evenly distribute numbers across a range
 
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!!



A1 = 7 (for Apples)
A2 = 12 (for Months)

B1 = ROUND(ROW()/$A$2*$A$1,0)
B2 = IF(ROW()<=$A$2,ROUND(ROW()/$A$2*$A$1,0)-SUM(B$1:B1),"")

Copy B2 down as needed.

UrbanS

Upgrade
 
Hi,

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 04:55 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com