1. P

    Rounding decimals to the nearest quarter

    I am working on a staff schedule budget. I am trying to calculate based on budget and rate how may hours an employee can work. Here is the catch, while an employee may get a budget of $2889.00 at a rate of $15.00/hr which would give them 192.6 hours. However, since our timesheet system only...
  2. E

    Rounding only if the 3rd decimal place is greater than 5

    Hi everyone, I have a task where I need to round a number up to 2 decimals ONLY IF the third decimal place is greater than 5, otherwise it'd stay as it is. For example, if the figure is $26.24701 then round it to $26.25 If it's $26.24301 then keep the 5 decimals. I've been trying to run an...
  3. A

    specific round figure

    I have a question and I hope to get an answer from you. I have grades for some students and I want to make a round figure for the marks in a special way as follows: If the figure in B1 contains 0.1 the round figure should be same 0.1 If the figure in B1 contains 0.2 the round figure should be...
  4. L

    SUM IF - Round up If - Round Down Else

    Hi all, I need help building out a formula here. If I have a column of data that I am performing a SUMIF on, I would like to incorporate a Rounding calculation to the SUMIF formula. If the sum of the data's decimal point is below .5, round down nearest whole number, if over .5 round up to...
  5. M

    Rounding the months and days in between 2 dates

    I'm calculating number of months and days in between 2 dates (jan1 2019 & Dec 31, 2019) using a formula Value(Datedif(A1,B1,"M")&"."&datedif(a1,b1,"md")+1) and i am getting a result of 11.31 but i wanted to appear as 12 instead (since 31 completes the whole month). I want to get the real...
  6. T

    Rounding currency up at 6 instead of 5

    Hello - I know this sounds strange, but I am looking for a way to condition data in spreadsheet cells to round down at 5 and round up at 6. Example: $2.245 would be $2.24 AND $2.246 would be $2.25. Is this even possible as it seems all of rounding functions in Excel and based on traditional...
  7. S

    Rounding while still seeing ".0"

    Good Morning, I'm trying to use a simple rounding formula but I don't want to lose the ".0" when a number, say 14.96, is rounded up to 15. I cannot format the cell itself. Ideas on how to do this. It's probably super simple but I've been brain farting all afternoon/evening....
  8. T

    Rounding in CONCATENATE

    Hello, I can´t solve the issue with CONCATENATE and rounding in this function like this screen: I need to round it only to 2 numbers after the decimal point (examlpe: 7.13%, not 7.15615615686164489%) Can anyone help me with this function to solve it? PS: Function is...
  9. J

    Asymmetric Rounding "Rounding-Half-Down"

    While I have been able to code a function for Asymmetric Rounding "Rounding-Half-Up" (code attached). But, I am struggling with Asymmetric Rounding "Rounding-Half-Down" method. Public Function RoundHalfUpAsym( _ ByVal myNum As Double, _ ByVal myFac As Double) As Double...
  10. R

    Rounding dates to the next specific date

    I am working on a spreadsheet for monitoring personnel records, especially in relation to the disposal of the records. I current have the spreadsheet which calculates 6 years after an employee left, and which will compare this to today's date to flag they have been gone for 6 years. Our policy...
  11. M

    Roundup within an IF statement after a condition is met.

    Hi Everyone, I'm new to this group and not well trained with Excel, I've spent my morning looking at formulas trying to find the solution. Here is the formula I have written, which works. I need to show a value of 7 if Z2 is less than 0.5, but I need it to roundup Z2 to the next whole number...
  12. S

    Rounding oddity

    The weirdest thing is happening ... In my table, I'm running a quick if/then calc to confirm if the sum of the values in one column equals the sum of values in another column. For some reason I keep getting a negative answer. It turns out there appears to be a rounding issue, as you can see...
  13. JTL9161


    I am doing a sum formula and need some help on rounding. One of my examples: 77.8 (shows as 78) minus 82.5 = -4.7 How do I get it to show a total of 4.5? Because in reality 78 - 82.5 = -4.5 Another one: 109.1 (shows as 109) minus 95.5 = 13.6 but I really need it to say 13.5 Any help...
  14. S

    Rounding a date to the nearest half year

    Hi, I'm looking to create a formula that will round a date to the nearest half year (either the start of the year or the middle of the year depending upon which is next) i.e if the date was 01/08/2019 the formula would round to 01/01/2020 if the date was 03/04/3021 the formula would round to...
  15. I

    Rounding using ceiling with conditions

    I'm trying to round to the nearest quarter hour but if the rounding is less than 1:15 I want the cell to just display 1:15 I've tried =IF(CEILING((B2-A2),"00:15")<"01:15","1:15",CEILING((B2-A2),"00:15")) and it just puts 1:15 is every cell no matter if it's above or below 1:15 Below is an...
  16. R

    Division Formula & ROUNDING UP

    Im running a league and I have x amount of players paying $13.50 a week. I am trying to get a formula set-up for this that ROUNDSUP Example for week #1 the total money collected was $1,374 If I divide that =C9/13.5 by $13.50 it comes up with 101.7777778 Now what I am trying to come up with...
  17. W

    rounding error

    Hi, i'm facing an issue with rounding error and i would like to know if there is a way to solve it. below is an simple example. <colgroup><col style="text-align: center;"><col style="text-align: center;"><col style="text-align: center;"><col span="3" style="text-align...
  18. B

    Round up issue

    HI All, I know it might sounds silly but Im looking to round up or down below formula. I was trying multiple ways and still excel not rounding up this :( IF(K10="Order",((SUM($J5:$U5))/(COUNT($J5:$U5)-COUNTIF($J5:$U5,0)))*X8,0) result in this example was 935 Thank you for any ideas
  19. H

    100% rounding

    Hi I have an audit template where we have a set of scored criteria. each one marked green = 5 points amber = 3 points red = 0 points We add up all the the score, using aggregate(9,6,range) We then calculate a %age grade using a simple divide total by max possible total formula This works...
  20. B

    Rounding off decimals

    How to round off 19 years 2 months to 19 years, 19 years 3 months 20 days to 19 years 3 months, 19 years 6 months 6 days to 19 years 6 moths and 19 years 9 days 20 days to 20 years in excel?

Watch MrExcel Video

This Week's Hot Topics

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back