Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
divide a month's target into weeks
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. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
divide a month's target into weeks
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) "Shazzer" wrote: 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. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
divide a month's target into weeks
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 "Shazzer" wrote in message ... 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. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
divide a month's target into weeks
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. "JLatham" wrote: 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) "Shazzer" wrote: 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. |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
divide a month's target into weeks
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. "Shazzer" wrote: 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. "JLatham" wrote: 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) "Shazzer" wrote: 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Target cell reference moves when target is cut and pasted | Excel Discussion (Misc queries) | |||
Finding this month's cell | Excel Worksheet Functions | |||
PivotTable contains last month's dates | Excel Discussion (Misc queries) | |||
Divide one row over other row I dont wont to divide one number | Excel Discussion (Misc queries) | |||
calculate weeks from a start date ( not yr weeks) | Excel Worksheet Functions |