MrExcel Publishing
Your One Stop for Excel Tips & Solutions

formula help


Posted by Anne on December 17, 2001 10:45 AM

I am trying to write a formula that would give the solution to the following: if the values in cell B1 to B200 are less than 50$, multiply that value by 200, if the values in cells B1 to B200 are more than 50$ but less than 100$, multiply the value by 150, and if the values in cells B1 to B200 are more than 100$ multiply that value by 140.


Posted by Aladin Akyurek on December 17, 2001 10:56 AM

Anne --


in C1 enter: =IF(B1,(B1 <= 50)*200+(B1>50)*(B1 <=100 )*150+(B1>100)*140,"")

Copy down this as far as needed.

You didn't tell what must happen if the B-value is exacly 50 or exactly 100. I added them to the formula as you can see by the use of <= (less then or equal to).

Aladin

Posted by Aladin Akyurek on December 17, 2001 11:09 AM

=IF(B1,B1*((B1<=50)*200+(B1>50)*(B1<=100)*150+(B1>100)*140),"")

Cheers.

Aladin