# 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
ADVERTISEMENT
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
View Related
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
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
Oct 9, 2009

Here is what i am trying to achieve. If the date 2/20/2010 is located at F53 & the cell next to it at H53 is populated with a number between 1 & 16, then i want the cell at J11 (42 rows further up) to auto populate with the number 1. When this occurs the cells beneath this, from J12 to J52 should also auto populate with the with consecutive numbers from 2 to 42. Would also like to see the cells with numbers 1 to 28, automatically format to orange & the cells containing numbers 29 to 42 automatically format to yellow. I plan to have this condition repeat several times later in the year, at dates that are to be decided. When these dates are decided i want to be able to enter a number from 1 to 16 & next to the date & all of the above automatically occurs.

View 14 Replies
View Related
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
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
Jun 10, 2008

I have the following syntax in B1: "1,2,3,5,6". and I need VBA to take the very last number out of that list, and increase by four in single steps. For example: before the code: "1,2,3,5,6" and then after it "1,2,3,5,6,7,8,9." The code must be flexible, though, because it will be running within another Macro, and it must work whether the last number in the list is 1 digit ("...4,5,8"), 2 digit ("...34,35,36") or 3 digit ("...111, 113, 114"). Those three examples would be changed into "...4,5,8, 9, 10, 11" "...34,35,36, 37, 38, 39" "...111, 113, 114, 115, 116, 117".

View 4 Replies
View Related
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
Aug 21, 2009

I've got the following sumproduct formula (which seems to work):

=SUMPRODUCT(--(ISNUMBER(SEARCH($B17,Product_Keywords))),--(YEAR(Invoice_Date)=H$3),--(ROUNDUP(MONTH(Invoice_Date)/3,0)=H$4),--(Invoiced_Amount))

B17 is a keyword to be found in the Product_Keywords range

I'd like to modify it so that:

ifthe cell in the range Product_Keywords also includes "," thendivide the sumproduct by the number of commas+1

Presumably I could add --(ISNUMBER(SEARCH(",",Product_Keywords) as one of the conditions,

but how would I keep track of the number of commas in the cell?

View 9 Replies
View Related
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
Nov 16, 2009

Immediately a chart is added, excel chooses it's own data to plot. How do I suppress this?

View 5 Replies
View Related
Dec 20, 2011

whats the vba code in filling a selection with a series 1 ,2 ,3, 4, 5, and so on?

cant seem to find it in google and cant make it out on macro recorder

View 3 Replies
View Related
Oct 12, 2010

I have a list of numbers in sequential order. There are numbers missing. Is there a way to have Excel insert rows for the numbers that are missing in the series.

example:

1

2

3

4

8

9

becomes

1

2

3

4

8

9

View 7 Replies
View Related
Mar 14, 2008

I want to ask that I have got a workbook with different number series i want user form where i can enter its start number and end number and then it finds and delete shift cells up said series number i have entered in user form please see mentioned below example.

Series

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

and i want to delete 1 to 5 numbers delete to shift cells up.

View 9 Replies
View Related
Jun 18, 2008

I have an XY chart that I would like to have the user select a single data point in the chart and be able to add a comment in a table corresponding to that point. For example: if the x-values are in A1:A10, y-values are in B1:B10, the user can select a point, say the 4th point, have some code run that will enter a text string into cell C4. I can manage the code to add the text to the appropriate location, but I'm having trouble with 2 things.

First, how can I ensure that the user has selected a single point (as opposed to the entire series or the chart background)?

And second, how can I determine which point is selected? I think I need to determine the point's item number in the SeriesCollection, then offset that number of rows in my data table. But I can't find a way to retrieve that number.

View 3 Replies
View Related
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
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
May 9, 2014

I'm trying to build a function, but I haven't been able to successfully navigate some of the eccentricities of Excel formula-building.

Right now I am working on counting all of the cells that contain numbers (so likely just a =COUNT function), but will be eventually getting other descriptive stats as well. My problem is that I would like to create a formula that will perform the counting/mean calculation, etc., but only on a specific column that has the header cell that contains several specific words. Rather than having to type in the target words for each column, I would like to have the target words in the function be imported from multiple designated cells.

For example,

I want to find how many numbered cells are found in column E headed as "dog brown," but I also have columns headed as "cat brown" "dog black" etc. (which I will be using the formula on later) to paint a small picture, in my descriptive table, "dog" is in B31 and "brown" is in C30, then "cat" in B32 and "black" in D30.

View 11 Replies
View Related
Nov 18, 2013

Is there a way for me to enter numbers in my cells and have them a specific color? I want all my odd numbers to be bold red with a yellow highlighted cell, my even Numbers to be green with a gray color cell.

I want it to look like this 7 and 8. I can not seem to find anything to show the high light but basically the red 7 will be in a yellow box and the green in a light gray. I will be entering rows and rows of anywhere from 4 to 8 digits

View 3 Replies
View Related
Aug 15, 2014

I have the onerous task of dividing up a series of steps in one cell into multiple cells. I tried the text to columns utility, but it only delimits once, not multiple times. Below is a data example:

"1) Load App

2) Login using Administrator Access details .

3) Click on icon 'Admin' on the right hand side of page.

4) click on subcategory 'Administration'"

"1) Load App

2) Login using Administrator Access details .

3) Click on icon 'Admin' on the right hand side of page.

4) Click on subcategory 'Administration' .

5) Click on any Organization name from 'Organization' section in the list.

Or click on 'Add Organization' icon in section 'Organization' .

6) Validate field 'Parent Organization' in 'New Organization' or 'Edit Organization' form"

"1) Load Documoto.

2) Login using Administrator Access details .

3) Click on icon 'Admin' on the right hand side of page.

4) Click on subcategory 'Administration' .

5) Click on any Organization name from 'Organization' section in the list.

Or click on 'Add Organization' icon in section 'Organization' .

6) Validate field 'Tenant ERP Accessible' in 'New Organization' or 'Edit Organization' form"

1) Load App

2) Login using Administrator Access details .

3) Click on icon 'Admin' on the right hand side of page.

4) Click on subcategory 'Administration' .

5) Click on any Organization name from 'Organization' section in the list.

Or click on 'Add Organization' icon in section 'Organization' .

6) Validate field 'DMZ license key expiration period ' by click on checkbox or by 'Uncheck' checkbox"

I need to put each step into it's own cell.

View 1 Replies
View Related
Jul 3, 2014

I'm trying to program a spreadsheet for staff attendance at a school. In the spreadsheet the staff member has personal days, sick time, and sick time in the bank (sick time rolled over from last year). When they use sick time, it needs to come out of the bank first, until it reaches zero, then it should move on to the sick time accumulated for this year.

Staff attendance WIP.xlsx

On the left side is the staff name, sick time, personal days, and bank. Then are the school days for July (if a staff member uses sick time/personal day it is placed under the date it was used). On the far right is the total remaining amount that the staff member has left.

The formula I have for cell AF4 is: =MAX(0,G4-SUM(H4:AC4)) so that the cell will not go below zero.

The formula I have for cell AG4 is: =E4-(COUNTIF(H4:AC4,"P")+(COUNTIF(H4:AC4,"HP")/2)) So that when P for personal day is entered, it will take a whole day, and when HP is entered a half a personal day is subtracted.

I need to figure out a formula for cell AD4. I've tried a couple of formulas I've seen on other posts, but they weren't for a series of numbers like this. And when I put them into this particular spreadsheet, it would subtract the bank until zero, move on to the 'sick time' cell and subtract the SUM of all the days sick rather than subtracting what was left when the 'Bank' reached zero.

View 1 Replies
View Related
Nov 15, 2008

The series 1,3,6,10,15,21,28,36 ("A" series) is the ongoing sum of 1+2+3+4+5+6+7+8 ("integers") and naturally goes on for as long as on chooses. I am only interested in the numbers up to 100.

Is there an Excel function that will allow me choose any integer as a starting point and any higher integer as an ending point and provide the sum of the "A" series associated with the two choices.

For example: If I begin at 6 and end on 12 it would give me 308 =28+36+45+55+66+78

00

11

23

36

410

515

621

728

836

945

1055

1166

1278

1391

View 9 Replies
View Related
Dec 10, 2013

I have a list of data in Column A of a spreadsheet that is all at different lengths.

I would like to return the numbers from this (see below) seperately and also everything after the last number so returning "green"

Example:

EAST MIDS, YORKSHIRE0112012green

EAST MIDS, YORKSHIRE0112012red

EAST MIDS, YORKSHIRE0112013black

EAST MIDS, YORKSHIRE0112013brown

View 1 Replies
View Related
Dec 4, 2012

How can I subtract a series of numbers from an array. example.

Code:

B

C

D

E

F

G

H

I

J

K

L

M

N

O

P

Q

R

S

T

13

14

25

26

37

39

[Code] ......

=ABS(1-B2) C2,D2,E2,F2 AND G and after start again with =abs(2-B2:G2)

I want to know how to do this for example until 60, I mean 60 minus the row B:G

View 1 Replies
View Related
Aug 30, 2013

I have a graph with something like 40 series that are being plotted. For one, I create non-standard data labels using VBA. In that code, I refer to ChartObjects(1).Chart.SeriesCollection(34). At some point I deleted a series that preceded it in the "select data" list and the code crashed. Took a lot of digging to learn that SeriesCollection(34) had become SeriesCollection(33).

To avoid this in the future, is there a way to refer to the SeriesCollection by a unique name rather than a sequential number?

View 4 Replies
View Related
Apr 5, 2014

I have a series of raws with 7 numbers in each raw; sums may vary a lot from raw to raw, and also differences between one number and the other in a same raw.

Now, i need to obtain a much simpler distribution of scores, so reducing differences.

I would need to transform all numbers into a 0-4 score distribution, according to differences between numbers in a same raw. It's like a kind of rank score.

I want to obtain in each raw:

- 1-2 numbers with score = 4 (if three or more numbers have similar values: no 4 scores)

- a maximum of 3-4 numbers with score 2-3 (according to differences between these numbers but also also according to the 4 scores already assigned)

- all other scores = 1 if the numbers are >0

- all other scores = 0 if the numbers are 0

example: |14|23|3|0|45|0|10| => |2|3|1|0|4|0|2|

Would it be possible to do with Excel?

View 9 Replies
View Related
Mar 25, 2004

I have a spreadsheet, with about 1,500 lines. In column A is a list of numbers that I need to remove the last digit from each number, for example in A22 is 02602726521 - I need this to be 0260272652. The numbers are all uniqe.

How can I remove the last digit from every number, without going in manually to do so?

View 9 Replies
View Related
Jun 12, 2014

Have Series 1 (to be a line chart) with 20 data points X values are 0 to 68. Have Series 2 (Bar chart) with 68 data points, again X values 0 to 68

Cant get series 1 to display beyond X value of 20

View 3 Replies
View Related
May 27, 2014

I have a series of numbers in cells B2 to G2. The maximum value of each of these columns is found in B5 to G5. Is there a formula that will calculate the percentage of each of the values in row 2 (B2 to G2) and tell me which is the lowest number based on the percentage? Example...

Row 2: 12, 100, 43, 1444, 141, 332

Row 5: 25, 140, 55, 3000, 244, 440

Which number in row 2, is the lowest percentage of the total possible points found in row 5?

B2 is 45% (12 divided by 25), C2 is 71%, D2 is 78%, E2 is 48%, F2 is 58%, G2 is 75%.

I want a formula to tell me in a new cell that "12" (in B2) is the lowest percentage in that series.

View 4 Replies
View Related