I have written a macro which includes a number of calculations. This has resulted in a value with a number of decimal places. I want this value rounded to 1 decimal place and this value written to a cell. How can i round this value correctly.
View 4 RepliesI 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)
What is the code to round up a range of numbers at certain decimal.
View 14 Replies View RelatedI have a sheet that i use for doing quotes for sound systems. i have a qty and price column that i input and then a column which gives me the overall total. ie qty 2 x unit amount 160.00 = 320.00 i also have a discount column that i use and a column that gives them the final price after discounts. so i will put 22% into my discount column and get 249.60 as my final price for the 2 items after the discount.
what i am wanting to do is "round up or down to the nearest 5.00 mark. so for instance, the final price in this case would be 250.00 is this possible?
I have a worksheet (Sheet1) that gets information from other sheets (1)
So in cell D7 I have the function ='1'!K33
K33 is from the Sheet labeled (1) obviously. Now the problem I am having is if the number 20.6, it automatically rounds up to 21. I need to number to round down to 20 regardless if its 20.1, or 20.9.
Since I already have a formula in D7, how do I still get the information from the other sheet but have the number round down for me. Is it possible?
Am currently calculating the total number of cartons used for different items with this formula:
=SUM(F99/352)+(H99/135)+(J99/110)+(L99/60)+(N99/28)
The divisors are the number of items in a carton.
Would like to round up each of the 5 subtotals in the above formula to the next full number, so that the total will be the sum of full numbers.
What would be the correct formula?
Cell "CostPerEvent" has the value .298896, and when I run the following statement I expect that A2 will contain the same value .298896, yet it is entered into the cell as .3
Cells(1, 2).Value = Range("CostPerEvent").Value
Is there some kind of implicit rounding going on? I rewrote the code to:
Cells(1, 2).Value = CDbl(Range("CostPerEvent").Value)
But I am curious whether this behavior is documented...
How can I make excel display 321.165 as 321.16 when I have the cell format set to number and 2 decimal places instead of rounding it up to 321.17?
View 11 Replies View Relatedcustom number format.
The numbers in the worksheet read like this: 114'31.5
The number behind the hyphen are not to exceed 32, after 32 they become a full point (115'00.0).
I have been trying for ages to get a formula where any number prefixing .5 automatically rounds up and not down as the excel standard does.
16.4999999 does show with no decimal places as 16.
16.5 does show with no decimal place as 16. But I want it to round up to 17.
and obviously 16.50000001 does indeed round to 17 anyway.
Its purley numbers that have .5 I need to go up and not down.
In one cell i have £92.00 to 2 decimal places. If i increase that to 4 decimal places it is £91.9998. I need this £92.00 to show as £91.99 (only as 2 decimal places not 4)but when i go back to 2 decimal places it shows as £92.00 again.
View 2 Replies View Relatedlooking for for some help on a fairly simple problem: i've attached a worksheet, and in column B (Due to Supply Chain) i'd like to insert a formula that will subtract 21 days from the date in column Z (Pub Date), and then round that date to the nearest wednesday. is this possible?
fyi: the dates in column Z are in a yyyy-mm-dd format; they don't have to remain that way.
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.
I need a formula showing that if a number is less than say 1.25 then it rounds down to 1.0 and if its between 1.25 and 1.75 then it rounds to 1.5 and then if greater than 1.75 then it rounds up to 2. I need it to work for all numbers not just 1.
View 10 Replies View RelatedI am trying to write a formula that will round numbers to $.05, $1, $5, and $10. The formula needs to be written in a way that If c75 <100 round to $.05, if c75 is greater than $101 but less than $500, round to $1, if c75 is greater than $501 but less than $1,000, round to $5, and if c75 is greater than $1,001, round to $10.
=IF(C75<100,ROUND(C75*20,0)/20)
but i can't quite figure out how to get the rest of it to work.
numbers in one column need to be rounded to the nearest half decimal, with next conditions(ill took number 704,00 for example):
- if last two decimals are < 0,25 then my number has to be 704.00
- if last two decimals are >0,25<0,75 then my number has to be 704.50
- and finally if last two decimals are >0,75 then my number has to be 705.00
I tried with IF, CEILING, INT and ROUND functions but i didn't made it work with three options, that i need. I only made it work if i use only two options.
I have a spreadsheet which has a daily schedule. It goes from 8am to 9pm in 15 minute slots.
I am looking to do a Time Bar. Using Conditional Formatting I was wanting to know if the time now can be rounded up or down in any way
Example, As I write this the time is 15:37 I have this in a cell F4
In row8 Cells C to BC I have the time in 15min slots like 08:00 08:15 08:30 and so on. What I was thinking was could I round the time from 15:37 down to 15:30 so I can use a Conditional Format to trak the time in my schedule?
Or is there an alternate way?
I have a set of data that is meant to distribute a certain number of items to different groups.
I have 10 groups, some will get more than others depending on previous usage. The problem is that I need the percentages to be in whole numbers and the total percentage needs to be 100%. I tried rounding but it doesn't work. Here is an example from one item's line.
Group#,1,2,3,4,5,6,7,8,9,10
Dist %,.1,0,.04,0,0,0,0,0,.87,0
These are rounded and it comes out to be 101%.
I have a number 53.30242 in a cell a1. How can I just make it 53.302? I don't want to round it to 3 decimal place, just keep the first 3 digits.
View 6 Replies View RelatedI have a sum in my sheet (=U27*0.5).
Now obvously this is the value of U27 x 0.5, this varies depending on the value of U27, however is there a way to round up the sum to the nearest even number. So if the sum produces .75 then I want it to be .76.
This is what I am attempting to do via a formula in a worksheet (not VBA):
IF the last two digits of H8 are greater than 50 AND if those digits are less than the last two digits of the values contained in H9, H10, H11, H12, H13, H14, H15, and H16 THEN I would like to ROUNDDOWN(H8, 2), ELSE ROUND(H8,2).
What do you think? I've tried using multiple AND's in a conditional statement but to no avail.
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 RelatedHere it is:
I used ceiling and floor for this but it was futile.
I was hoping to round off a no. according to the limitations set,
For example:
Sample Entries Preferred Rounded-off nos.
Cell A1 Cell B1
.40 .40
.39 .40
.38 .40
.37 .35
.36 .35
.35 .35
.34 .35
.33 .35
.32 .30
.31 .30
.30 .30
When using ceiling and floor formulas there was an error prompt telling me that there are too many arguments.
i've set up some columns to calculate what date certain things need to be done on for each course that we have running (using the =date(year(**)+x,month(**)=y,day(**)+z) function) but what i'd rather like it to do is to round the calculated date to the nearest working day date.
View 9 Replies View RelatedI'm copying a column range containing numbers with varying decimals from one worksheet to another. The new worksheet and column are set to have no more than two decimals places in the cells through the formatting options.
However, numbers stay showing their original amount of decimal places, so I tried hard-coding the format with:
Sheets("Toolset Scorecard").Columns("G:G").NumberFormat = "0.00"
I have a set of numbers that I need to round up to the nearest number divisible by 5. For Example...
28 would be 30
-13 would be -10
There is one additional part though. If the number is already divisible by 5 I need it rounded to the next number. For Example:
0 would be 5
-10 would be -5
10 would be 15
I am looking at trying to do a complex rounding within Excel. I have a spreadsheet of all my product with my cost. I then convert to show what margin I want to sell my items at and then round to .99.
What I am looking at doing is getting my pricing more uniform so that instead of having 23.99, it is 24.99 or 20.99 it is 19.99. I want all the numbers in the ones' spot to round depending on a set parameter.
I have played around with if statements, but can not get it to work fully. Basically if the ONES number is 0 to 2 I want it to round down to 9.99 (ie first was 21.99 I want it to be 19.99) from 2.01 to 4.98 to round to 4.99 and then 5.00 to 9.98 round up to 9.99.
How can i rounddown time in VBA
I got a variable value EX: 00:30:08/// 0h:30m:08 second.
How can i round it down to the nearest EX: 00:30m:00
Narurally, the variable as not always the same value.
I have excel 2003. I'm working on a weight lifting program and need to have cells round to the nearest 0 or 5. My problem is that the cells I need to round already have a formula in them and I can't get the cell to round the answer of the formula.
D17=U4*.6
I need the answer to be rounded to the nearest 0 or 5.
I am pulling out my hair at the moment-
I have learned that the ROUND function functions differently in VBA vs. in a worksheet.
I have found an MS webpage that explains and also gives some alternate functions that "should" solve the problem.
http://support.microsoft.com/kb/196652
But, my problem remains. There may be a "bug" in the "Fix" Function.
The "bug" (which I have traced in excruciating detail in the VBA) is when I want to round "9.405" to 2 decimals for example, the SymArith function gets a correct intermediate value of "941", but FIX() then gives me "940", and my final result comes out to "9.40". What gives? This is not a "fix"!!
Function SymArith(ByVal X As Double, _
Optional ByVal Factor As Double = 1) As Double
SymArith = Fix(X * Factor + 0.5 * Sgn(X)) / Factor
End Function
(One note about this function and the other replacement functions, MS should have used "10^Factor" instead of just "Factor", so that the functions would work the same as the regular ROUND function.
To debug, I broke the calculation into 2 steps, used Breakpoints to check intermediate values, and used "10^Factor" instead of just "Factor". I still get the same wrong result:
Function RoundSymArith(ByVal X As Double, _
Optional ByVal Factor As Double = 1) As Double
Dim XX As Double
XX = X * 10 ^ Factor + 0.5 * Sgn(X)
RoundSymArith = Fix(XX) / 10 ^ Factor
End Function