Power Query Course in Spanish
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 8 of 8

Thread: Help needed with empty cells.

  1. #1
    New Member
    Join Date
    May 2002
    Posts
    10
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I'm using the following to enter values in cell B1 on sheet 2:

    =(Sheet1!A1>Sheet1!A2)*100+(Sheet1!A1=Sheet1!A2)*50

    The problem is, the formula is responding to empty cells. When both cells are empty, the formula is accepting that the cells are equaled and returning a value of one. This is messing up everything. I want the formula to ignore the cell if the cell is empty. What should I do?

    Thanks.

  2. #2
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Austin, Texas USA
    Posts
    11,654
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    =(Sheet1!A1>Sheet1!A2)*100+AND((Sheet1!A1=Sheet1!A2),LEN(Sheet1!A1)>0)*50

  3. #3
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    84,042
    Post Thanks / Like
    Mentioned
    38 Post(s)
    Tagged
    8 Thread(s)

    Default

    On 2002-05-12 14:36, Amortcal wrote:
    I'm using the following to enter values in cell B1 on sheet 2:

    =(Sheet1!A1>Sheet1!A2)*100+(Sheet1!A1=Sheet1!A2)*50

    The problem is, the formula is responding to empty cells. When both cells are empty, the formula is accepting that the cells are equaled and returning a value of one. This is messing up everything. I want the formula to ignore the cell if the cell is empty. What should I do?

    Thanks.
    =(Sheet1!A1>Sheet1!A2)*100+ISNUMBER(A1)*(Sheet1!A1=Sheet1!A2)*50

    would return 0 in case A1 and/or A2 are empty.

    Is that what you would want to have?

    Aladin

  4. #4
    New Member
    Join Date
    May 2002
    Posts
    10
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Aladin thanks...
    You really know your stuff...it works just the way I want it to.

    Thanks again.


  5. #5
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Austin, Texas USA
    Posts
    11,654
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-05-12 14:55, Amortcal wrote:
    Aladin thanks...
    You really know your stuff...it works just the way I want it to.

    Thanks again.

    Just be sure that Sheet1!A1 will never contain a text representation of a numeric value!

  6. #6
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    84,042
    Post Thanks / Like
    Mentioned
    38 Post(s)
    Tagged
    8 Thread(s)

    Default

    it works just the way I want it to.

    Are you sure? I actually attempted to change my last reply into a question by editing it. It seems I didn't succeed. So I'm posing that question now:

    What result do you want to see
    when

    A1 or A2 is blank

    and when

    both A1 and A2 are blank?


    [ This Message was edited by: Aladin Akyurek on 2002-05-12 15:15 ]

  7. #7
    New Member
    Join Date
    May 2002
    Posts
    10
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    It actually worked just the way I wanted it to.

    When A1 is blank I want the result to be zero, and the same when A2 is blank. That is, once a cell is blank the result should be zero.

    Thanks

  8. #8
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    84,042
    Post Thanks / Like
    Mentioned
    38 Post(s)
    Tagged
    8 Thread(s)

    Default

    On 2002-05-12 15:29, Amortcal wrote:
    It actually worked just the way I wanted it to.

    When A1 is blank I want the result to be zero, and the same when A2 is blank. That is, once a cell is blank the result should be zero.

    Thanks
    May I suggest using

    =(COUNT(Sheet1!A1:A2)=2)*((Sheet1!A1>Sheet1!A2)*100+(Sheet1!A1=Sheet1!A2)*50)

    instead.

    Aladin

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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