MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Sales??


Posted by Jon on September 19, 2001 12:38 PM

Problem. 3 price breaks.....

below $5000 60%
between $5000 and $10,000 55%
above $10,000 50%

I need to figure the percent of a cell if that cell is in the above range....
Example $5500 formula result 55% of $5500
or $12,000 formula result 50% of $12,000


Posted by Aladin Akyurek on September 19, 2001 12:48 PM

Jon,

Try this Boolean formulation:

=(A1<5000)*(A1*60%)+(A1>=5000)*(A1<=10000)*(A1*55%)+(A1>10000)*(A1*50%)

Copy down as needed.

Aladin

=======

Posted by IML on September 19, 2001 1:09 PM

Same idea as Aladin...
=+A1*0.5+(A1 < 10000)*0.05*A1+(A1 < 5000)*0.05*A1
good luck

Posted by Larry Bruce on September 19, 2001 5:10 PM

Jon,

Here's yet another way:

=IF(A1>10000,.5*A1,IF(A1>4999,.55*A1,.6*A1))

/s/ Larry

Posted by Aladin Akyurek on September 19, 2001 10:29 PM

> Here's yet another way:

> =IF(A1>10000,.5*A1,IF(A1>4999,.55*A1,.6*A1))

Larry --

That's right. My motive for going boolean is that such a formula evaluates faster than the IF-equivalent. The difference should be noticeable on very large sets of data.

Regards.

Aladin