Round Up/round Down
i 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 Complete Thread with Replies
Sponsored Links:
Related Forum Messages:
VBA Round Function Vs Worksheet Round Function
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??
View Replies!
View Related
Round Down
Tried 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)
View Replies!
View Related
Round Numbers
19.25581 1.01346 3.29376 0.25337 0.25337 1.26683 Total: 25.33660 If I use the Decrease Decimal on the total 25.33660, it becomes 25.34. How do I round all the sub numbers so that 25.34 is achieved.
View Replies!
View Related
Round Off Decimal
I have excelsheet with the following data A - 1.3 B - 1.3 SUM: 2.6 Now when I want get sum of A and B it shoud be 2 i.e. Round figure of A - 1 B - 1 SUM : 2 Whereas I'm getting sum as 3 instead of 2 as round figure of 2.6 is 3
View Replies!
View Related
Round Down To Two Decimals
i am looking for some formula to round off a decimal number e.g. A1=8.288 if I use the formula ROUND(A1,2) the result is 8.29. But I am looking for a result 8.288. That means it shoukd not round off rather the after decimal we should have only two numbers. 8.288 should be 8.28 27.4627 should be 27.46
View Replies!
View Related
Round To A Certain Number
I have budgets that I want to round to certain numbers For example: 55% rounds to 75% 45% rounds to 50% 33% rounds to 35% And so on. I have about 7 of them to do, I'm hoping that someone can help with this. I have been struggling all morning on it.
View Replies!
View Related
Round Down Function
=IF((OR(A1"",B1"")),IF(ROUNDDOWN(A1,0)=ROUNDDOWN('Sheet2'!$E$1,0),C1,IF(ROUNDDOWN(B1,0)=ROUNDDOWN('Sheet2'!$E$1,0),C1,""))) All cells in this formula are dates in the format dd-mmm-yyyy h:mm (except C1). The formula works fine only if I type in a date/time like 09-Jun-2007 0:00 in cells A1 or B1. As soon as I revert to an actual date/time eg 09-Jun-2007 9:53, it returns a #VALUE! error. If I reference only one cell eg: =IF(A1"",IF(ROUNDDOWN(A1,0)=ROUNDDOWN('Sheet2'!$E$1,0),C1,""),"") it works fine with or without an actual time.
View Replies!
View Related
Convert Over All To Round
I am trying to 1) covert the overall draft position to the round and pick for a 12 team league and 2) express it in a XX (round).XX(pick) format. For example Example 1 Player X was taken 41st overall. In a draft with 12 teams that works out to the 4th round, 5th pick. This is usually expressed as 4.05. I cannot simply divide 41 by 12 (3.416) and get the intended result in the correct (desired) format of 4.05. Example 2 Player X was taken 95st overall. In a draft with 14 teams that works out to the 7th round, 11th pick. This is usually expressed as 7.11. I cannot simply divide 95 by 14 (6.78) and get the intended result in the correct (desired) format of 4.05.
View Replies!
View Related
Round Based On Value
I am trying to round a dollar amount to two decimal places but the increments aren't consistent. I need to round to .25, .50, .75, or .95. In the attached code I am rounding the number then concatenating to a price book. I have a spreadsheet with several hundred lines and I am hoping to find a cleaner solution ...
View Replies!
View Related
Round The Dates
I have a series of dates that I simply want to round down so they all start on the 1st of each month. The range goes from 01/01/2010 to 03/01/2010, and this continues every month for 4 years. Is there any way I can simply round each date down?
View Replies!
View Related
How To Round Dates Up Or Down
I need to round all dates that are between the 2nd and 15th down to the 1st and all dates greater than the 15th to the 1st of the next month. i.e. 1/3/01 -> 1/1/01 1/14/01 -> 1/1/01 1/16/01 -> 2/1/01 I could use either a formula or macro. I tried using the answers posted, but couldn't get them to work properly. I was able to use those answers, however, and modify them to come up with this: =IF(AND(DAY(A1)>=1,DAY(A1)
View Replies!
View Related
Round To Nearest .05 Or .09
Below is my current formula. Right now I have it rounding the results to the nearest .09. I would also like to have it round to the nearest .05 as well. In other words, to be more exact: I need all numbers that end in 0 or 1 to be rounded down to 9; any numbers that end in 7 or 8 to be rounded up to 9; any numbers that end in 2, 3, or 4 to be rounded up to 5; and 6 to be rounded down to 5. And, of course any numbers ending in 5 or 9 shouldn't change.
View Replies!
View Related
Round The Number UP To The Next Whole
I want to use a formula that will round the number UP to the next whole if the the tenths is .3 or higher and round DOWN if the tenths is .2 or lower. ex. 79.1 --> 79 79.2 --> 79 79.3---> 80 79.4---> 80 79.5---> 80 79.6---> 80 79.7---> 80 79.8---> 80 79.9---> 80 I am thinking this is either not possible or would be an If Then type formula but way beyond my knowledge. The reason I want to do this is becaue if my student has a 79.5 avg for instance I would normally round to 80. However if they are off by just .1 (ex 79.4) i still want to give them the 80 and then I decided to make it within .2 (ex. 79.3.)
View Replies!
View Related
Use IF And ROUND In Same Calculation
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.
View Replies!
View Related
Round The Numbers
I 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
View Replies!
View Related
Round Off A Date
How 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).
View Replies!
View Related
Round Up In Units Of 80!
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.
View Replies!
View Related
Round Up Formula
I 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 Replies!
View Related
Round Up Date..
When a new employee gets a uniform, after they write their first order, I have to make sure the cost of the unifrom gets deducted from their check. Payroll ends on Sunday every week. I want to use conditional formatting to highlight the cell when it's eligible for deduction/reimbursement. No matter what day they write the order, it won't be eligile until after Sunday. Because the day of the week will vary, what type of formula can I use to highlight the cell? My sale dates are in column "AC" and the reimbursement column is "AF".
View Replies!
View Related
Cell To Round Itself..
i 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 Replies!
View Related
Round A Number
Is there a code that turns 4.123 to 4.5, so it rounds .123 to .5 And same for 4.8 a code turns it to 5, so it rounds .5 to 1 and add it to 4 I tried with round, but……………..
View Replies!
View Related
Round To Specified Criteria
How would I write a formula to ROUND as per the following criteria: If number is between .00 and .50 ROUNDUP to nearest .25 or .50 (only those two numbers). If >.50 and less then the next whole number then ROUND to .95 Examples: $3.29 = $3.50 $3.51 = $3.95 $5.99 = $5.95 $6.03 = $6.25 $4.42 = $4.50 $5.09 = $5.25 The only three possible numbers to the right of the decimal are .25, .50 and .95.
View Replies!
View Related
Round Function
I am suspecting that round function is playing a trick on me (N3 = Round(Rnd() * 10, 0) / 10 ). Although sometime it appear to be equal in fact they are not (Reponse = verifie) because of the round function Sub auto_open() Dim N1 As Integer Dim N2 As Integer Dim N3 Dim Reponse As Double Dim Compteur As Integer Dim Score As Integer verifie = 0 Reponse = 0 N1 = 0 N2 = 0 N3 = 0 On Error Goto ErrorHandler Score = 0 Randomize MsgBox "Welcome in the mental calculation program "...............
View Replies!
View Related
Sum Literally :: Round Off
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
View Replies!
View Related
Rounding: Round Up And MRound
Is it possible to combine these two functions, Roundup and MRound? Current formula: =IF(MROUND(E5950-F5950,10)-0.01>E5950,E5950,MROUND(E5950-F5950,10)-0.01) I attempted: =Roundup(IF(MROUND(E5950-F5950,10))-0.01>E5950,E5950,Roundup(MROUND(E5950-F5950,10))-0.01)
View Replies!
View Related
Round To Odd /even In Vba
A 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 Replies!
View Related
Round Up To A Specific Date
I have run into another block wall. Here's my problem: I need to know if there is a formula that will allow me to not only calculate a date from two different cells -- but also to round the answer to THE NEXT 15TH OF THE MONTH beyond that date. Ex: Cell F5 has a purchase date. (1/1/10) Cell F25 indicates a specific number of months. (3) Cell F5 calculates the totals of F5 plus the # of mos. in F25. (4/1/10) But what I need to do - is to have Cell F5 show 4/15/10. (or, if Cell F5 were to come out to be 4/16/10 due to different figures in F5 and F25 -- it would need to indicate 5/15/10, and so on.)
View Replies!
View Related
Round Option Buttons
the code below produces a message box with square command buttons. Anyone know how to make it appear with round option buttons? Selection.Copy ans = MsgBox("Are you sure you want to clear the entire workbook?", vbYesNo, "CLEAR ALL") If ans = vbNo Then Application.CutCopyMode = False Exit Sub End If If ans = vbYes Then
View Replies!
View Related
Round Time To X Minutes
Is there a formua that can roundup the time duration to the nearest fifteen minutes? Eg, if the time duration is 2hr 17 min, can it be rounded to 2hr 15 min? If is 2hr 31 min, to be rounded to 2hr 30 min.
View Replies!
View Related
Formula Round Up The Values
I am using Excel as my payroll spreadsheet A....name B....rate of pay C....date of work D....date of work E...date paid F....# of hours worked G....vacation hours H....Gross Pay....B7*(F7+G7) I....Net Pay....H7-J7-K7-L7-M7-N7 J....FICA....H7*6.2% K....Federal....no formula L....State.....no formula M...Medicare....H7*1.45% N....duductions....no formula O....OT...no formula P....Paid OT....O7*15.75 Q....Blank R....FICA MATCH....J7*2 S....Medicare MATCH....M7*2 (ISN'T ROUNDING UP OR DOWN PROPERLY) For Instance.......Joe worked 19.58 hours at $8.50 = $166.43 gross federal is $0 state os $0 fica is $10.32 medicare is $2.41 R...FICA Match is $20.64 S...Medicare Match is $4.83.....(SHOULD BE $4.82) Sorry but I didn't know how to properly explain the problem that I am having. The problem is in the rounding. Sometimes it doesn't round up and sometimes it doesn't round down.
View Replies!
View Related
Round Up A Range Of Numbers
Is there an easy way to round a range of numbers up? I have a big list of prices and I would like to keep the two digits after the decimal point as .00, but round up the main number. And I would like to do that without writing a formula in every cell. Is this possible?
View Replies!
View Related
Automatically Round Results
I have a worksheet in which I enter figures as whole intergers. Sometimes I want to increase all numbers by 1.5x, which I know how to do. Is there any way to get the results to read only a whole intergers, such as 3 x 1.5 = 4.5, but I'd like that number rounded to 5 (preferably not 5.0).
View Replies!
View Related
Round Len Function
I have this formula which works well, but the 3-len part needs to be dependent on another cells content. Is it possible to have the formula automatically accommodate for this? Eg =ROUND(I36-I37,3-LEN(INT(Q16))) in cell Q16 is a number 0.010 as displayed, and the sum of i36-i37= 0.007 I need the formula to say ok the minimum division size is in multiples of Q16 (0.010) and change the 0.007 to either up or down depending on the calculation. This works currently, but the figure in Q16 can be 0.1 or 0.00001 etc, and the sum in i36-i37 could be anything also as it is a calculated error figure. I need the formula to be able to adapt automatically to the reference DP in Q16. Namely the "3-" part in the formula needs to accomodate for the Q16 number format changing.
View Replies!
View Related
Datedif: Round Up To The Next Intyerval
When using the following formula datedif will calculate completed intervals (years in this case): =DATEDIF(A1,TODAY(),"y") Is it possible to tweak the formula so that it will round up to the next intyerval? i.e. 16 years in stead of 15 years 3 months and 12 days
View Replies!
View Related
Round Function Nearest .5
I'm trying to round some figures UP to the nearest .5, However i only know this formula: =MROUND(P2,0.5) and that rounds to the nearest .5? Is there anything i can use instead to make sure it is always up not down. so 6.13 becomes 6.50 or 7.53 become 8.00? etc.
View Replies!
View Related
Format/ROUND Function
1) My data is $42,397.40 is there a way that I can format for it to read $42.4? I tried ROUND function and that did not work.=ROUND(A1,-3) 2) Cell A5 is Data:1/14/09 - 1/14/09. Since I only wanted the date, I used this formula =RIGHT(sheet1!A5,LEN(sheet1!A5)-FIND("-",Sheet1!A5)) which gave me 1/14/09. I need to set the format to look like 1/14/2009 but the data function would not recognize..
View Replies!
View Related
Round To N Significant Figures
I have been trying to find a way to take the result of a formula, calculate how many decimal places based on significant figures, then rewrite the formula with the Round function. =(5-2)/2 result is 1.5 taken to 3 significant figures is 1.50 =Round((5-2)/2),2) I need to keep the formula in the cell for validation purposes and I need the result truncated to significant figures for accuracy. I am currently using the significant figure formula found on the forum for my data with numbers.
View Replies!
View Related
Round Up To The Highest Number
this is a formular that i used to divide =IF(Z6=TRUE,K6/MID(D6, FIND("1=",D6)+2,LEN(D6)-FIND("1=",D6)-1),K6) the problem i have is that if i have 14 in cell k6 and 1=10 in d6 the answer i get is 1.40 what i need the formular to do is always round it up to the highest number ie 2 not 1.4 example 1=10 20 answer 2 1=10 25 answer 3 1=4 11 answer 3 1=4 20 answer 5
View Replies!
View Related
|
TRACKER
