Email:      Pass:    Pass?
Close Window   
Free Ebook
Get Your Free Excel
ebook!
Our Top 15 Excel Tutorials
Instant Access!
E-mail:
Subscribe for Free Excel tips & more!
E-mail:
Advertisements



Divide Cell Amounts Between Cells Equally

Forum Register
Search Excel Forum Posts, Tutorials, Macros, Tips, and More

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

View Answers     

Similar Excel Tutorials

MOD Function in Excel
The MOD function is very simple but it can be used to do wonderful things in Excel. It returns the remainder after ...
PMT Function - Get the Payment Due for a Loan in Excel
How to calculate the payment amount for a loan or similar financial instrument that has a series of constant paymen ...
Group Data Together for Increased Readability in Excel
How to group data together or collapse it in order to focus only on the important data in Excel. This allows you t ...
NPER Function - Calculate Number of Periods Needed for a Set of Payments in Excel
How to calculate the number of periods required for an investment in order to get the desired return.  The number o ...

Helpful Excel Macros

Convert Numeric Dollar Values into Text in Excel - UDF
- Convert a numeric dollar amount into its text equivalent. This UDF (user defined function) for Excel will change or tra
Close a Workbook after a Time Limit is Reached
- This macro will automatically close the workbook in which it is located after that workbook has been opened for a certai
Delete Only the Text from Cells
- This macro will delete all text from selected cells. This will not delete any other characters or numbers from any cell
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
Automatically Run a Macro so Many Seconds, Minutes, or Hours After an Excel Workbook has been Opened
- Run a macro after a certain amount of time has passed since the Excel workbook was opened. This means that you can set

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 ------B------C-------D
1 13.78-- 14.52--15.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? =d1-a1 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 auto-populated with the amount of C2 divided by C5.

Hope that makes sense.


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


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.


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,

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!




As per the attached.
Column b are names:
Column d are the total amount of listings the person has
Column f is the calculated total amount of listings that person can advertise.

At the top in Cell G2 is the total amount of allocations allowed which is to be divided equally over all the persons listings The more listings the more allocations. However, the formula I currently have doesn't equal the exact amount in Cell G2. It needs to. This is where my problem is.

Currently my formula in column d is: =IF(ROUND($D6/$D$24*$G$2,0)=0,1,ROUND($D6/$D$24*$G$2,0))

This is rounding close but it needs to be exactly what is in cell G2. (remembering that all these numbers can change - including G2)

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,

I've got a fairly difficult exercise that I cannot seem to find a solution for.

I need to divide one budget in this example pro rate (in reality there are more than 24 different budgets).

There is a limited amount that one person can receive (=maximum 5% of the budget) and the 'lost amount' for these persons is divided pro rate
over the other people in the budget (based on the height of there earnings - (column 'amount' in the example).

The full budget has to be used.

Can someone give me a macro or formula so that I'm able to do this for several budgets?

Your help is very welcome.

Regards,

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




I have a row A through N. In cell A1, have a dollar amount. In cells C1 through N1, I have different dollar amounts. In cell O1, I want to have a formula that will take the dollar figure in A1 and subtract from that amount the total of all the amounts in cells C1 thorugh N1. But I don't know how to write that formula.

Let's say I've got $100 in A1, and then in C1 through N1, I have $2, $3, $4, etc. for a total amount of $94 in cells C1 through N1. I'd like to have a formula in O1 that would show $6.

I hope this is clear. I'd appreciate any help about how to write up that formula in cell O1.

Jason

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 = "=b4-c4"
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


Dear forum,

In a macro that automatically creates new invoices, I want to give names to cells that contain the following information: debtor, amount excluding VAT, amount including VAT and VAT rate.
Another macro puts these numbers in a separate balance sheet. Therefore, I need all the cells that contain those numbers to have the same name for all the invoices (worksheets). I've noticed, though, that if an existing name is used for a cell in a new worksheet, it stops being valid for the old one.
Is there a way around this?

Jilt


Hello,

Here is what I'm trying to do:

I have dollar amounts listed in cells E7-E10 which are all total together in cell E11.

Cells F7-F10 lists the invoice numbers used to bill for the amounts in the adjacent E cell.

If there is not an invoice number listed in the F cells, then I do not want the amount in the corresponding E cell to be counted in the total sum in E11. When the invoice number is placed in, lets say F7, then I want the dollar amount in E7 (dollar amount was in E7 before the invoice was even made) to some how be activated and count in the total sum in the E11 cell.

How can I make this happen? Little help?


I'm trying to sum up the "subtotals" for a set of numbers that occur at the same intervals but I don't want to say Sum(x1 + x2 +x3...). I thought their was a way to select one cell and do some kind of control alt something to have excel highlight all the cells that were equally spaced apart from one another. It's a huge spreadsheet and I'd rather not get carpel tunnel. Thanks!


Is there a way to find out if certain cells within a range sum up to a specific number? I often find myself looking for a certain amount - like I've got an error for a specific dollar amount - but I have a range of entries, and any combination of those numbers could sum up to the total of the error. I've tried selecting various cells with the hopes that I can randomly pick the right ones, but that's just hit or miss, and depending on how many cells (amounts) are involved, it could be impossible. Is there a formula or add-in that can check a range of numbers to see if any combination will sum up to a specific amount? (I'm using Excel 2000) Thanks!


How could I convert this 'IF' into a VLOOKUP function?

=IF(H20 is inbetween 12-14),1,IF(H20 is inbetween 15-17),2,IF(H20 is inbetween 18-19),3,IF(H20 is inbetween 20-21),4,IF(H20 is inbetween 22-23),5,IF(H20 is inbetween 24-25),6,IF(H20 is inbetween 26-27),7,IF(H20 is inbetween 28-30),8,IF(H20 is inbetween 31-32),9,IF(H20>33),10,"")

I know "is in between" is not part of any excel function, just wanted to spell it out for clarity of what it is I want to do.

Thanks in advance.


Hi
I have sereral cells with the same amount of cells in between. These cells contain the sum of the 12 cells above. I would like to sum these as well with a butt*******.
I need a VBA code to sum each 16.th cell and post the result.
E.g. Sum(D16+D33+D50+D67+D84....)

But I dont want to use a so spesific Cell referance. I would like a code where i Specify the interval between each cell I want to add to the count and when to stop adding, such that if I with another button only want to add the 3 first of the sequence I can do that and with another button sum 15 cells (each with the same amount of cells in between them)

I don't need help with the posting to a textbox though, just the loop or recommended action.

I might need help presenting this in a chart with use of VBA though - which I would like as well.


I want to learn how to draw a shape (an arrow) inbetween two specified cells. I recorded a macro while doing this, and found the addconnector method:

http://msdn.microsoft.com/en-us/libr...ffice.10).aspx

But this uses some kind of absolute vertical/horizontal screen positioning? How can I find this for each of the cells that I want to reference?


I am trying to set up a spreadsheet to calculate tips for wait staff. There
is no cash involved and all tips are paid through the server's paycheck.I
would like the spreadsheet to contain all the names of the staff and be able
to designate which ones worked that particular shift for the tip share. For
example, there are 10 waitresses who work at the establishment but only 5
work on any given day. I am given a total amount of tips for the day which is
divided equally among those who worked that shift. Right now the work is done
manually and is sometimes time consuming. Thanks, Kay





I need a formula that will look at two cells and determine if there is specific number in between the two cells(numbers)

I have attached an image give you an idea,

service forecast.jpg

Hi,

I have dollar amounts in cells A1:H100. I would like to increase those dollar amounts by 5.5%. Adding an "=" before the amount & a "*1.055" after the amount in each cell would achieve the desired result. However, I was looking for a quicker way to do that or to achieve the result. I'm using Excel 2007 on Windows XP SP3. Any help is greatly appreciated. Thanks in advance.