MrExcel Publishing
Your One Stop for Excel Tips & Solutions

default to specific value


Posted by Mike on July 30, 2001 3:14 PM

What is the formula to return this result

if columnA >= 85 then columnB is 1
if columnA >= 80 but < 85 then columnB is 0.75
if columnA >= 75 but < 80 then columnB is 0.50
if columnA <75 then columnB is 0


Posted by IML on July 30, 2001 3:24 PM

Try using Vlookup as follows:

=VLOOKUP(SUM(A:A),{0,0;75,0.5;80,0.75;85,1},2)

Good luck

Posted by Aladin Akyurek on July 30, 2001 3:24 PM

=VLOOKUP(A1,{0,0;75,0.5;80,0.75;85,1},2)

Posted by Mark W. on July 30, 2001 3:43 PM

Or, perhaps... : )

=VLOOKUP(A1,{-9.99999999999999E307,0;75,0.5;80,0.75;85,1},2)