
Divide Cell Amounts Between Cells Equally


Search Excel Forum Posts, Tutorials, Macros, Tips, and More
Divide Cell Amounts Between Cells Equally  Excel

View Answers


Hi, I have a chart of numbers and i need the difference inbetween 2 cells (shown here on the left and right) to be divided up equally between the amount of cells inbetween the cells. The amount of cells in the centre here is two but the amount will vary.
Please see below where i have manually worked one out to give you an example.
13.78 14.62 15.46 16.30
15.24  21.17
22.74  29.09
26.84  35.42
32.10  40.71
37.02  49.84
44.60  57.58
49.58  62.05
Thanks
Similar Excel Video Tutorials
Helpful Excel Macros
Format Cells as Text in Excel
 This free Excel macro formats a selection of cells as Text in Excel. This macro applies the Text number format to cells
Similar Topics
Hi, I have a chart of numbers and i need the difference inbetween 2 cells (shown here on the left and right) to be divided up equally between the amount of cells inbetween the cells. The amount of cells in the centre here is two but the amount will vary.
Please see below where i have manually worked one out to give you an example.
A BCD
1 13.78 14.5215.46 16.30
2 15.24 21.17
3 22.74 29.09
4 26.84 35.42
5 32.10 40.71
6 37.02 49.84
7 44.60 57.58
8 49.58 62.05
There are a lot of numbers to the right an left of these cells, so I cant use those cells in the formula unless its way over, about 40 columns to the right.
Could this be made simpler? I know i'd have to do a formula for every column but would something like this work, however I'd need help with translating my suggestion into a formula.
How would I write this formula correctly? =d1a1 divided by 3 plus a1. This would be based on 2 blank columns in the centre as above, this is basically how I worked it out on the calculator. If I did this then wrote a new formula for the remaining column??!
Hi there,
I have an amount in cell C2 that will be broken up into a number of payment installments (selected from a drop down list) in cell C5.
In cells E8, E10, E12, E14, E16 and E18 I have the installment amounts.
So Installment amount #1 = E8, Installment amount #2 = E10, Installment amount #3 = E12, Installment amount #4 = E14, Installment amount #5 = E16, and Installment amount #6 = E18
What I need to do is if the amount in C2 is $100, and the number of installments selected in cell C5 is 4, than I need cells E8, E10, E12 and E14 to divide C2 by cell C5 while cells E16 and E18 remain blank.
If I selected the number of installments in cell C5 to be 6 (which is the maximum amount) than cells E8, E10, E12, E14, E16 and E18 would would get autopopulated with the amount of C2 divided by C5.
Hope that makes sense.
I have constructed a spreadsheet which details the comparison between
expenditures generated in maintaining a boat between two partners (
Adam & Bill ) , who share these equally.
This spreadsheet has the following columns :
A : Date
B: Name
C: Purchase
D: Amount
F: Adam's purchase amount divided by 2
G: Bill's contributions
H: Balance between F & G
"Adam" does all the purchasing
and this amount divided by 2 is shown in column F .
The other partner "Bill" has his contributions detailed in column G
and then the totals in F & G are compared to show the Balance in
column H.
As subsequent purchases and contributions arise and are entered as
described , these appear in column C3, C4...etc with the resultant
BALANCE being constantly adjusted in column H but always shown in cell
H2. Similarly with the totals shown in columns F & G , i.e these also
are adjusted automatically with the data entered in column C but are
also always shown in cells F2 and G2
I would like these amounts now shown in cells H2 , F2 & G2 to appear
on the same row as the latest entry in column C but don't know how to
arrange this so it happens each time I enter a purchase in column C.
Can anyone help with this , please ?
B.N.
I need to find a formula which will give me the Net Amount and Vat Amount totals. (I am in UK, VAT is Value Added Tax and it's 17.5%).
I am using bookkeeping ledger books at the moment but the company will be going computerized in a few weeks but nobody has a clue about spreadsheets really (they can't afford a Sage package!) so although I have a little knowledge it is not enough! So, I have a set of completed figures in front of me which have been worked out with a calculator  now I want to get the same answers using Excel and formulating cells.
So:
I would already have the TOTAL AMOUNT and the NO VAT AMOUNT (No VAT amounts are the element deducted from a sale or work done which is zero rated vat) to work from.
To find the VAT AMOUNT: Using a calculator, take the TOTAL AMOUNT, deduct the NO VAT AMOUNT, multiply that by 7 then divide that by 47. This gives you the VAT amount.
Using the sums from the figures below I would enter on calculator the Total sum 5,745.22 minus no vat sum 101.40 which equals 5,643.82 multiply that by 7 which equals 39,506.74 divide that by 47 which equals 840.56893 (rounded up to 840.57). Finding the Net amount is easy: just deduct the vat amount from the total amount.
TOTAL AMOUNT 5,745.22
NO VAT AMOUNT 101.40
VAT AMOUNT: 840.57
NET AMOUNT: 4,904.65
I've tried my best with cell referencing but obviously I'm doing it wrong because I don't get the right VAT amount of 840.57  I get something like 5,730.12!
There's a nice slice of chocolate cake for those who can advise
All,
Not sure how best to explain my problem, which may be why I'm not having any luck finding my answer through Google or the forum search. Can I have multiple cells be equally divided to total a static amount in another cell?
Example:
I have 4 cells with a value of 25 each that, when totalled, equal 100 (which is the value in another static cell). If I change the value in one of the cells to 10, I want the other 3 cells to automatically update to 30, 30 and 30.
Can this be done and how?
I appreciate any and all feedback on this. I'm stumped on this one.
Matt
Hi Guys thanks for your help with this! I am fine with excel and basic formula's but have no idea how to do this!!
I have a spreadsheet that keeps a track of hours for each month worked. I have a target to work each week and would like the spreadsheet to countdown the target. So i have a cell that has the target in. A cell that has a autosum in to count the amount of hours worked. I have a cell that works out the difference. What i would like is a formula that knows that the 1st x amount of cells have been filled in and then divide the total by the amount of remaining cells.
Please Help!!! please please please!!!
The bit i am struggling with is how to get excel to work out that a certain number of cells out of the 12 are in use and then to know that it needs to divide the total by the remaining!
Many thanks for your help!
Dan
Hi All,
This is my second question in continuation of my 1st question. (Series of QuestionI). I got the total cost per customer ID.
Now these customer ids corresponds to different cities.
I need to divide the total cost of customer id equally among cities and then add the amount for common cities. I'll give you an example:
I have 3 customer ids :
CUST001 with total spent amount $300.
CUST002 with total spent amount $100.
CUST003 with total spent amount $500.
CUST001 can spend money in as many cities as he wants. Let he spent money in NYC, Seatle, L.A.
CUST002 spent his money in NYC.
CUST003 spent his money in L.A. and S.F.
My Final output should be combination of these 2:
First the money should be equally divided among cities.
NYC: $100(from CUST001)
Seatle: $100(from CUST001)
LA: $100(from CUST001)
NYC: $100(from CUST002)
LA: $250(from CUST003)
SF: $250(from CUST003)
and then add the common cities amounts
NYC: $200
LA: $350
Seatle: $100
SF: $250.
In the final output I need not show the customer IDs but only the cities and the total amount spent in them. I have already done this in excel sheet using a macro.
For division of money:
Code:
Private Sub CommandButton5_Click()
Dim rng As Range
Dim k As Integer
Dim kntdups As Integer
Dim mySheet As Worksheet
Dim myRange As Range
Set mySheet = ActiveWorkbook.Worksheets("Customer ID Report")
Set myRange = mySheet.Range("A1").CurrentRegion
mySheet.Activate
Set rng = mySheet.Range("A:A")
k = 1
kntdups = 0
Do While rng.Cells(k).Value ""
Do
If rng.Cells(k) = rng.Cells(k + 1) Then
kntdups = kntdups + 1
k = k + 1
Else
If kntdups >= 1 Then
With rng
For i = k  kntdups To k
.Cells(i).Offset(0, 11).Value = (.Cells(i).Offset(0, 10).Value) / (kntdups + 1)
Next i
End With
Else
With rng
.Cells(k).Offset(0, 11).Value = .Cells(k).Offset(0, 10).Value
End With
End If
kntdups = 0
k = k + 1
End If
Loop Until kntdups = 0
Loop
ActiveWorkbook.Worksheets("Start").Cells(18, 1).Value = "Completed"
End Sub
For addition of money:
Code:
Private Sub CommandButton7_Click()
Dim rng As Range
Dim k As Integer
Dim oldk As Integer
Dim kntdups As Integer
Dim mySheet As Worksheet
Dim assetSheet As Worksheet
Dim myRange As Range
Dim destrange As Range
Dim Lr As Long
Dim rFound As Range
Set mySheet = ActiveWorkbook.Worksheets("Customer ID Report")
Set myRange = mySheet.Range("A1").CurrentRegion
mySheet.Activate
Set assetSheet = ActiveWorkbook.Worksheets("Cities Report")
Set rng = mySheet.Range("E:E")
k = 1
kntdups = 0
Do While rng.Cells(k).Value ""
Do
If rng.Cells(k) = rng.Cells(k + 1) Then
kntdups = kntdups + 1
k = k + 1
Else
If kntdups >= 1 Then
With rng
.Cells(k + 1).EntireRow.Insert
.Cells(k + 1) = .Cells(k)
.Cells(k + 1).Offset(0, 7).FormulaR1C1 = "=SUM(R[" & kntdups + 1 & "]C:R[1]C)"
.Cells(k + 1).Offset(0, 8).Value = .Cells(k + 1).Offset(0, 7).Value
End With
kntdups = 0
oldk = k + 1
k = k + 2
Else
With rng
.Cells(k).Offset(0, 8).Value = .Cells(k).Offset(0, 7).Value
End With
kntdups = 0
oldk = k
k = k + 1
End If
On Error Resume Next
With assetSheet
Set rFound = .Columns(1).Find(What:=rng.Cells(oldk).Value, After:=.Cells(1, 1), LookIn:=xlValues, Lookat:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False)
On Error GoTo 0
If Not rFound Is Nothing Then
rFound.Cells(1, 43).Value = rng.Cells(oldk).Offset(0, 8).Value
End If
End With
End If
Loop Until kntdups = 0
Loop
ActiveWorkbook.Worksheets("Start").Cells(24, 1).Value = "Completed"
End Sub
If these macros can be modified according to Access then also its fine with me or you can help me to find a faster way to do this (These macros are slow).
Hope I have made myself clear here. If there are still some questions from your side please do revert.
Thanks a lot in advance for all your help!!!!!!
Good Evening All,
I seem to be in a bit of a bind. I messed up a report that I was working running. The end result left my "payment amounts" and "credit amounts" on different rows. I am trying to figure out some way (via VB or a formula) to make the amount in the "Credit amount" cell to appear in the same row, next to the Payment amount (that way all of my data is in one single row, not two).
My example is attached. The dollar amounts that I wish to move are in bolded red text. The cells that I want those amounts to go into are highlighted in yellow. For example, My very first Patient name is "Joe Schmoe" (row 2). His payment amount was $20.00 and his credit amount was $15.00. I want the $15.00 to appear in the "Credit Amount" column right next to the $20.00 payment amount.
Can anyone make sense of my question and give me a hand?
P.S. I have Excel 2007, but saved my attachment as .xls, for compatibility reasons. If you know of a simple solution that is specific to Excel 2007, please let me know!
Thank you so much in advance!
Ok, I've been using Excel for many years but I'm not considered an expert by any stretch ...
This problem has me stumped and maybe there is no simple solution...but I'm hoping there is
I have dollar amounts that I need to distribute amongst varying numbers of columns and not have have the total distributed be over or under the original amount by any number of cents. When I simply divide the dollar amount by the number of columns, the total of those columns can sometimes be more or less than the original dollar amount by a few cents.
Code:
e.g.
Dollar # of A B C All Columns
Amount Columns Total

$25.05 2 $12.53 $12.53 $25.06
$11.47 3 $ 3.82 $ 3.82 $ 3.82 $11.46
$25.05 divided into 2 columns gives $12.53 in Column A and $12.53 in Column B. Total of Columns A and B is $25.06. Over by a penny.
$11.47 divided into 3 columns gives $3.82 in Columns A through C. Total of Columns A through C is $41.46. Under by a penny.
I know I could simply always add or take away the pennies from one column, but I would prefer the process to be random or formulated in such a way that the Column to which the extra pennies are added to or taken away from differs in order to be "fair to each column".
Man, I hope I explained that properly.
Thanks for ANY help with this.
Hi guys, just needing someones help please. I want to add up cells diplaying only W (not J cells) to total up the amount shown.
Tipping Comp Rules
Jackpot System
8. Perfect Round:
8.1 An amount of $15 will jackpot each week to go towards the perfect round prize money. The jackpot amount will vary depending on the numbers of players that joins the competition.
e.g.
A minimum of $15 will Jackpot each week
A1 $15 : A2 $30 : A3 $45 : A4 $15 : A5 $30 etc...
B1 J : B2 J : B3 W : B4 J : B5 W etc...
Answer; add cells that equal W only = $75
Thanks
Goma (NASA)
A friend told me that I could probably do this with a macro ... but I have no idea how. Can anybody help me accomplish this?
Here's the problem:
Say I start off with an amount in cell: D104
(For grins, say the amount is: (5)
Now ... say I later enter amounts in cells: H95 through H102
(say, the amount entered in each of these cells is: (1)
Is there a way via a macro or VB to add the amounts in (H95:H102) to the already existing amount in (D104)?
In the example I explained above ... the total would be: (5+1+1+1+1+1+1+1+1)=13
Thank you!
I am trying to use the SUMIF function and it is not working the way I think it should. I have several columns with amounts on a single row. Here is my scenario:
If B2=R, then sum cells K2 through X2. The amount in K2 is a summed amount for numbers in cells C2 through J2, while the amounts in L2 through X2 are not summed amounts. This is what my formula looks like:
=SUMIF(B2,"R",K2:X2)
It is only giving me the amount in cell K2 and not the rest, even though my sum_range in the function formula box says ={70,60,0,0,0,0,0,0,0,0,0,100,0,0}
What am I doing wrong???
Ok, I've been using Excel for many years but I'm not considered an expert by any stretch ...
This problem has me stumped and maybe there is no simple solution...but I'm hoping there is
I have dollar amounts that I need to distribute amongst varying numbers of columns and not have have the total distributed be over or under the original amount by any number of cents. When I simply divide the dollar amount by the number of columns, the total of those columns can sometimes be more or less than the original dollar amount by a few cents.
Code:
e.g.
Dollar # of A B C All Columns
Amount Columns Total

$25.05 2 $12.53 $12.53 $25.06
$11.47 3 $ 3.82 $ 3.82 $ 3.82 $11.46
$25.05 divided into 2 columns gives $12.53 in Column A and $12.53 in Column B. Total of Columns A and B is $25.06. Over by a penny.
$11.47 divided into 3 columns gives $3.82 in Columns A through C. Total of Columns A through C is $41.46. Under by a penny.
I know I could simply always add or take away the pennies from one column, but I would prefer the process to be random or formulated in such a way that the Column to which the extra pennies are added to or taken away from differs in order to be "fair to each column".
Man, I hope I explained that properly.
Thanks for ANY help with this.
Hello All,
Suppose If I enter any numeric value in any cell, how can I distribute the values equally in the other cells.
For example, in cell A1, if I enter 150, then the values should be equally distributed among three cells B1,C1 and D1 as 50 each. If I enter 90, then it should be 30 each.
Your help is much appreciated.
Thanks
Gopala Krishna Rao
Hello all,
I've tried searching the forums and some have touched on this but not quite. I am trying to show the amount of hours that will be needed over the next few weeks. As you can see in the sheet "Breakdown", I have a spreadsheet with four rows of total hours (B18:B21). Above that, going across (C11:Q11) are the amount of man hours available. These are linked to the cells above that for the number of people and hours available each week. These will be adjusted to fit the number of hours required (i.e. what if we added a sixth person).
So down below (C18:Q21), I want to see the hours divided up evenly between the four rows and when one row is completed, the rest of the hours will be divided up evenly among what's left. As the amount of work hours are adjusted, the numbers will adjust automatically. This would mean once a row's hours have been completed, the remainder cells in that row would be left blank.
Please let me know if you need more info. Thanks!
Hi all
I'm wanting to do the followiing
I have ten cells A1 to A10, I want to atuosum the cells to A11 (easy enough)
Problem I have is In A12 I want to divide A11 by the amount of cells that actually have a value in
them,
Ie A1 might be the only cell with a value so A12 would divide A11 by 1, I then
might add a value to A8 also so I would want A12 to divide A11 by 2 and so on
Hope this makes sense
Thanks in Advance
AlanB
In my worksheet I have numbers in cells C7  L35. Is there a way to equally
increase all the values (which are different) without rekeying each one by
hand? I don't know if I should be looking for a formula some sort of
formatting.
Help, please!!
I have a problem that I've had for a while, so far I've always worked around it because I have not been able to solve it in a satisfactory way.
Say I want to do a search for the amount of people of a certain age in a column, but I want to be able to vary the amount of cells I look in. So first I might want to look for people aged 15 in A3:A35 and then in A3:A55 to see if there is a difference.
Now the optimal way to do this, in my opinion, would be to have a reference that looks like A3:A(B1) and then have the number of the last cell I want to look in in B1, in this case either 35 or 55.
Is this at all possible or do you have to change the formula each time you want to look something up?
Or is there another way to do the same thing?
Summing the difference of multiple cells based on the value of a cell. Does that even make sense.
So this is what I'm trying to do. I have dollar amounts in b5 through b256. One dollar amount in each cell. I want to designate a cell in say C260 where I can enter in a random dollar amount and have that dollar amount subtracted from each of the dollar amounts in column b and then total the differences.
I know I can accomplish this by doing something like this =b5c260+b6c260+...etc but I'd have to do that 250 some times and that's just silly. Is there a different way to do it.
The goal is to be able to identify a pay rate in c260 which compares itself to the pay rates in column B and then shows how much money we're losing. Ideally I would like to be able to write a formula that only subtracts c260 from numbers that are higher than then number i place in 260 and then add them up. Hmmmmmm? Does that make sense?
Hello,
This is my first post here after "lurking" for some time. I have searched the archive and done Google searches but can't figure out an answer to my question.
The snippet below seems to work fine, but it's ugly and I would simply like to know how to streamline it. What is the correct syntax to tell VBA to take some action if a cell value equals any of several values? There must be a more efficient way than retyping "Cells(1,b) = " each time.
Thanks in advance for the help!
Andrew
For b = 50 To 10 Step 1
If Cells(1, b) = "GST TAX AMOUNT" Or Cells(1, b) = "QST TAX AMOUNT" Or Cells(1, b) = "HST TAX AMOUNT" Or _
Cells(1, b) = "RPT FULLY EXTRACTED" Or Cells(1, b) = "RPE EXP" Or Cells(1, b) = "E DAT" Or _
Cells(1, b) = "TE" Or Cells(1, b) = "DATE" Or Cells(1, b) = "" Then
Columns(b).Delete
End If
Next b
Hi,
I hope I can explain it well. I can figure out why is not working. Any hints on what I am doing wrong will be appreciate it. I have an excel file like this with Sheet 1 with 66 rows
Customer ID
Amount purchased
Amount paid
Amount
1302
$2,049
$2,466
$417
2245
$1,494
$598
$896
1416
$3,165
$3,165
$0
1512
$2,277
$228
$2,049
1224
$3,268
$1,634
$1,634
I created another Sheet named "Amount", should list two columns Costumer ID and Amount Owed but only those that owed more than $1,000
I don't know why my For each loop is not working, is copying the last amount owed over $1,000 only but not the rest of the amounts or the Customers Id.
Thank you very much for any help on what I am doing wrong.
Here is my code
Code:
Sub Amounts()
Dim cell As Range
Dim Amount As Worksheet
Dim c As Range
Dim CustomerId As Range
Range("d3").Value = "Amount"
Range("d4").Formula = "=b4c4"
Range("d4").Copy Destination:=Range("d5:d65")
Sheets.Add
ActiveSheet.Name = "Amount"
ActiveSheet.Cells.ClearContents
Range("a3").Value = "Customer ID"
Range("B3").Value = "Amount Owed"
For Each c In Worksheets("Sheet1").Range("d4:D66").Cells
If (c.Value) > 1000 Then
Range("b4").Value = c
'Range("A4").Value = CustomerId
End If
Next c
End Sub
******** ******************** ************************************************************************>
Microsoft Excel  Book2
___Running: xl2002 XP : OS = Windows XP
(F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)bout
A1
=
A
B
C
D
1
$ 1,081
$1,087
$1,079
Sheet1
[HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name box
PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.
Is there a formula that i can insert to will give show me the smallest amount in cell D1 for example from these three amounts I want to show the amount in C1 in cell D1 because C1 is the smallest amount out of the three.
Thanks in advance
Hello folks,
I am trying to minimize the amount I will be rounding off of numbers in an array. I have numbers listed in column K, the same numbers rounded off in column L, and the difference between column K and L, stated absolutely, in column M (see attachment). The numbers in column K were derived from calculations elsewhere in the sheet. By changing the number in F3, the numbers in K through M will vary. My goal is to minimize the amount I have rounded off from the values in the K column. That is, I would like to create a low value in the M cells. Specifically, I would like each cell in M to be less than 0.1.
Is there a way to solve for F3, such that each cell in column M is less than 0.1? F3 is the only cell I am free to change. Lastly, F3 must be 3 or greater.
Any help would be most appreciated. I look forward to learning more from you all.
Steven
Hi there,
I have a budgeting spreadsheet im developing and in it is a version of the tax tables.
on the left the rows increase in multiples of 2
eg
2
4
6
8
etc...
This is the Gross Pay table.
On the right is the PAYE (tax to be charged if your pay is the amount on the left)
and this goes up by some formulae set by the tax department.
Im looking for a formulae which can take your exact gross pay (example $3) and work out what the exact tax would be.
Gross Pay , Paye
2 , 0.28
4 , 0.58
6 , 0.86
in the example the correct tax amount would be 0.58 minus 0.28 (because $3 is between 2 and 4) multiplied by 200 (the amount of difference in cents between 2 and 4 dollars) then multiplied by 100 (the difference in cents between 2 and 3) then adding the original .28
Giving an answer of 0.43
I need this to work for all amounts even cents  for example $2.8
The correct tax amount would be 0.58 minus 0.28 Divided by 200 (the amount of difference in cents between 2 and 4 dollars) then multiplied by 80 (the difference in cents between 2 and 2.8) then adding the original .28
Giving an answer of 0.4
VB isnt an option as I am working in Numbers for mac (but if i can find a formulae on here I can get the gist of it and if it doesnt work on numbers atleast i have an idea on how to work it out.
Kindest regards,
Tim,
ps any questions please feel free to ask!
Hi everyone have another problem which is bugging the life out of me . I have 39 cells (A1A39) i also have a list of names in (B1Bx) what i need to do is get excel to count the names  that part is easy enough and depending on the amount of names fill all 39 cells of A with those names equally ie if there is 13 names in the list each will get 3 cells with the name in but if there is 10 names i need it to show 2 names in one cell as 1 name would have 6 cells and the rest 5 and a half each . Anyone got any ideas?
Thanks
Mark

