Divide Cell Amounts Between Cells Equally 


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
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 Tutorials
Error Values in Excel  Full Explanation
Here, I'll teach you what the errors in Excel mean. There are many errors that you can get and each one means som ...
Here, I'll teach you what the errors in Excel mean. There are many errors that you can get and each one means som ...
Link to Cells on Other Worksheets in Excel
It is very easy to link one cell to another cell in Microsoft Excel. The steps needed are listed below as well as p ...
It is very easy to link one cell to another cell in Microsoft Excel. The steps needed are listed below as well as p ...
PV Function  Get the Present Value in Excel
The Present Value (PV) function in Excel will return the current value of an investment. This calculates the curr ...
The Present Value (PV) function in Excel will return the current value of an investment. This calculates the curr ...
Quickly Copy the Last Action to Multiple Cells in Excel
In the previous tutorial I talked about the Redo button in Excel and how using Ctrl + Y can be used to do the same ...
In the previous tutorial I talked about the Redo button in Excel and how using Ctrl + Y can be used to do the same ...
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
 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
 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
 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
 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
 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 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??!
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 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 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
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
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
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
Consider i have an amount e.g. 100,000 in A1. Now this amount can be divided equally on 24 months or 36 months i.e. 100,000/24 or 100,000/36.
I want to create formula that if i select 2 years in validation column( "A2" ) it Automatically divide the A1 value by relevant no of months and map the result monthly from January to December in appropriate columns. I have attached a sample file of what i want.
Please have a look.
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:
For addition of money:
Code:
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!!!!!!
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!!!!!!
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:
$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.
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.
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!
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???
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:
$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.
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.
Is there a way to have a cell return a minimum time value of if the difference between two cells is less than X? I know that if I put the formula "=If(B1A1<=2,2,B1A1)" it works, but when those cells are formatted in time, I can't get it to work. For instance: Someone is guaranteed a minimum of 2 hours of pay regardless of the amount of time he or she actually worked, so if he or she puts in that they came in at 8:00 and left anytime before 10:00, they'd get 2:00, and if they left anytime after 10:00 they'd get the actual amount they worked. Any ideas? Thanks.
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!
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)
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!!
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?
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?
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
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
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:
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
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
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 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
Thanks
Mark
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!
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!