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.
View 9 Replies
ADVERTISEMENT
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
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.
View 6 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
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
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.
View 4 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
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.
View 9 Replies
View Related
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.
View 9 Replies
View Related
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)
View 2 Replies
View Related
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
View 9 Replies
View Related
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?
View 6 Replies
View Related
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).
View 9 Replies
View Related
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?
View 9 Replies
View Related
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.
View 2 Replies
View Related
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
View 2 Replies
View Related
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
View 6 Replies
View Related
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.
View 9 Replies
View Related
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.
View 4 Replies
View Related
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.
View 9 Replies
View Related
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..
View 6 Replies
View Related
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
View 6 Replies
View Related
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
*
View 10 Replies
View Related
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)
View 9 Replies
View Related
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.
View 9 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
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
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
