Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old December 23rd 08, 04:22 AM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Nov 2007
Posts: 6
Default 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!!

  #2   Report Post  
Old December 23rd 08, 11:29 AM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 11,058
Default 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$11)=$A$1,"",IF(SUM($D$11)+$C$1$A$1 ,$A$1-SUM($D$11),$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!!

  #3   Report Post  
Old December 23rd 08, 12:01 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Nov 2007
Posts: 6
Default 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!
  #4   Report Post  
Old December 23rd 08, 02:59 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 11,058
Default 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!

  #6   Report Post  
Old January 29th 20, 02:13 PM
Junior Member
 
First recorded activity by ExcelBanter: Jan 2020
Posts: 1
Default 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


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Wrap Text Evenly in Cell Native Excel Discussion (Misc queries) 1 March 29th 08 05:48 PM
two columns range of numbers need to list all numbers in the range arsovat New Users to Excel 2 October 30th 06 09:21 PM
DIVIDE NUMBERS EVENLY NEYS Excel Worksheet Functions 5 August 17th 06 08:48 AM
Divide numbers and distribute evenly. rhon101 Excel Worksheet Functions 0 May 17th 06 02:46 AM
Distribute values evenly in two columns MIckeyLove Excel Discussion (Misc queries) 0 April 27th 06 04:23 PM


All times are GMT +1. The time now is 10:40 PM.

Powered by vBulletin® Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright 2004-2020 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017