Results 1 to 5 of 5
  1. #1
    New Lounger
    Join Date
    Aug 2003
    Posts
    16
    Thanks
    0
    Thanked 0 Times in 0 Posts

    if statement with an 'OR' nested (xp)

    I am trying to create an if statement to do the following: if a certain cell(x) contains the value '0' or text, the if statement should output zero. if those two situations dont occur, then it should divide that cell(x) by another in the worksheet and output that answer. does this make sense? please help at your earliest convenience!

  2. #2
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Vienna, Wien, Austria
    Posts
    5,009
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: if statement with an 'OR' nested (xp)

    For a simplified solution, try something like:

    =IF(ISERROR(D32/A5),0,D32/A5)

    Where D32 is the cell that may be text or zero and A5 is "another in the worksheet". Please note that this will also produce 0 if "another in the worksheet" is either text or zero and D32 turns out to be a valid value.

    If you want something that tests only D32, that can be designed also.

    HTH
    Gre

  3. #3
    3 Star Lounger
    Join Date
    Nov 2002
    Location
    New York, New York, USA
    Posts
    266
    Thanks
    0
    Thanked 19 Times in 19 Posts

    Re: if statement with an 'OR' nested (xp)

    Assuming that you are working with cells A1 and A2 with A2 as denominator try the below formula

    =IF(AND(CELL("Type",A2)="v",A2<>0),A1/A2,0)
    Regards,
    TD

  4. #4
    New Lounger
    Join Date
    Aug 2003
    Posts
    16
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: if statement with an 'OR' nested (xp)

    both worked great. i can follow the first solution somewhat, but the second, i dont understand at all how it works? what is the 'v'? do you mind explaining a little? thanks for your help!

  5. #5
    3 Star Lounger
    Join Date
    Nov 2002
    Location
    New York, New York, USA
    Posts
    266
    Thanks
    0
    Thanked 19 Times in 19 Posts

    Re: if statement with an 'OR' nested (xp)

    The CELLS function in Excel returns information about, formatting, location, or contents of a specific cell or the upper-left cell in a referenced range. It can be a very useful function.

    In the formula provided the info type requested is "Type" for cell A2. This returns the following values. "b" for Blank, "l" for labels and "v" for values. Since you only wanted to deal with values that are non zero the nested AND statement requires that the cell be a value AND that value not be zero for the formula to perform division; in all other cases ("b","l", or the value zero) the formula returns -0- (zero).

    Regards,

    TD

Posting Permissions

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