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

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

"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

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