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 =&LT;"50,000",".95"
IF F15 = "500" AND D7 >"50,000" BUT =&LT; "100,000",".97"
IF F15 = "1000" AND D7 >"100,000", ".98",
IF F15 = "1000" AND D7 =&LT;"50,000",".92"
IF F15 = "1000" AND D7 >"50,000" BUT =&LT; "100,000",".94"
IF F15 = "1000" AND D7 >"100,000", BUT =&LT; "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&LT;=50000)*0.95+((D7>50000)*(D7&LT;=100000))*0.97+(F15=500)*(D7>100000)*0.98)+(F15=1000)*((D7&LT;=50000)*0.92+(D7>50000)*(D7&LT;=100000)*0.94+(D7>100000)*(D7&LT;=250000)*0.95+(D7>250000)*0.96)