Subscribe for Free Excel tips & more!
E-mail:

Divide Cell Amounts Between Cells Equally

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 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 ...
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 ...
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 ...
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 ...
Export an Excel File to a CSV File
How to export an Excel file to a CSV file (comma separated values file).  This allows you to turn any Excel spread ...

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

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'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!

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 doing accounting for a landscaping company and I am trying to evenly distribute the amount of travel time between properties per crew, per day. But I do not know how to divide by the number of cells (because there is a different amount of properties each day). Can anyone help me here?

On my calculator, if I divide 462,534.05 by 335 I get 1,380.70. Then if I times 1,380.70 by 335 I get 462,534.50. Which is what I need it to be because I need to calculate the difference of a figure not dividing equally.

On excel, if I divide 462,534.05 by 335 I get 1,380.70. Then if I times 1,380.70 by 335 I get the SAME result of 462,534.05.

I have to account for the difference of not dividing equally so there is some format or accounting function that's preset that I don't want but I have no idea how to fix it or format my excel sheet to calculate as my calculator would.

Please can someone help? Thanks!

How do I Centre Align an Overlaying Picture on a Cell?

I need to centre align a Picture in a group of Merged Cells however I am just doing it manually, is there a way of using any functionality to maintain the CENTRE ALIGNMENT on Deleting from any cells the group of Merged Cells or on Changing the Width of the Group of Merged Cells the picture still stays in the centre of the Merged Cells..

The problem I am facing is when I go in the Print Preview Mode and come back the Image gets Tiny and is moved to some other area..

I am also thinking of using it with the Camera Icon Dynamic Picture..so as to keep it fixed however even that does not work...

So please suggest any options I need to select or even VBA stuff if necessary..with an Example sheet (if possible) ..

Warm regards
e4excel

I wrote the following code to look at each cell in a range and give a message box if the maximum amount is exceeded. The code is looking at each cell in the range, but I get the message box no matter what the amount is. (I get the message for any amount, including zero.) What I need is to change this code so that I only get the message box if the amounts in the range exceed 99,999,999.99. Can anyone tell me where my code needs to be changed?

For Each Cell In MyCell.CurrentRegion.Cells

If Cell.Value > 99999999.99 Then
Msg1 = "Fund exceeds maximum allowed."
Style = vbOKOnly
MsgResponse = MsgBox(Msg1, Style, Title)
If MsgResponse = vbOK Then
Exit Sub
End If
End If
Exit For
Next Cell

Hey all,

This will probably seem a simple and easy problem for many of you, but im not very experienced with excel (hence why I'm here )
I'm trying to create a roster/timesheet excel doc for my own record keeping regarding what hours im rostered on and work, the pay im entitled to etc. (I've been having a problem lately with my employer not paying me the correct rates (they vary depending on the day) or the correct amount for the hours ive worked.)

I work a rotating roster including night shift. What I would like to be able to do is write down that on friday i am rostered on at say 21:00 and finish the next day at 08:00 (Saturday is charged at a higher rate than friday, so the 8 hours worked on sat would have to be calculated seperately than friday.) I may also start another shift on saturday night and go on until sunday morning.
the way my company sets out their payslips is quite stupid imo, but its easier to understand it if i could format my own records in this way.

what i am having trouble with is figuring out a way to do this so i dont have make a set of cells for each hour (broken in to two, in case i start/finish at half past) without putting a 1 in each second cell that im rostered on and then using the sum formula to calculate the amount of 1's that i've put down -_-
Is there a way of making it so i can just write 21:00 - 08:00 and it fills it in (perhaps with a colour rather than a character) and then calculates the amount of coloured cells (divided in half)?

Here's a link to the rough sheet that i've put together so far so as to perhaps help you understand what i mean.

http://www.savefile.com/files/2162356

thanks,
joel

p.s. far out i wish i had found this site and forum sooner. ive had so many excel questions in the past and just tossed the project aside when i couldn't figure out what to do next.

Hi All,

I would like to be able to check senarios of the problem.
There is a Capital amount invested with an interest rate of 'x' annually, with a fixed monthly withdrawal - how long would this investment last? - that's the easy part.
Now, if there are periodic amounts (not fixed in amount for frequency) paid in as well as the odd monthly fixed amount left in (not withdrawn for that month), what would the new term be?

Hopefully there are a lot more clever people out there than me.
Many thanks in advance for your assistance.

Regards,
Chubby16

I have a VLOOKUP formula that works when searching some cells but not others when both cases should work.

Q: Does VLOOKUP have a certain maximum amount of characters it can search? If a cell contains more than this amount of characters will VLOOKUP not work?

This is what seems to be happening on my spreadsheet. The VLOOKUPS that refer to the cells with more than the average amount of characters seem to fail. The same happens with INDEX.

Hello gurus!

Here's my question...I have a list of numbers and I want to know how many combinations (and what they are) of adding the numbers will equal an amount.
e.g.
List of Numbers
1
2
3
4
5
6
7
8
9
10

Amount to be reached = 12

These are some of the possible combinations to reach 12 :
2+10
3+9
4+8
5+7
3+4+5
6+3+2+1

Is there a function in Excel that will do this for me? I want to know which numbers (i.e. cells) can be added to reach 12. AND, if possible, colorcode the cells added for each combination.

Good luck and thanks!!

Hello. I need to copy a column (vertical) within 1 spreadsheet that has over 1000 cells to another spreadsheet that I filtered to have the same amount of cells (I hope it does have the same amount of cells).

The column of cells in spreadsheet 1 has formulas, but I only need the value, so I do a special paste and click only value show. But when I do paste it on the new spreadsheet, it does not completely copy the column. I realized that it does not copy any values that repeat (ex: the column of cells has these values: .01, .01, .02, .01, .03, .02, .03 ; and when I copy/special paste them in a new column in another spreadsheet, it only pastes .01,.02,.03)

I would not like to do this manually, so any help would be greatly appreciated! Thank you

I created a production tracking worksheet (see attachment), that breaks down everything that we want. But we need something visually that shows how far along they are while working. On their desk they have a Standard Production Job Aid (see 2nd tab) that's laminated so they can use it multiple times. They cross off the amount of claims they have worked (stick = 30 min). It's a tall order but if anyone has any suggestions on how to make the second tab reflective of the work in the first tab, it would make a lot of people happy. The first tab is broken up into formulas (shaded part they do not touch) and entry cells (clear cells they enter their production count and hours worked). I have been messing around with a chart but not sure if that's the best way. If it could some how shad or change the color of the stick part to the amount of claims worked that would be ideal. Any suggestions will be helpful.
Thanks again,
Seahawk56
P.S. I just found out you can not attach a workbook and I can't figure out the htmlMaker form Colo. If you are interested in trying to answer this, please reply with an e-mail address and I will e-mail it to you. Or if someone knows a better way let me know. Thanks again!

I need to caclulate an amount due to my client where there is an increase in
the amount owed each month (amounts vary) and an interest rate that is tied
to the prime rate. No payment to be made until the end of the term.

Intergers entered into the worksheet (even in previous worksheets set up
prior to the problem) are divided by 100. Example: 1 becomes 0.01, While 1.0
remains as entered. I don't believe it's in the cell formatting as the number
shown in the formula bar is not 1 but 0.01, while 1.0 is shown as such.

Hi All,

I wanted to make a spaghetti chart (to show amount of transportation waste in a process), but found that the manual way is too tedious...this led to the thought of automation...and since I dont know anything about VB...here I am.

Situation: Column A and Column B have numbers as following
1 5
2 3
3 4
2 5 ... and so on.

These numbers are actually process steps.
In another corner of the sheet, the steps are mentioned as following in adjoining cells and others in couple of rows below...

1 2 3 4 5

6 7 8 9 10....assuming there are only 10 process steps.

What I want is that in the numbers entered in column A and B should result in a curve line linking the relevant cells...as in above example...a curve line links 1 and 5...
If I have confused you, pls let me know and I will try and upload an example of what I want.

Hi all,

Is there any way to convert a column of numbers that are positive to be
negative? We create a text file from our general ledger program that has all
the transactions for the past year, then import that file into Excel. We use
the text to columns to convert the text file to a usable format where the debits
are in one column and the credits in another. The problem is that all the
numbers are shown as positive so when we net various figures the amount is not
correct. Here's an example:

DR-amount CR-amount
8,239.32
23,545.26
18,028.12
200,000.00
15,490.71
652.20
16,590.00
130.00
8.00
654.75
308.00
895.09

I want all the numbers in the credit column to be negative; is there any way to
do this without manually editing every entry? Thanks!

Sue

Hi All,
having a little trouble trying to do the following.

A report is run, based on the values between A7:A31
This then populates info into cells B7:I31

However, if there is no value in one of the cells at the end, eg. A:28, A29, A30, A31 (the cells are sorted in order first, so you wouldnt get a random empty cell inbetween values) then it should just clear the contents for that entire row, so that #N/A, doesnt show.

please note i only want to clear out the cell content, not delete out an entire row.

this is something like what i need i think...

Code:

```Sub ColorEmpty()
LastRow = Range("A65536").End(xlUp).Row
For i = 1 To LastRow
If IsEmpty(Cells(i, 5)) Then
Cells(i, 5).EntireRow.Delete
End If
Next i

End Sub
```

http://www.mrexcel.com/forum/showthr...lete+empty+row

I have a macro that's trying to copy the contents of merged cells onto the end of a group of merged cells.

For example, imagine each box is a group of three cells merged together and '1' or '2' means it has a value.

Sheet 1:
[1][1][][][]
Sheet 2:
[2][2][][][]

When I run the macro, it should create this:

Sheet 1:
[1][1][2][2][]

Code:

```Windows(filename).Activate----Sheet 2
Sheets(SheetSKUs).Range(Cells(2, 1), Cells(2, lastcolumn)).Copy----In this example, last column would give me 9, so that it copies 3 groups of 3 merged cells, which is the amount of open space in the first sheet
Windows(template).Activate----Sheet 1
Sheets(SheetSKUs).Select
Range(Cells(2, skuColumn), Cells(2, 15)).PasteSpecial xlPasteValues-----skuColumn would give me 7, which is the first column of an open cell
```

In Excel 2007, it works fine, but in Excel 2003 it tells me the cells are not identical, but they are exactly identical because Sheet 2 is an exact copy of Sheet 1.

My chart has about a dozen data series in it, with a legend created for each.
A couple of the data series currently are zero amounts, and my boss prefers
not to show the legends for the zero amount data series in the chart legend.

I understand how to delete the legend name from the chart legend and leave
the data series as part of the chart. Is there some way to check each data
series when the chart (or worksheet) is activated, see if the amount is no
longer zero, and if so, reinsert the legend information for that data series
in the chart legend?

--
Bill @ UAMS

I am trying to find a formula for the following:
I am creating a dashboard view of a spreadsheet.
In column F from F22 to F93, each cell needs to show a dollar amount.
These dollar amounts begin in I163 and repeat every 54 cells until I3997
Is there a formula I can use in F22 that can be copied down to F93. Currently I am writting into each cell F22 =I163, F23=I217, F24=I271..... I have 8 other columns like this and it is very time consuming.

This sounds fairly simple to do but I can't think of how to actually do it.
I would like to have about 15 cells available to type text into (they are stretched to be about the size of half a page long and the normal height of a line. I don't want to merge the cells together) and I would like to be able to start in the first cell and when the text comes to the end of that space (not the total amount the cell can hold but the space shown as if you were writing on a piece of paper) you are moved down to the next cell down without having to press enter.

Can that be done?

I'm trying to multiply the hours I work by the amount per hour
I make. My "hours worked" cells are formatted "7:03" hours.

When I multiply by the amount I make I get a weird decimal.

In addition when I format more than 4 cells with the above hour
format I get a decimal that is not an accurate account of my hours.

Can anyone detail a simple formula for mutilplying hours by dollars.

Thanks,
Joshua SF Bay Area

I currently have a spreadsheet which copies a formula, which has been put together using the concatenate function, and 'pastes special' in another cell.
The idea is that the formula ive put together in concatenate will show up in a different cell and the link will be active showing external data.
The problem i have is that the macro will paste the formula but the formula doesnt update so all i have is a list of links, i have to go through each manually, highlight it, press enter and then the link updates with figures (which can take along time with the amount of data)
Is there any VBA which will update the cells? everything ive tried hasnt worked.

Thanks

I have a very specific situation I need help with.
I have this spread sheet containing about 300 rows of people. In some, not all columns, there is a number. Is there a way of making all those numbers increase or decrease by a random amount. Say if one column was 88 then after this it would be 74 or 93. I've tried RAND however, where columns have no data, I want it to remain blank. But putting a rand in all the cells it generates a number. And selecting all the cells I want to change would be tiresome.

Any thoughts?