Correct formula except show 0 when cell blank.

[Stretlow]

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.

 

[jbeaucaire]

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

 

[Trebor76]

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

 

[Stretlow]

Thanks for your help guys

 

[jbeaucaire]

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.

 

[Aladin Akyurek]

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