+ Reply to Thread
Results 1 to 7 of 7

if function with error checking

  1. 02-28-2010, 11:36 PM #1
    smalltime
    smalltime is offline
    Registered User
    Join Date
    08-06-2009
    Location
    Japan
    MS-Off Ver
    Excel 2003
    Posts
    32

    Red face if function with error checking

    Hello

    My file looks like this:

    Column
    A - number of pieces (manual input)
    B - x dimension of area (manual input)
    C - y dimension of area (manual input)
    D - area based on B and C (formula = B*C)
    E - irregular area (manual input, if area is not rectangle)

    F- solve for density number (pcs/area)

    Please Login or Register  to view this content.
    Please Login or Register  to view this content.
    TIA
    Last edited by smalltime; 02-28-2010 at 11:38 PM. Reason: correct code
    Reply With QuoteRegister To Reply
  2. 02-28-2010, 11:47 PM #2
    JBeaucaire
    JBeaucaire is offline
    Administrator JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: help- a simple if function with error checking

    Maybe:

    You haven't presented enough information to resolve this formula issue..."it doesn't work" is pretty unhelpful:
    =IF(AA27="",H27/AB27,IF(ISERROR(H27/AA27),"",(H27/AA27)))


    This formula should also work, even when B or C is blank.
    =IF(D1=0,A1/E1,IF(ISERROR(A1/D1),"",(A1/D1)))


    There must be something else going on you have not presented. Click GO ADVANCED and use the paperclip icon to post up your workbook, let us see what else might be going on.
    _________________
    Microsoft MVP 2010 - Excel
    Visit: Jerry Beaucaire's Excel Files & Macros

    If you've been given good help, use the icon below to give reputation feedback, it is appreciated.
    Always put your code between code tags. [CODE] your code here [/CODE]

    “None of us is as good as all of us” - Ray Kroc
    “Actually, I *am* a rocket scientist.” - JB (little ones count!)
    Reply With QuoteRegister To Reply
  3. 03-01-2010, 01:39 AM #3
    smalltime
    smalltime is offline
    Registered User
    Join Date
    08-06-2009
    Location
    Japan
    MS-Off Ver
    Excel 2003
    Posts
    32

    Re: if function with error checking

    thanks JBeaucaire,

    see attached.. Cell F6 should be zero or blank.

    I think i have to add column A to the IF condition.

    thanks
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  4. 03-01-2010, 02:57 AM #4
    smalltime
    smalltime is offline
    Registered User
    Join Date
    08-06-2009
    Location
    Japan
    MS-Off Ver
    Excel 2003
    Posts
    32

    Re: if function with error checking

    i already have a solution.
    Feel free to share if you have a better one
    thanks

    Please Login or Register  to view this content.
    Reply With QuoteRegister To Reply
  5. 03-01-2010, 02:58 AM #5
    Jbentley
    Jbentley is offline
    Forum Contributor
    Join Date
    04-03-2007
    Location
    Auckland, New Zealand
    MS-Off Ver
    2007
    Posts
    137

    Re: if function with error checking

    Hi,
    Looking at your attachment, you formula is doing exactly what it should.
    I think the problem my be that you do not have anything to treat an error where you are D6 = 0 AND E6 is empty or equals zero.

    Not sure what you want to happen where both D6 and E6 are empty or zero...
    Reply With QuoteRegister To Reply
  6. 03-01-2010, 03:05 AM #6
    Jbentley
    Jbentley is offline
    Forum Contributor
    Join Date
    04-03-2007
    Location
    Auckland, New Zealand
    MS-Off Ver
    2007
    Posts
    137

    Re: if function with error checking

    Oops, sorry didn't see you had found a solution.

    Your solution works fine if there is nothing in cell A2, but will not work if you have a number in cell A2.

    Try replacing your 
    Please Login or Register  to view this content.
    with 

    Please Login or Register  to view this content.
    This will test if the Length of any entry in cell A2 is 0, and if is zero then ""
    Reply With QuoteRegister To Reply
  7. 03-02-2010, 01:58 AM #7
    smalltime
    smalltime is offline
    Registered User
    Join Date
    08-06-2009
    Location
    Japan
    MS-Off Ver
    Excel 2003
    Posts
    32

    Re: if function with error checking

    thanks Jbentley

    your solution is better
    more power

    edit: i found the best solution according to my needs:

    Please Login or Register  to view this content.
    Last edited by smalltime; 03-02-2010 at 03:20 AM.
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1