Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Divide the total in one cell into up to 6 others
Hi,
Does anyone know how to divide the number in one cell and have up to 6 other cells equal the original number? i.e. 65 = 11, 11, 11, 11, 11, 10. Thanks for any help Byron |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Divide the total in one cell into up to 6 others
hi
assuming the 65 is on the a2 on b2 use: =$a2/6 copy it util f2 and on g2 use a2-sum(b2:f2) hth -- regards from Brazil Thanks in advance for your feedback. Marcelo "Dos Equis" escreveu: Hi, Does anyone know how to divide the number in one cell and have up to 6 other cells equal the original number? i.e. 65 = 11, 11, 11, 11, 11, 10. Thanks for any help Byron |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Divide the total in one cell into up to 6 others
Just a slight tweak - use:
=ROUND($a2/6,0) Hope this helps. Pete Marcelo wrote: hi assuming the 65 is on the a2 on b2 use: =$a2/6 copy it util f2 and on g2 use a2-sum(b2:f2) hth -- regards from Brazil Thanks in advance for your feedback. Marcelo "Dos Equis" escreveu: Hi, Does anyone know how to divide the number in one cell and have up to 6 other cells equal the original number? i.e. 65 = 11, 11, 11, 11, 11, 10. Thanks for any help Byron |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Divide the total in one cell into up to 6 others
Thanks guys, that function helps, but it's returning incorrect data.
If $a2 is 47, $a3-9 return 45 and the sum in $a10 is 270. Not what I was looking for. Should have ended with 12,12,12,11 in the first 4 cells and totaled 47 in $a10 It does give me something to work with though so thank you. If you have any other ideas, I'll still be checking this post.. Thanks again, Byron Pete_UK wrote: Just a slight tweak - use: =ROUND($a2/6,0) Hope this helps. Pete Marcelo wrote: hi assuming the 65 is on the a2 on b2 use: =$a2/6 copy it util f2 and on g2 use a2-sum(b2:f2) hth -- regards from Brazil Thanks in advance for your feedback. Marcelo "Dos Equis" escreveu: Hi, Does anyone know how to divide the number in one cell and have up to 6 other cells equal the original number? i.e. 65 = 11, 11, 11, 11, 11, 10. Thanks for any help Byron |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Divide the total in one cell into up to 6 others
Just check your formula in A3 to A9 again - it looks like you have
=$A2-2 for it to give you 45 in those cells. In your original post, you said you wanted to split the number into 6 cells, whereas you now quote 47 split into 4 - is the number of cells variable, and if so, where (which cell) would this be located? Pete Dos Equis wrote: Thanks guys, that function helps, but it's returning incorrect data. If $a2 is 47, $a3-9 return 45 and the sum in $a10 is 270. Not what I was looking for. Should have ended with 12,12,12,11 in the first 4 cells and totaled 47 in $a10 It does give me something to work with though so thank you. If you have any other ideas, I'll still be checking this post.. Thanks again, Byron Pete_UK wrote: Just a slight tweak - use: =ROUND($a2/6,0) Hope this helps. Pete Marcelo wrote: hi assuming the 65 is on the a2 on b2 use: =$a2/6 copy it util f2 and on g2 use a2-sum(b2:f2) hth -- regards from Brazil Thanks in advance for your feedback. Marcelo "Dos Equis" escreveu: Hi, Does anyone know how to divide the number in one cell and have up to 6 other cells equal the original number? i.e. 65 = 11, 11, 11, 11, 11, 10. Thanks for any help Byron |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Divide the total in one cell into up to 6 others
Pete,
I checked my cells and then copy and pasted the formula's paosted here to the cells, same result. The numbers go in cells C12 - C20, I put in the number of items to be grouped with a minimum of 10 and maximum of 15. I currently have from 12 to 69 in a group. I was thinking a formula that started by dividing the number of items by 10 would tell me how many groups I will have but it also has to account for any left overs. For instance, if I start with 18, only 15 can go in a group and the other 3 have to be accounted for. What I'd like is something like this formula in an outside block: (Block C12) 47 (Block A137) =Round C12/10(4) (Block A138) =If[4 cells](D12:G12)<C12,D12+1 Loop, E12+1, loop, F12+1... I know the code above is invalid, but that's the kind of thing I'm looking for. I should evaluate the number of cells needed, total the sum of those cells, compare that sum to the number of items listed and add one to consecutive cells until the number of items is reached. Yes, I'm asking alot, but it should be possible and probably easy, just not obvious. Anyway, Thnaks for your help sofar, Hey, I just came up with this: =IF(N223,ROUND(C22/N22,0),0) That's the 4th cell in line and N22 divides C22/10 giving me 4 in that cell. With that formula, I end up with 12 in the frist 4 cells and 0 in the last 2 for a total of 48 instead of the 47 I'm looking for. Any thoughts? Pete_UK wrote: Just check your formula in A3 to A9 again - it looks like you have =$A2-2 for it to give you 45 in those cells. In your original post, you said you wanted to split the number into 6 cells, whereas you now quote 47 split into 4 - is the number of cells variable, and if so, where (which cell) would this be located? Pete Dos Equis wrote: Thanks guys, that function helps, but it's returning incorrect data. If $a2 is 47, $a3-9 return 45 and the sum in $a10 is 270. Not what I was looking for. Should have ended with 12,12,12,11 in the first 4 cells and totaled 47 in $a10 It does give me something to work with though so thank you. If you have any other ideas, I'll still be checking this post.. Thanks again, Byron Pete_UK wrote: Just a slight tweak - use: =ROUND($a2/6,0) Hope this helps. Pete Marcelo wrote: hi assuming the 65 is on the a2 on b2 use: =$a2/6 copy it util f2 and on g2 use a2-sum(b2:f2) hth -- regards from Brazil Thanks in advance for your feedback. Marcelo "Dos Equis" escreveu: Hi, Does anyone know how to divide the number in one cell and have up to 6 other cells equal the original number? i.e. 65 = 11, 11, 11, 11, 11, 10. Thanks for any help Byron |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Divide the total in one cell into up to 6 others
Oh, and it was gining me 45 because I was refering to the wrong cell.
C21 instead of C22, large difference. Dos Equis wrote: Pete, I checked my cells and then copy and pasted the formula's paosted here to the cells, same result. The numbers go in cells C12 - C20, I put in the number of items to be grouped with a minimum of 10 and maximum of 15. I currently have from 12 to 69 in a group. I was thinking a formula that started by dividing the number of items by 10 would tell me how many groups I will have but it also has to account for any left overs. For instance, if I start with 18, only 15 can go in a group and the other 3 have to be accounted for. What I'd like is something like this formula in an outside block: (Block C12) 47 (Block A137) =Round C12/10(4) (Block A138) =If[4 cells](D12:G12)<C12,D12+1 Loop, E12+1, loop, F12+1... I know the code above is invalid, but that's the kind of thing I'm looking for. I should evaluate the number of cells needed, total the sum of those cells, compare that sum to the number of items listed and add one to consecutive cells until the number of items is reached. Yes, I'm asking alot, but it should be possible and probably easy, just not obvious. Anyway, Thnaks for your help sofar, Hey, I just came up with this: =IF(N223,ROUND(C22/N22,0),0) That's the 4th cell in line and N22 divides C22/10 giving me 4 in that cell. With that formula, I end up with 12 in the frist 4 cells and 0 in the last 2 for a total of 48 instead of the 47 I'm looking for. Any thoughts? Pete_UK wrote: Just check your formula in A3 to A9 again - it looks like you have =$A2-2 for it to give you 45 in those cells. In your original post, you said you wanted to split the number into 6 cells, whereas you now quote 47 split into 4 - is the number of cells variable, and if so, where (which cell) would this be located? Pete Dos Equis wrote: Thanks guys, that function helps, but it's returning incorrect data. If $a2 is 47, $a3-9 return 45 and the sum in $a10 is 270. Not what I was looking for. Should have ended with 12,12,12,11 in the first 4 cells and totaled 47 in $a10 It does give me something to work with though so thank you. If you have any other ideas, I'll still be checking this post.. Thanks again, Byron Pete_UK wrote: Just a slight tweak - use: =ROUND($a2/6,0) Hope this helps. Pete Marcelo wrote: hi assuming the 65 is on the a2 on b2 use: =$a2/6 copy it util f2 and on g2 use a2-sum(b2:f2) hth -- regards from Brazil Thanks in advance for your feedback. Marcelo "Dos Equis" escreveu: Hi, Does anyone know how to divide the number in one cell and have up to 6 other cells equal the original number? i.e. 65 = 11, 11, 11, 11, 11, 10. Thanks for any help Byron |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Compiling macro based on cell values | Excel Discussion (Misc queries) | |||
Help with this conditional IF statement | Excel Discussion (Misc queries) | |||
How do I total range of cells that have checks in checkboxes? | Excel Discussion (Misc queries) | |||
Adding numbers in one cell and showing total in seperate cell | Excel Discussion (Misc queries) | |||
cell color index comparison | New Users to Excel |