# 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
ADVERTISEMENT
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
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
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
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
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
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
Sep 10, 2007

i have a User form that i'd like to add preset tabs command locations to.

For example,

on open i'd like the active cell to be on cell M3. and as the user presses the tab button jump to the following cells locations:

P3

Q5

P7

Q7

P9

View 9 Replies
View Related
Apr 23, 2007

I have a spreadsheet with time values incremented in column A.

In column K, there are numerical values that correspond to the time in column A.

Elsewhere, a specific time is chosen from which to begin the summing of the values in column K.

I need to be able to sum the values in column K until preset values are reached. Then I need to know at which corresponding time value the summing loop stops.

For example, a time of 11:00 AM is chosen as the "start time". I want to sum the values in column K until they are >= 2.000.

At that point, I need to know what the cell value was in colum A that is in the same row as the last cell value in column K.

I can't seem to do it with simple Excel functions without falling into a self referencing loop. So what I need is for a VBA loop to lookup the appropriate values in column K, sum the values, check the total, and continue the loop until the preset total is reached. Then look up the value in column A in the last row of summed values, and return that value.

View 9 Replies
View Related
May 22, 2014

With the sizes in in B6 AND B7 these result in an error in B68 , the formula is in B68 and the tables are on sheet 3 , the increments have to stay as they are.

View 6 Replies
View Related
Oct 5, 2007

I have a simple formula that adds two numbers and divide the result by another number

K1: SUM(E1,F1) / H1

K2: SUM(E2,F2) / H2

K3: SUM(E3,F3) / H3

K4: SUM(E4,F4) / H4

K5: SUM(E5,F5) / H5

etc, etc

and then I average the results

AVERAGE(K1:K5)

etc, etc

When H1, H2, H3, etc is 0, it generates a #DIV/0! (since you cannot divide by 0)

I want a formula that I everytime a H1, H2, H3, etc is 0, I want to set it to a specific number H1, H2, H3, etc to 15.00

For example:

IF (H1 = 0, 15.00, sum(E1,F1) / H1)

If the number provided (H1) is 0, set it to 15.00, otherwise add the two numbers (E1, F1), and divide this result by the number provided (H1)

But this is where the problem is:

In every single cell that I have NOT yet provided information, it treats the BLANK cells as if it was 0, and has set every single cell to 15.00.

View 9 Replies
View Related
Jul 8, 2014

I have a pivot table where i would like to filter the values according to top 10, bottom 10 etc. I know that it works with using the filters in the pivot tables (Value Filters - Top 10 ...), but for making it easier and faster to use these filters, I would like to have some preset filter options as buttons or a drop down menu that immediately display them.

So that you don't need to choose the filter values yourself. I need to be able to either filter the e.g. Top2 Business Areas or within one Business Area filter the top 3 Business Groups. I thought about maybe making a drop down menu where you can choose the business areas and then another drop down where you can choose the Filter such as Top10, Bottom 10, Top 3 etc.

bSample_Pivot.xlsx

View 3 Replies
View Related
Nov 19, 2012

I have situation where my account (from trail balance) will present to different grouping based on reporting, example below. How I can formulaize or using VBA to sum to amount based on the format.

Example :

if i want the format b : if i sumif HCM - the total should be from account code a1001,a1002,a1003 and hrd will sum up based from account code a1004,a1005,a1006

Account Code

format a

Format b

format c

format d

format e

a1001

adm

hcm

adm

adm

hrd

[Code] ........

View 7 Replies
View Related
Dec 6, 2013

I have 90+ rows of data that I want to transfer into a table format on another worksheet. At present I have laboriously been copying and pasting from one to the other but am losing the will to live

Id like to create a macro to do this for me. Is it a matter of recording the macro to replicate as I copy and paste or is there a better way

View 3 Replies
View Related
Oct 27, 2009

I have a big group of test data each has a list of record of the format (time, parameter). But they have different time step, ie. the records have different number of data set in the same duration. I would like to make them the same, so I need to extract data from the records. For example, I have a list of the original record (in column A & B) as follows: ..

View 9 Replies
View Related
Apr 3, 2012

I have few numbers in Column "A". Few are normal figures & few are decimal numbers, like this:

20

5

5.63

4.5

200

53.263

125.5

Now I want to filter only the decimal numbers, so that after filter the result will show like this:

5.63

4.5

53.263

125.5

But I don't want to use any other column for doing so. I can do it using MOD in other column but I want the result in the same column.

View 7 Replies
View Related
May 30, 2009

I have an inherited formula and I am not sure if it is giving me the correct answer. It is:

=0.03*668*0.75*0.10

The result is 1.503

What I am aiming for is to get 3% and 25% of 688, deduct the results from 688 and then get 10% of that answer.

Is the inherited formula correct?

View 3 Replies
View Related
Mar 23, 2008

The list columns 5,7 and 9 has number entries.The userform has 3textboxes.is it possible to link total amount of this numbers result into the textboxes.Like column 5"CZ" entries total will showup in textbox10,column 9"DD" = textbox14

Private Sub TextBox2_Exit(ByVal Cancel As MSForms.ReturnBoolean)

Dim a, i As Long, ii As Long, b(), n As Long

ListBox6.Clear

With TextBox2

If .Text = "" Then Exit Sub

If WorksheetFunction.CountIf(Range("cv:cv"), .Text) = 0 Then

MsgBox "No Entry !"

TextBox2 = ("")

Exit Sub..........

View 9 Replies
View Related
Jan 14, 2009

If I have a dropdown with

A (value 1)

B (value 2)

C (value 3)

D (value 4)

and a person selects one of those letters in the dropdown in column 1, I would like column 2 to show the number value associated with it

View 14 Replies
View Related
Apr 19, 2014

I'm trying to automatically add and remove one criterion from an autofilter without disturbing the previous existing criteria The autofilter code sounds like this:

Code:

ActiveSheet.Range("$A$1:$L$47").AutoFilter Field:=6, Criteria1:=Array( _

"Clay", "Grass"), Operator:=xlFilterValues

How would I go about referring to this array and adding another item called "Hard" to any pre-existing elements in this array?

I plan on attaching this code to a button and use it as a filter, so when the button is pressed, the item will be added to the list of criteria, and if it is "un-pressed" the item will be removed.

View 3 Replies
View Related
Jul 13, 2006

First it should Insert a Column to the left of A. Then it should go to column B and search for the number 99 and delete all rows after it. In column C I want it to do the following formula =vlookup(b2,descriptions!$b$2:$c$250,2,false) and have it copied all the way down to the end of data.

Next I need labels added to the tops of the rows

Next I need the description columns in C to be copied to column I

Finally, beginning at J2, I would like the formula (e2 * g2)/100 entered to the end of data. Attached is sample data.

View 2 Replies
View Related
Jan 29, 2009

Need to divide a cell containing a time value (hrs:mins:secs). My timesheets calculates the number of hours on a job and I want to divide this total by the number of components made.

View 7 Replies
View Related
Jun 24, 2014

How to do the following?

When dividing two cells and the answer is zero then I want to return a different value. I've tried the following with no luck:

=IF(ISERR($D20),"*",$G20/$G$22*100)

or

=IF($H19,$G19/$G$22*100,"-")

=if a1/b1 = 0 then "*", else return actual value

(N)(%)

64 13

82 16

82 16

2 0 --> change this to * or -

264 52

13 3

507(total)

View 9 Replies
View Related
Jun 9, 2006

I have a macro that imports a report. If the Charge Type in column A is BTOREPLX, I need to prefill the field next to it in column B with the text "REPLX", e.g. "REPLXCDROM". I've attached an example of the report.

View 5 Replies
View Related
Sep 15, 2014

Dividing column in equal parts divide a column in groups with the same rows and the same total sum? For example, I have the column A as following:

8,942

2,807

2,568

5,818

5,818

596

8,942

4,390

1,607

890

8,772

1,103

The total number of rows is 12 and total amount is 52,254

I want to divide the column in three equal parts with the same total. So each group must contain 4 rows with the total sum 17,418

View 9 Replies
View Related
May 23, 2006

Have a list of information and would like to divide that list by a number that the user enters. Then from the total count of nonblank rows divide by the numeber entered by the user didive the list into equal parts and print out each group with a page break per groups.

Have attached an example.

View 6 Replies
View Related
Jul 26, 2014

V = 100 x [E /(IY + YP)] x Sqr. [((R + G)/2 / (IY + YP + F)]

V = 100 x [6.10 /(5.60)] x Sqr. [((10.2+2)/2 / (5.60 + 2.1]

V = 108.93 x Sqr (.79)

V = $96.89 / share as of 7/18/2014

E = Earnings in $ / share 6.1 (b9)

IY = Interest rate in % 5.6 (b10)

R = Profitability Factor in % (internal growth rate)10.2 (b11)

G = Earnings Growth in % / yr. 2 (b12)

F = Inflation Rate in % / yr. 2.1 (b13)

Here's what I have, result should be $96.89, I need to tell excel to take square root of everything to the right not just (B11+ B12)

=100*(+B9/B10)*SQRT(B11+B12)/2/(B10+B13)

View 3 Replies
View Related
Jan 24, 2014

I'm not sure if it's just been a long week but I can't get get the following to work in a sum formula.

In the example below I want to deduct B1 from A1 and then Divide by C1. The result should be 0.57 but I can't get to it within 1 formula.

9:49:12 - 6:18:46 = 3:30:26 / 2 = 0.57!!! Not sure if it's because I'm using unfriendly hh:mm:ss format but I need to.

A1 - 9:49:12

B1 - 6:18:46

C1 - 2

View 5 Replies
View Related
Feb 10, 2014

Cell A1 contains 2, B1=220077:23

In C1 I created a function = (2*1000)/B1, I get error. I want the answer 2000/220077h23m = 0.009087713

View 5 Replies
View Related