Results 1 to 6 of 6

Correct formula except show 0 when cell blank.

This is a discussion on Correct formula except show 0 when cell blank. within the Excel Questions forums, part of the Question Forums category; Hi all. A little help if you dont mind. I have this =LOOKUP(D3,{0,6,12,24,36,48,49},{1000,600,400,200,100,0}) to do some scoring However when the ...

  1. #1
    Board Regular
    Join Date
    Dec 2008
    Posts
    116

    Default Correct formula except show 0 when cell blank.

    Hi all.

    A little help if you dont mind.


    I have this

    =LOOKUP(D3,{0,6,12,24,36,48,49},{1000,600,400,200,100,0})

    to do some scoring

    However when the cell is blank (D3) ie no data in there i need the score to show 0 at the moment it shows 1000

    I cant for the life of me get it right.

    Any help greatly appreciated.


    Stret.

  2. #2
    Board Regular jbeaucaire's Avatar
    Join Date
    May 2002
    Location
    Bakersfield, CA
    Posts
    5,772

    Default Re: Correct formula except show 0 when cell blank.

    You'll have to add a preliminary test before allowing the LOOKUP() to run.

    =IF(ISBLANK(D3), 0, LOOKUP(D3,{0,6,12,24,36,48,49},{1000,600,400,200,100,0}))
    Microsoft MVP 2010 - Excel
    Jerry Beaucaire's Excel Tools

    "Actually I *am* a rocket scientist." -- JB

  3. #3
    Board Regular
    Join Date
    Jul 2007
    Location
    Sydney
    Posts
    2,312

    Default Re: Correct formula except show 0 when cell blank.

    Or you can use this:
    =LOOKUP(D3,{0,6,12,24,36,48,49},{0,1000,600,400,200,100,0})

  4. #4
    Board Regular
    Join Date
    Dec 2008
    Posts
    116

    Default Re: Correct formula except show 0 when cell blank.

    Thanks for your help guys

  5. #5
    Board Regular jbeaucaire's Avatar
    Join Date
    May 2002
    Location
    Bakersfield, CA
    Posts
    5,772

    Default Re: Correct formula except show 0 when cell blank.

    I'm certain that blank and zero are not the same thing. The original formula appears to give a value of 1000 to scores of zero. To keep the same thing from happening for a blank cell, you would need to test if the cell is blank or not, else you'll always get that 1000 score.
    Microsoft MVP 2010 - Excel
    Jerry Beaucaire's Excel Tools

    "Actually I *am* a rocket scientist." -- JB

  6. #6
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    64,669

    Default Re: Correct formula except show 0 when cell blank.

    What result must obtain when D3 is 0 and when D3 is 2?
    Assuming too much and qualifying too much are two faces of the same problem.

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
  •  


DMCA.com