+ Reply to Thread
Results 1 to 4 of 4

How to use IF and ROUND in same formula

  1. #1
    Registered User
    Join Date
    07-22-2008
    Location
    WV
    Posts
    70

    Question How to use IF and ROUND in same formula

    I have this formula =IF(((D11-4)^2*C11/16)=0,"",(D11-4)^2*C11/16)

    This works for my purpose, but I want the result to round to the nearest whole number.

    I have tried variations of =IF(((D11-4)^2*C11/16)=0,"",ROUND(D11-4)^2*C11/16), 0)) bit can't seem to get anything to work. I'm sure I'm missing something simple here.

    Of if there would be a better formula to achieve my results without an error in the cell altogether, any help and suggestions would be much appreciated.

    Thanks

  2. #2
    Forum Expert Ron Coderre's Avatar
    Join Date
    03-22-2005
    Location
    Boston, Massachusetts
    MS-Off Ver
    2013, 2016, O365
    Posts
    6,996

    Re: How to use IF and ROUND in same formula

    Does this work for you?
    Please Login or Register  to view this content.
    Ron
    Former Microsoft MVP - Excel (2006 - 2015)
    Click here to see the Forum Rules

  3. #3
    Forum Expert
    Join Date
    05-01-2014
    Location
    California, US
    MS-Off Ver
    Excel 2010
    Posts
    1,795

    Re: How to use IF and ROUND in same formula

    Although you could write:

    =IF((D11-4)^2*C11/16 = 0, "", ROUND((D11-4)^2*C11/16, 0))

    I wonder if that truly gives you what you expect.

    On the left, the unrounded expression is exactly zero only when D11=4 or C11=0.

    But on the right, the rounded expression could be zero when the unrounded expression is not. For example, if the unrounded expression is 0.004, the rounded expression is zero.

    If your intent is not to return zero, you might be happier with:

    =IF(ROUND((D11-4)^2*C11/16, 0) = 0, "", ROUND((D11-4)^2*C11/16, 0))

  4. #4
    Forum Guru
    Join Date
    02-27-2016
    Location
    Vietnam
    MS-Off Ver
    2024
    Posts
    6,175

    Re: How to use IF and ROUND in same formula

    Or try:

    =IFERROR(1/(1/ ROUND((D11-4)^2*C11/16, 0)),"")

+ 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] 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
  2. Replies: 1
    Last Post: 09-04-2015, 04:06 PM
  3. Replies: 2
    Last Post: 12-06-2014, 01:58 PM
  4. [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
  5. round up round down formula
    By m_789 in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 04-04-2014, 05:34 AM
  6. [SOLVED] How do i round up the sum of a formula
    By ian in forum Excel General
    Replies: 2
    Last Post: 05-27-2006, 11:50 PM
  7. 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