divide a month's target into weeks

[Guest]

Is there a formula which will divide a number up into whole numbers. I.e. if
I have to acheive 25 sales in a month, in a 4 week month, I want to divide it
up into 4 whole numbers to see how many I need each week. So week 3 of the
weeks should read 6 and 1 of the weeks should read 7.

 

[Guest]

In all of the weeks except the last one put a formula such as this:
=INT(25/4)
each of those would display 6
Then in the last week put a formula similar to this (assumes the other 3 are
in B2, B3 and B4)
=25-SUM(B2:B4)

If you need to alter the number of sales frequently, you can put the 25 into
a cell and use that address instead of hard coding 25 into the formulas.
Let's say you put it into cell B1 then the formulas become:
=INT(B$1/4)
and =B$1-SUM(B2:B4)

 

[MartinW]

Hi Shazzer,

Here is a novel approach that may or may not be useful.

Using your example
A1: 25
A2: 4
B1: =A2-1&" x "&INT(A1/A2)
C1: =1&" x "&INT(A1/A2)+MOD(A1,A2)

It needs a bit of refining to be useful, but could be a good starting point.

HTH
Martin

 

[Guest]

Thanks. That seems like a really good idea but unfortunately, for some
reason, the last one is given me the answer -23 instead of 7.

 

[Guest]

What are your actual values, where are they, and what do your formulas look
like. Hard to believe that 25-18 = anything other than 7 in this dimension.

With the following:
In A1: value 4
in B1: value 25

in C1
=INT(B$1/A$1)
in C2
=INT(B$1/A$1)
in C3
=INT(B$1/A$1)
each of those cells should show 6
finally, in C4
=B$1-SUM(C1:C3)
should display 7.