Dividing Dollar Amounts Unevenly Into Varying Numbers Of Cells/columns

Dec 10, 2009

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.


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".

View 10 Replies


ADVERTISEMENT

Extract Dollar Amounts From Cell

Sep 4, 2007

would save me a tremendous amount of time.

I have over a thousands cells that contains text such as:

For 20,000 lb loads add $1.50, replaces AC-4574;price increased $2.00/CWT rod increase

The order of the text in each cell is different.

Here is what I would like to do:

1. Extract only the dolloar amounts (here $1.50 and $2.00) and copy it to the row to the right.

Is there a way I can do it automatically without me going through each cell and extract it manually?

View 9 Replies View Related

Gather Dollar Amounts Entered On Different Dates For A Monthly Total

Jan 25, 2014

I need to gather total dollar amounts from a column based on their date/month entered.

I have a sample attached, in cell L2 for example I would need to gather all the dollar amounts in Column G that were entered for that month in column B that correspond to the month in column K.

View 2 Replies View Related

Multiplying Hours By Different Dollar Amounts Based Upon Time Worked?

Feb 16, 2012

I charge one rate for day work and one for night shifts. My spreadsheet is set to figure the total number of hours worked and I know how to multiply by dollars to get answer #1, but is it possible to use a formula to multiply times a different rate for a night shift?

For example I use =IF(B2

View 9 Replies View Related

Creating Macro That Can Grab Varying Amounts Of Data And Put Them In New Worksheets

May 1, 2013

I have one worksheet with four columns of data. Column A is a well name, RA-0001, column B is the measured depth of the well from 0 feet to however far down it goes, anywhere from 4000 to 15000 feet, column C is the inclination of the well, column D is the Azimuth.

I have 500 wells from RA-0001 to RA_0500 or so all in this one worksheet, all the wells have varying Measured Depths associated to their well name. I need to create a macro that can separate the wells and either put them in a new worksheet for each well, ie. a worksheet named RA-0001, RA-0002, ..... ect. OR, and this would be nicer, a macro that can actually save all these individual wells as (Formatted Text (Space Delimited)) files with the associated well name.

Here is an example of what it looks like. The columns do not have a subject line to state what information is in each column because I dont need it in that format.

RA-0001
0
0.00
0.00

RA-0001
100
0.91
5.56

[Code] .......

Even just knowing how to create a simple macro that would take all the data from each well so I could manually copy and save them as new files.

View 7 Replies View Related

Dividing A Series Of Numbers In Specific Selection Of Cells

Dec 19, 2006

I have a txt file imported into excel that's slightly strange.

But once into excel, I need to edit some numbers.

The values in cell B 23 is 1.

So is the value in B 28, so is the value in B 33, B38, B 43 and so forth ad infinitum... Or so it feels.

I need to edit all those values to 0.

View 9 Replies View Related

Concatenate Varying Numbers Of Cells Based On Duplicates Found In Separate Column

Jul 25, 2011

I need to concatenate varying numbers of cells based on duplicates found in a separate column, but I'm not sure how to approach it. I have 41,000+ rows of data, so I have to find a formula.

Example:

1AB2Denton, PaulFB357D4D3OwensTest, MarcyFB539F934Brennan,
JosephFB539F935Bowser, AmyFB539F936LaRock, ChuckFB667D3B

Based on duplicates in column B, I want to combine the data in column A into one cell. The duplicates in column B could be only 2, or could be 20+.

View 2 Replies View Related

Dividing Numbers Evenly

Apr 6, 2009

I have a total at the bottom of my spreadsheet and I want to distribute that total equaly to the missing columns for each month and each product highlighted in green in the spreadsheet. What formula do I need to use to be able to do that?

At the end I want the numbers in each month to total the total at the bottom.

View 10 Replies View Related

Dividing Time By Numbers

Sep 6, 2006

I've found how to present time exceeding a minute in seconds ([m]:ss.00), but I need to formulate items per second. Doing simply CellItem / CellTime won't work. I've tried Minute(CellTime)*60+Second(CellTime), but this leaves out the hundreds of a second.

View 3 Replies View Related

Dividing Letters And Numbers String

Jan 25, 2009

A given string of some letters (from three to five, six) is followed by a single number (1-4). Then it ends, or is followed by another such combination, up to a maximum of, let's say, four.

Example:
xxxx1
yyy3yyyy1
xyzxx1yxz4xzx1
xxx2xxx2xxx2xxx2

What I would like to do is to divide a given chain of characters into the smallest chains which contain only letters and one number. To the examples given above, I'd like to receive the following sets:

xxxx1
yyy3 and yyyy1
xyzxx1 and yxz4 and xzx1
... and so on

Is it somehow possible to do it with simple functions? Or is VB necessary (which sadly I don't know)?

View 6 Replies View Related

Dividing To Get A Result Of Whole Numbers Preset

Dec 11, 2009

Does anybody know the code or the formula to divide a number generated by a formula. Id like to divide this number by 2 different set numbers. Meaning if my number comes out ot 19, id like to divide this by 5.5 and if it's not a whole number which this isn't (it's 3.45), i'd like to round this number to a whole number and add 2.5. My end answer would be 3 5.5 and 1 2.5.

Windows XP
Excel 2003

View 9 Replies View Related

Dividing Time By Whole Numbers (for Productivity Per Hour)

Mar 21, 2013

I'm trying to work out the productivity of something per hour but I'm having a bit of trouble with dividing time in Excel.

Basically I've got

A1 = # of Sales (e.g. 5)
A2 = Total time worked (e.g 07:36:00)

I want C1 to show me how many Sales are made per hour.

View 1 Replies View Related

Format Numbers Into Amounts

Nov 13, 2008

For example: I have a spreadsheet with a column that has numbers such as 48600. When I go to format it into currency, etc. I select the decimal to move 2 places. Although it keeps giving me $48,600.00. I dont want that. I want it to put $486.00.

When I try to increase or decrease a decimal, it wont do anything until I have a decimal already in the number. So then I took 48600.00 and tried to increase/decrease the decimal, but it gives me either 48600.00000 or 48600. What I need is to make my number 48600 $486.00

View 2 Replies View Related

Excel 2010 :: Dividing Numbers By Number Of Cell They Are Found In?

Feb 13, 2012

I am using excel 2007 or 2010.

I need to divide the numbers on sheet 1 column D. This column contains numbers and blank cells so I need to divide by the actual number of cells that contain only numbers. I will be referencing the divided numbers on sheet 2 in a monthly summary format.

View 3 Replies View Related

Sorting Numbers Of Varying Lengths

Aug 15, 2012

I am trying to do a VLOOKUP using a list based on our Part numbers in Excel 2007. When I do a normal sort it groups the numbers 1 thru 9 by the number of digits, giving me multiple list of 1 thru 9. When I do my lookup it will not find the shorter numbers. Is there some way to sort these Part numbers in order regardless of the number of digits?

View 2 Replies View Related

Macro To Subtotal Varying Lists Of Numbers

Apr 17, 2014

I am fairly new to excel and I am having trouble trying to get a sub total function to work for me. I need the subtotal to for a list of about 20 columns in one line with a varying amount of rows. So the subtotal must stop when there is a blank at the top. I can get the macro to find the totals A on the spreadsheet and then offset to beneath the columns of numbers but the sub total formulas takes in every number. Here is the example:

456.00
8,798.00

45,465.00
789,789.00

45,465.00
789,789.00

45,646.00
7,897.00

Totals A

[Code] ........

View 9 Replies View Related

Macro, Autofill On Varying Numbers Of Rows

Jan 13, 2003

I am putting together a macro to import a CSV file onto excel. I will be manipulating columns of data (concatinating some, dividing by 100 etc.)

What I am having trouble with is that each file imported will have different number of rows.
I tried, in recording a macro, to use autofill (entered formula and double-click fill handle to drop the fourmula into each cell) hoping this would show me how to do it but alas, it just coded the range as the starting and stopping cells (didn't show a count formula or some such.

View 9 Replies View Related

Formula Columns Where My Workers Record The Amounts Of Work They Do During The Day

Oct 22, 2008

I have three columns where my workers record the amounts of work they do during the day. The columns are labelled as follows:

Column A – “Correspondences’’
Column B – “Linking’’
Column C – “Allocating”

When a task has been completed an “X’’ is put in the relevant column (which is then summed at the bottom). I use these sums to calculate each workers productivity in the following way – number of “x’’ divided by number of days worked. This is a simple formula, however, each “x” now equates to time – which is causing me problems. An “x’’ is Column A equals 1 hour, Column B 1 hour and Column C 2 hours.

I am looking for someone to help me create a formula which will calculate all possibilities in the above situation above. Please bear in mind that a worker can do one of these tasks during the course of a week, two or all three .

View 9 Replies View Related

SUMIF- Column Of Contract Numbers Of Varying Length

Mar 27, 2009

I have a column of contract numbers of varying length. I want to run a sumif of all of the values on my reference sheet that have the contract number beginning with the contract number in my listing. All of the the contract values in my reference sheet are very long. Since the numbers I am working with vary in length, i don't know how to match this string in the sumif function

I want something like this

=SUMIF('Reference Sheet!$A$5:$A$13410,LEFT("", LEN('Select Contracts'!$C6))='Select Contracts'!$C6,'FINALIZED DATA'!$H$5:$H$13410)

Is there a way to refer to the range in the sumif function,

View 9 Replies View Related

Adding 1 Dollar Amount To All Cells

Jan 6, 2010

I have the new USPS rates in an excel sheet. I need to add my handling fee to all of the new rates. Is there a way to quickly add this same .70 to all cells?

View 2 Replies View Related

Varying Validation Drop-downs Based On Varying Cell Values

Aug 7, 2013

I am attempting to have cells in Column 'U' deliver different drop-down menus based on the corresponding value in column 'D'. I have created 7 named lists:

List_117G
List_152
List_JMET
List_XBAND
List_PACWIND
List_VORTEX
List_ROVER

Those lists will be called up based on 7 values in column β€œD”:
β€œG”
β€œ152”
β€œJ”
β€œX”
β€œD/E”
β€œV”
β€œR”

So far I have only been able to get this to work for the first category β€œG”. When I change the value of column β€œD” from β€œG” to β€œ152” I no longer get a drop-down. Here is the formula I am using in the List function of validation.

=IF(D6="G",List_117G,IF(D6="152",List_152,IF(D6="J",List_JMET,IF(D6="X",List_XBAND,
IF(D6="D/E",List_PACWIND,IF(D6="V",List_VORTEX,IF(D6="R",List_ROVER,)))))))

View 2 Replies View Related

Combine Rows Of Varying Length And Varying Number Of Occurrences

Jun 28, 2012

There are 11,000 rows and 4,000 are unique. The goal is to merge the data down to the 4,000 records. Each of the unique entries shows up 1 to 15 times.

In trying to solve this, I wonder if I should break this down into the different # of occurances and implement specific solutions. e.g., There are

5700 entries that show up 2x
504 that show up 3x
24 that show up 12x

View 3 Replies View Related

Extract Two Separate Numbers Of Varying Length From Text Field

May 24, 2014

a formula to extract the numbers into two separate fields. The text may vary in length and the numbers vary in length also from 1 digit to 2,000,000.

Data in A1 is as follows:-

Meter reading Old:1345 New:67890

View 9 Replies View Related

VBA - Call Macros With Varying Names And On Varying Modules

Oct 2, 2013

I want to call a macro with a varying name that is within a module with the same name.

I have a module called Test1 and within this, a macro name called test1
I have a module called Test2 and within this, a macro name called test2
etc

On another module called Test8 (with the macro called Test8), this Test8 macro will call either Test1 or Test2 or Test3 etc depending on what I choose in an excel spreadsheet. So on sheet1, cell A1, there is a drop down with the options Test1 or Test2 or Test3 etc.

The following works to run the macro test1 from module test1 (when it does not vary i.e. i physically put in the name of the macro myself):

Sub Test3()
Test1.Test1
End Sub

The following works to run the macro test2 from module test2 (when it does not vary i.e. i physically put in the name of the macro myself):

Sub Test3()
Test2.Test2
End Sub
etc

However, if I try it so that the calling of the macro varies as below , it does not work:

Sub Test3()
MacroToCall = Sheets("Sheet1").Range("A1").Value
MacroToCall.MacroToCall
End Sub

View 7 Replies View Related

Split One Large Dollar Amount To Multiple Cells?

Apr 11, 2014

I have a group trip coming up where we took in a $1000 deposit per person. The total amount is only in the main members cell. There will be two more payment amounts coming. I need to make certain each person has paid the total amount they are required for the trip. There are two possibilities for total trip amount. Double occupancy is $15,500.00 and Triple is $14,500 per person.

There is a unique Identifier for each registrant, except when there is a multiple person under one registration the identifier has all the same number except at the end. I figured out a way to strip the number down to just show the same number in one registered group. There could be up to 10 people under one registration. I might not have the best option for breaking down the Identifier number, but thought it would be a good start. Used the code Left(column, number).

Issue is this. How can I now take the total number of people under one group take the total deposit and split it between just that unique group so I can then determine what is still owed after payments are made.

View 1 Replies View Related

Copy 3 Columns And Paste With Varying Row Offsets

Mar 31, 2009

I am trying to copy three columns of information from sheet1 into sheet2.
When pasted I need the information to be spaced or offset as per below sample.

Sheet1
HTML ABCDEF
1#AnimalDescription
21Dogfour legged fur ball
32Catother four legged
43Horsegsdfgdfgdbfb
54Snakedfgegergeg
65Ratlkjlmlbnm,
76Birdtshgjmgjkk

Sheet2
HTML ABCDEF
1#AnimalDescription
21
3Dog
4four legged fur ball
52
6Cat
7other four legged ...................

View 6 Replies View Related

Copying Contents Of Columns Of Varying Lengths

Feb 7, 2007

I export data from another programme into excel and I wish to write a macro to copy the contents of columns G, H, I and J and paste it into columns A, B, C, D but starting at row 4. I then carry out various formatting tasks. I have written a macro that works for the copy and paste and the formatting but it only works when the data is the same length. The length of the data exported varies each time and so I am looking for some code that stops at a blank cell and copies the range above. I believe this uses xlup or xldown.

View 8 Replies View Related

Macro For Summing Columns Of Varying Lengths

Mar 16, 2007

I have a worksheet (downloaded from Paypal) and the columns I need to sum are of varying length (sometimes 12 rows and maybe 200). I am having trouble finding a way to find the end plus one of the column to write the formula in.

View 9 Replies View Related

Dividing Range Of Cells With Given Cell Having Formula?

Sep 3, 2012

I am working an Excell sheet. It have many cells with formula like sum of a range of cells etc.

I wanted to divide a range of cells with the given cell (which is inturn having a formula for sum of a range of cells.) want i wanted is to divide a range of cell values with a given cell whose value (number) is obtained through a formula.

when i do

=100*(v65)/v20 for T65 cell and copy the formula for T66-T106 cell range

it is calculating for T66=100*(v66)/v21 and T67=100*(v67)/v22 ........T106=100*(v106)/v61

what I want is all the cells T66=100*(v66)/v20 ... T106=100*(v106)/v20.

View 1 Replies View Related

Dividing Number And Setting Maximum In Cells

Nov 2, 2013

I am dividing the number. I have for example 20 hours(A1) which have to be divided into 8h + the rest.

So, 8(B2)+8(B3)+4(B4) hours. I have tried few things, but I can only divide it by 8 and it doesn't go anywhere from that.

View 5 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved