1. 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...
  2. 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...
  3. 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...
  4. 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....
  5. 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...
  6. 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...
  7. 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...
  8. 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...
  9. 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...
  10. 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...
  11. 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...
  12. 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...
  13. 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...
  14. 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...
  15. 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
  16. 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...
  17. 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?
  18. S

    VBA- Rounding a static value to closest increment of a Loop/Array created from another static value

    <tbody> INCREMENT STATIC 1 STATIC 2 VALUE .25 100 100.35 .45 200 201.55 .65 300 302.75 </tbody> I'm struggling to figure out in VBA the most efficient way to create a loop/array within the value in the STATIC 1 column using the increment in same row, and then rounding the STATIC 2...
  19. L

    Rounding up or down depending on...

    When it comes to rounding I use excel like everyone else. I’ve recently come across an engineering specification that rounds up or down to an even decimal place if the second decimal ends in a 5. i.e 2.45 to 2.4, 2.75 to 2.8 and normal rounding occurs otherwise 2.44 to 2.4, 2.74 to 2.7 and 2.53...
  20. S

    Rounding to the following Sunday

    Racking my brain to get the following to work: =IFERROR(IF(D5=0,"",D5+45),IF(D5="","",D5+7-MOD(D5-1,7))) D5 being the source date that I'm adding 45 days to and then rounding to the following Sunday. Honestly wouldn't mind figuring out the previous Sunday as well. We are trying to set...

Some videos you may like

This Week's Hot Topics