I have a value in a cell that is to one decimal place. I need to round this value to the nearest 0.5 multiple up or down which ever is closer. The value in cell A1 reads 6.6, therefore rounding I want the cell to read 6.5 If the value in A1 is closer to 6 say 6.2 I want the cell to read 6.0
View 5 RepliesSay i got a figure 9.9218. If written "=floor(9.9218,0.0625)", the result i would get is 9.875. However, if the formula was written with the CEILING function, I would get 9.9375.
Now here's the fun part. Is it possible to combine the FLOOR and CEILING function codes into one complete function, where it could determine whether 9.9218 is closer to 9.9375 than 9.875?
The following was taken from my decimal equivalent chart, at spaces of .0156:
.875
.8906
.9062
.9218
.9375
*As seen here, .9218 is closer to .9375
1. If column D422 is greater then 9,750 then multiply D422 by 10% and then floor it to the nearest 100. But what I am trying to do is;
If D422 is between 9,750 and 9,999 then multiply it by 10% then ceiling it to the nearest 100 which would be 1,000. But if it is equal to or greater the 10,000 then multiply it by 10% then floor it to the nearest 100. So the minimum 10% returned should be 1,000.
=IF(D422,FLOOR(D422*10%,100),9750)
I have the following calculation that I use to determine if a price is outside of a floor or ceiling, if it is outside of the range it uses either the floor or ceiling price
=IF($G$76E76,-(($G$76-E76)*F13),0))
the formula is in cell G71
F13 is the total quantity
E76 is the ceiling price of $15.00
F76 is the floor price of $7.50
G76 is the calculated price of ($6.21)
In this case the floor of $7.50
I would like to modify the formula to where if you input N/A (or something else) that it will give a result of $0. I do not want to put a zero in the cells for the floor and ceiling price because it will give me a result of $0.
I am trying to write a macro, but am failing at it.
Basically, what I am trying to do is:
----------------------
Select a whole row, for example 5
Define that row as "Ceiling"
Then select a row a few steps down, for example 18
Define that row as "Floor"
Then select the range (ceiling, floor)
Then print that area.
-----------------------
But I have no idea how to write the code.
I was using the "round" functionin VBA and realized that it does not work, because it uses the "Bankers" method of rounding, whereas I want the "Arithmatic" rounding, I looked through the web and went to:
http://support.microsoft.com/kb/196652
But the problem is the function is not working.
HTML Function AsymArith(ByVal X As Double, _
Optional ByVal Factor As Double = 1) As Double
AsymArith = Int(X * Factor + 0.5) / Factor
End Function
I put in AsymArith(8010.095,2) and it gives me 8011.
I also tried this function:...................
I am trying to ask if a truncated number is divisable by 9. e.g. 1234 truncated would be 123. To truncate the number i've tried to devide it by ten and then round it down using the floor or rounddown function. However i get the "Complie Error Sub Or Function Not Defined" error in my User Defined Function
If Floor((i / 10), 1) Mod 9 = 0 Then
Do my groove thang
End If
The compiler is IDing Floor (or rounddown) as the problem.
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 have to multiply a value X by 20, then depending on the Case = ProteinA or Case Else, round up to the next multiple of 100 or 500.
If Case = ProteinA, I want that 20*x to be rounded up to the next 500.
If Case else, then I want the next multiple of 100.
I've got a UserForm with a bunch of TextBoxes on which the user is inputting numerical values into.
After any entry a macro is rounding the value to the nearest multiple of 25 using application.worksheetfunctionceiling/floor functionality.
I've got a problem that as the value in the textbox approaches 33,000 I get an overflow error message. (I'm presuming at 32 bits)
heres the data: [url]
Im meant to produce a simple spreadsheet that calculates the floor area of a new build city centre hotel. The developer is looking at various plots of land that allow differing sizes of floor plates and storey heights. The key variables are the number and type of bedrooms, number of floors and whether the hotel is classed as a premium or budget hotel.
I need to produce a spreadsheet that shows the key variables and the total calculated floor area at the top of the sheet.
I am trying to nest an IF function with a CEILING function. If C10 is < 3.5, make it 3.5, however, if C10 > 3.5, CEILING (C10, 5)
right now it looks like:
If (C10
I don't know if there is a setting I'm missing or I'm going mad but when I use the round function in VBA it doesn't round.
I am using Excel 2000. See the example attached.
In the cell A2 I have a value 0.525, cell B2 has a formula "=round(A2,2)" which = 0.53, but cell C2 is assigned via VBA ie
Sheet1.Cells(2, 3).Value = Round(Sheet1.Cells(2, 1).Value, 2)
and the result is 0.52??
I am aware of the following topic in the VBA Help file:
"Using Microsoft Excel Worksheet Functions in Visual Basic
You can use most Microsoft Excel worksheet functions in your Visual Basic statements. To see a list of the worksheet functions you can use, see List of Worksheet Functions Available to Visual Basic.
Note Some worksheet functions aren’t useful in Visual Basic. For example, the Concatenate function isn’t needed because in Visual Basic you can use the & operator to join multiple text values."
And I'm aware of how to call Excel funcitons from within VBA; e.g., answer = Application.WorksheetFunction.Min(myRange)
However, not only are some Excel functions not useful; the fact is they cannot be used because VBA has a native function that does exactly the same thing and you have to use that native VBA function to achieve your goal. It is these overlapping functions that I am especially interested in. I want to know what I should use directly in VBA and what I need to go to Excel for.
Using vba how to round off the value e.g.: The number 582.356894 has to be rounded off to 582.4 ....
View 1 Replies View Relatedi have a list of minutes in cells a1-a5 say 123 256 147 158 235 divided by 60 giving a total of 15.3 hours. i want the hours to round up if over the. 5 mark or round down if under .5 how would i get the desired result?
View 2 Replies View RelatedA cell value is calculated via a formula in vba. I want to round the result down to the nearest odd number or down to the nearest even number, depending on conditions in an other cell. The result is already an integer.
View 8 Replies View RelatedTried the code below to change figures from countless decimal figures to two, however excel is not having any of it,
Range("d:o").Select
Application.WorksheetFunction.RoundDown (2)
Why is Excel not rounding up a percentage of 93.5 to 94?
View 6 Replies View Relatedrounding the numbers. I am working on a quote in which quantity is arrived by dividing the sell price by Total sell price. The condition is the result (quantity) should always be a whole number, I can achieve that by cell formatting but when the calculation is done using handheld calculator the results are different.
I need the result to be same if using excel or handheld device i.e quantity in whole number.
Hi, I have this issue:
1.67 -> DISPLAY 2 ROUNDED
1.67 + 1.67 = DISPLAY 3 ROUNDED
but I would like to know how to sum 1.67 as 2
WANTED -> SUM 2 AS A NUMBER AND NOT A ROUNDED
2+2 = 4
I've been trying various combinations, but currently no joy
I need to work out how long the batten has to be so the roof sheets fit evenly, the measurement has to start from 1460mm and go up in increments of 80mm eg 1540mm, 1620mm, 1700mm and so on.
But the number has be closest increment of 80mm over the shed width if this makes sense, the size of the battens for 2400 width shed would be 2420mm but i need this to work out for any width shed not just 2400.
I am using the rounding function in excel and its rounding to the 10's, is there a way to round to the nearest 5?
View 4 Replies View Relatedi want a cell to round itself. i have a form i'm filling out, based on a percentage of a dollar amount. when the formula calculates, it only shows the first 2 numbers past the dollar point. however, the cell still "knows" what the number is. I have several of these formulas on a spreadsheet, and the sum of them at the bottom of the page is NOT what would you would get adding up the numbers you see on the page, as it is calculating numbers you can't see.
View 5 Replies View RelatedI am trying to do is have the roundup formula round up the result of a more complex formula BUT do it all inside of the same cell? The formula I have is in cell A1 and currently I have to have the cell that contains the round up formula (in cell A2) and have it reference A1. The complex formula is =((280283.47/798186.89)*(700*20*4)) and the result is -19,664.41 which I want to round up to $20,000. Is there a way to make this all occur in just cell A2 or am I stretching it?
View 4 Replies View RelatedI have a number say:
156.9679628 then I go to format to change it to 2 decimal then it would show
156.97 and then I c&p that ... I still get 156.96796284 how do i just get
156.97
I'm creating a spreadsheet to calculate materials with the following columns Cost/10% of Cost/Customer Cost/Qty/Total cost.
I understand that whilst showing rounded to 2 decimal places excel stores more than this in the cell. which then throws out the Total cost by a few pence.
My research leads me to believe I need to use the ROUND function but I'm unsure which cell to use it or how.
round to two numbers. i have this formula
View 3 Replies View RelatedHow do I take a date value and round it off to a fixed month and day of the year following the year entered.
Ex: In cell A1 I have a date: 12/28/2009
I want to round off the date to January 1 of the next year. So I need a function that would return 01/01/2010 (jan 1 2010).
How do I get Excel to round up all values from .3 onwards. EG 2.36 to 3 and 2.29 to 2?
View 4 Replies View Related