dividing a number up evenly into a year
Hi,
I've been trying to make a formula that divides any whole number up into a year based on the current month. Let me explain with an example. Number to divide 17 Result: month-- 1 2 3 4 5 6 7 8 9 10 12 Divided number-- 1 1 2 1 1 3 1 1 2 1 2 so the divided number still sums up to 17 but is evenly distributed through the year even though it is an odd #. Any help/Ideas would be greatly appreciated. Thanks, Josh |
dividing a number up evenly into a year
In A1 enter 17 (value to be distributed)
In A2 enter 12 ( the number of months) In B1 enter: = ROUND(ROW()/$A$2*$A$1,0) In B2 enter: = IF(ROW()<=$A$2,ROUND(ROW()/$A$2*$A$1,0)-SUM(B$1:B1),"") and copy down. In A1 thru B12 we now see: 17 1 12 2 1 2 1 2 1 1 2 1 2 1 Have a Great Day! -- Gary''s Student - gsnu200856 "jxbeeman" wrote: Hi, I've been trying to make a formula that divides any whole number up into a year based on the current month. Let me explain with an example. Number to divide 17 Result: month-- 1 2 3 4 5 6 7 8 9 10 12 Divided number-- 1 1 2 1 1 3 1 1 2 1 2 so the divided number still sums up to 17 but is evenly distributed through the year even though it is an odd #. Any help/Ideas would be greatly appreciated. Thanks, Josh |
dividing a number up evenly into a year
"jxbeeman" wrote:
I've been trying to make a formula that divides any whole number up into a year based on the current month. Try the following in B2 and copy down through B13: =MAX(0,ROUND($A$1*A2/12-SUM($B$1:B1),0)) That assumes that the number to be divided (e.g. 17) is in A1, and the month numbers are in A2:A13. That also assumes that B1 is empty or text. If you cannot count on that, B2 must have a different formula, namely: =ROUND($A$1/12,0). Note that this assumes an even weighting of 1/12 (about 8.3%) in each month, as you requested. With 17, that produces only the numbers 1 and 2, never 3. If you want a different (uneven) distribution, that can be accommodated by putting the weighting factors in a parallel column. Post back for details, if necessary. ----- original message ----- "jxbeeman" wrote in message ... Hi, I've been trying to make a formula that divides any whole number up into a year based on the current month. Let me explain with an example. Number to divide 17 Result: month-- 1 2 3 4 5 6 7 8 9 10 12 Divided number-- 1 1 2 1 1 3 1 1 2 1 2 so the divided number still sums up to 17 but is evenly distributed through the year even though it is an odd #. Any help/Ideas would be greatly appreciated. Thanks, Josh |
dividing a number up evenly into a year
If your 17 is in A1, and your 1 to 12 in A2 to L2, put in A3 the formula
=ROUND($A1/12,0), and in B3 the formula =ROUND(B2*$A1/12,0)-SUM($A3:A3). Copy B3 across through to L3. -- David Biddulph "jxbeeman" wrote in message ... Hi, I've been trying to make a formula that divides any whole number up into a year based on the current month. Let me explain with an example. Number to divide 17 Result: month-- 1 2 3 4 5 6 7 8 9 10 12 Divided number-- 1 1 2 1 1 3 1 1 2 1 2 so the divided number still sums up to 17 but is evenly distributed through the year even though it is an odd #. Any help/Ideas would be greatly appreciated. Thanks, Josh |
All times are GMT +1. The time now is 11:27 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com