MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Greater than / Less Than


Posted by kwb on November 26, 2001 11:16 AM

Any Idea how to turn this into the correct formula??
IF F15 = "250","0",
IF F15 = "500" AND D7 =<"50,000",".95"
IF F15 = "500" AND D7 >"50,000" BUT =< "100,000",".97"
IF F15 = "1000" AND D7 >"100,000", ".98",
IF F15 = "1000" AND D7 =<"50,000",".92"
IF F15 = "1000" AND D7 >"50,000" BUT =< "100,000",".94"
IF F15 = "1000" AND D7 >"100,000", BUT =< "250,000", ".95",
IF F15 = "1000" AND D7 > "250,000",".96"


Posted by John on November 26, 2001 1:17 PM

This will work if F15 is restricted to just the three values (250, 500 or 1000):

=(F15=500)*((D7<=50000)*0.95+((D7>50000)*(D7<=100000))*0.97+(F15=500)*(D7>100000)*0.98)+(F15=1000)*((D7<=50000)*0.92+(D7>50000)*(D7<=100000)*0.94+(D7>100000)*(D7<=250000)*0.95+(D7>250000)*0.96)