# 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. ## 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. ## 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}))

3. ## 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. ## Re: Correct formula except show 0 when cell blank.

Thanks for your help guys

5. ## 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.

6. ## Re: Correct formula except show 0 when cell blank.

What result must obtain when D3 is 0 and when D3 is 2?

#### Posting Permissions

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