# Dividing A Number By 12 With Respect To Rounding

May 23, 2007

Suppose I have the following plan number (24) and I wan't to spread it across 12 columns, as evenly as possible but with respect to rounding.
Easy enough.. Each column receives 2...

Or say the figure was 24.6 and i wanted to round to 2 places...
Easy enough... Each column receives 2.05...

Or say the figure was 24.7 and i wanted to round to 2 places...
Easy enough... Each column receives 2.05... But one would be 2.06!

How about the number is 1 and say, the rounding is 0.1?
Ten of the columns would be .1 while two others (arbitrarily) would be 0.

## Dividing Number With Hours And Minutes?

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

## Dividing Entire Range By A Number

Jul 10, 2006

I have a large worksheet that I need to make into a quarter of what it is now. Each of the numbers is just inputted, so I was thinking there is a way to divide it all by four.

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

## Dividing Number Of Steps In One Cell Into Multiple Cells?

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:

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

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

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.

## Rounding Number And Then Shrinking Number Of Digits To Fit With Narrow Column

Feb 6, 2013

I have a number, eg, 1234567 and rounded to prepare the shrink of the size of the number like

= Round(a1, -3). I have, say now, 1234000.

I need to strip "000" from the number and place 1234 to a cell.

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

## CSV Number Rounding

Oct 25, 2009

I have a section of code that build a single sheet workbook that incudes names and phone numbers to upload in to a web site.

The numbers are in internation format eg 4412345678987

The code works fine but when it saves as a CSV Delimited it round the numbers up so they end up 4412346000000.

## Rounding A Number ...

Jul 4, 2007

I thought this was really easy and I swear that I did this before but I can't remember it at all. What I'm trying to do is take 2 user inputted dates and subtract them to get the total days. After that I divide it by 7 to get the total weeks. and ususally I will get a decimal. However I need to round this up to the next whole number.

## Rounding A Number Up In A Cell

Jan 28, 2009

I need to round up the answer of 1.72 to 1.73 for the formula listed. The formula in that cell reads as follows: =(22/12.762)

## Rounding To Specific Number

Feb 28, 2012

I have a column of numbers that represent sales prices.

If the price ends in anything between .x0 - .x4 I want the replacement number to be .x4 and if anything between .x5 - .x9 I want the replacement number to be .x9.

For example, the sales price is 1.93. The "rounded" number should be 1.94.

More examples:

3.76 = 3.79
3.13 = 3.14
2.50 = 2.54

## Rounding A Number To The Nearest Five

Sep 16, 2006

I have an overtime spreadsheet where I calculate how mush I'm paying on vouchers, as they only come in lots to �5.00 I want to round the figure to the nearset �5.00, doesn't really matter if I overpay as it will even out next time, what's a basic forumula to work this out?

## Determine The Age Of A Value In Respect To A Table Of Columns

Jan 13, 2010

I'm trying to determine the age of a value in respect to a table of columns and just cannot seem to nail it - perhaps it's my logic .

1 year2 year3 year4 year5 year5 year +Balance395.0384769.77271115.9771436.9441738.5292009.48775

I have a balance (in this case, 775), I need to determine where it sits within the table.

For example, if my balance is less than a year, retun "1 year less". If it's greater than the one year, but less than 2, return "1 year old"; if it's greater than 2 years but less than 3, return "2 years old". I need to do this all the way until it's greater than 5 years.

I guess it's an IF statement, but my calculations keep churnin out 2 years, when in fact, we can clearly see it's 3 years (because it's greater than the 2 year value).

## Rounding A Number Up To The Next Value In A Custom List

Nov 17, 2008

I have a scenario where I calculate the size of a piece of process equipment, and then select the next highest size from a list of standard equipment that we supply.

I've managed to write an ugly brute-force solution to a relatively straightforward previous situation, but I'm reluctant to redo the entire exercise when I'm sure there's a more straightforward way of doing this.

An example of the problem:

We sell equipment in sizes 1, 3, 8, 12 and 25. I calculate that the piece of equipment theoretically needs to be size 19.6. I want a formula that will automatically generate the answer to this as 25. Similarly, if my formula shows a theoretical size of 12.01, I want the formula to state the actual size as 25.

My previous method of solving this was a series of nested ifs, searching for the rounded value of my formula output, then that value +1, then +2 upto a maximum of +5. This wasn't a pretty sight, but it worked for that situation.

Are there any other ways of solving this that are more elegant than mine?

## Rounding Up A Cell Value To Next Highest Whole Number

May 4, 2006

I have a whole number value in cell F18. In cell F24 I want to enter a formula that allows me to divide the value in cell F18 by 4 and if the result is not a whole number, round it up to the next whole number.

## Rounding Up A Number To The Nearest 25th

Oct 27, 2006

I have a cell that returns a value...lets say "439". I need the cell to automatically round the number up to the nearest 25th. Like this:

439 would become 450
521 would become 525
551 would become 575

## Excel 2010 :: Rounding Number Of Years?

Sep 6, 2012

I'm using the following:

B23=IF(A23="","",DATEDIF(A23,I3,"y"))

Where:
A23 = a date of installation
I3 = TODAY()
B23 = a number of years

It currently calculates correctly if the number of years correctly if it's older than 1 year. If under one year, it yeilds 0. I would like B23 to show 1 if the current formula yeilds 0.

I want it to yeild a 1 if the current calculation is 0.

Windows 7 Ultimate / Excel 2010

## Macro To Convert Text To Number Without Rounding

Mar 27, 2014

This is what I have so far.

Code:

Range("L:L").Select
With Selection
Selection.NumberFormat = "General"
.Value = .Value =
.NumberFormat = "0.0"

[Code]...

The .NumberFormat = "0.0" is newly added and doesn't work.

## Rounding Number In Formula To Remove Decimal

Sep 19, 2006

My formula is not rounding properly. Cells I4, J4 and K4 all filter from the results of cell I3 divided by 3. (e.g. 10/3 = 3.333) I was able to remove the decimals in cells I1:K3, but the between formulas (I4:K4) keeps adding the decimal back, therefore this results in errors to my chart.

## Round To Nearest Even Number. Bankers Rounding

May 15, 2008

We are trying to find the correct formula to round number to the nearest even number. In the attached example, 0.105 should round to 0.10 since the zero to the left of the five is even. If the problem was 0.115 then the answer would be 0.12 since the one to the left of the zero is odd.

## Macro For Automatically Sorting Table With Respect To Particular Column?

Jun 17, 2014

I have a table of 5 columns (each of 50 length) that is automatically refreshed (i am getting the data from web)after an interval of 5 minutes. I want the first column to be sorted alphabetically after every 'refresh' command, that has been executed by the excel. i have tried recording the macro and then running it on keystroke of 'ctrl + m'...the table surely gets sorted out but i can't be pressing ctrl+m every 5 minutes. auto-sort that is required to be executed after the auto refresh command..

## Rounding Formula :: Round To The Nearest Integer (whole Number)

Feb 3, 2007

I would like to round this number (3.5) to become 4, but by using this formula =CEILING(A1,0.5) the result will be 3.5

i need a formula that turns 3.5 to 4

when i use this formula =ROUND(A2,0.5), 3.5 becomes 4, but 3.2 becomes 3 not 3.5

i.e. a formula turns 3.2 to 3.5 and 3.5 to 4

## Create A Function That Numbers Rows With Respect To Data Groups In A Column

Dec 1, 2007

I need to create a function that numbers rows with respect to data groups in a column (column labeled "Type" in this example). The result would be that shown in column A in this table.

How do I write the function?
#TypeName Date
1CarsJohn1/12/2008
2CarsJane11/10/2007
3CarsMary11/2/2004
4CarsBob12/7/2003
1TrucksMike12/12/2007
2TrucksSandy1/3/2007
3TrucksDale12/14/2006
4TrucksVince4/8/2005

*

## Banker's Rounding Or Scientific Rounding

Jul 9, 2008

I am trying to round similar to Banker's Rounding or Scientific Rounding but I can't find a consistent formula that works perfect with decimals.

Using three decimal places for all the samples, I can get 0.0785 to round to 0.078 but 0.1785 wants to round to 0.179 instead of staying 0.078. Or 0.0005 will round to 0 but 0.5115 wants to round to 0.511 instead of 0.512.

Here is a list of sample numbers along with desired results:
.0785 should be .078
.5115 should be .512
.5035 should be .504
.0005 should be 0
.0025 should be .002
.0194 should be .019
.0195 should be .02
.0135 should be .014
.0115 should be .012
.8115 should be .812

I cannot find a formula which gives me all of these results. Here is a list of the formulas I have tried so far (NOTE: cell A2 is the working cell in my worksheet where I enter the number to be rounded)

1) =MROUND(A2,0.001)

3) =ROUND(A2,3)

4) =IF(ISERROR(IF(MOD(MID(A2,4,1),2)=1,CEILING(A2,0.001),FLOOR(A2,0.001))),0,IF(MOD(MID(A2,4,1),2)=1,CEILING(A2,0.001),FLOO R(A2,0.001)))

5) =EVEN(A2)

6) =ROUNDUP(A2,3)

7) =ROUNDDOWN(A2,3)

## Rounding A Number To Next Whole Number

Jul 4, 2007

I'm trying to do is take 2 user inputted dates and subtract them to get the total days. After that I divide it by 7 to get the total weeks. and ususally I will get a decimal. However I need to round this up to the next whole number.

## Dividing A Time Value

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.

## Dividing And Returning Different Value Than Zero

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)

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

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

## Dividing Column In Equal Parts?

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