+ Reply to Thread
Results 1 to 2 of 2

Round down without affecting the value

  1. #1
    Registered User
    Join Date
    12-10-2020
    Location
    England
    MS-Off Ver
    2019
    Posts
    1

    Round down without affecting the value

    I have a sheet where I calculate someone's age exactly using YEARFRAC and pass this to various other formulae. I want to force this to round down without affecting the value (since this adds a rounding error to later calucluations) - if you are 20.6 years old, you would say you were 20 not 21 so this is what I want to see. Is there any way I can do this as ROUNDDOWN and DATEDIF both return integer values only and formatting would round this up?

  2. #2
    Forum Contributor
    Join Date
    08-12-2020
    Location
    Atlanta, Georgia
    MS-Off Ver
    Office 16
    Posts
    241

    Re: Round down without affecting the value

    Hi DavidS089,
    Without knowing the nature of your spreadsheet, what you can do is take the cell that has the computed age in it, and move it to a column or place that you can hide. Then, where that cell was originally, put in your rounding formula. The downstream cells that depended on the original formula will still compute the same because they will still refer to the original formula, and the cell with the new formula will display your round down result for visual purposes.
    Another way to do it would be to take the current formula, let's say it is in A1, take the formula and copy that into all of the downstream formulas that refer to A1. In other words replace A1 in all other formulas with the formula A1 contains so it no longer links to A1. Make sure every cell that refers to A1 is changed in this manner, then change the formula in A1 to have your round down formula.
    Example. Cell A1 is =year(today()-birthday), if cell d5 has a formula =A1+R1, you will change that formula to =year(today()-birthday)+R1.

    Squeaky.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] How to PRESENT round-down, but not perform round-down function?
    By superlative in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 12-02-2019, 12:06 PM
  2. [SOLVED] Custom formulas that will round up when a condition is met or round down
    By cinstanl in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 06-20-2016, 01:32 PM
  3. Replies: 5
    Last Post: 01-30-2015, 11:34 AM
  4. Replies: 2
    Last Post: 12-06-2014, 01:58 PM
  5. [SOLVED] How to round to nearest .01 and round down if thousands place is .005
    By dredre609 in forum Excel General
    Replies: 5
    Last Post: 09-29-2014, 11:47 AM
  6. [SOLVED] Conditional Round up or Round up Function
    By cdmterence in forum Excel Programming / VBA / Macros
    Replies: 17
    Last Post: 09-18-2013, 06:03 PM
  7. [SOLVED] How do I ROUND() round off decimals of a column dataset?
    By Højrup in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 01-12-2005, 07:06 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1