If Statement Logic Not Working

[CathyY]

This spreadsheet lists the commissions from sales of homes. The broke
needs to pay royalties to the company up to a cap amount. I have name
a cell for gross commissions that will be the running total of th
spreadsheet. I have one column that each cell is trying to have th
logic look at that gross commission cell and if the value is < 150000
then it should take 2% of the commission value in the cell before i
and paste that value in the cell, otherwise if the value is > 15000
then the value in that cell should be 0. I can't seem to get the I
statement to work

 

[rbyrns]

I created a spreadsheet with the following

a1 - Sales price
b1 - Commision paid
b2 - entered the following formula

=IF(SUM(B$1:B1)>150000,0,IF((SUM(B$1:B1)+A2*0.02)>150000,150000-SUM(B$1:B1),A2*0.02))

Pay attention to the absolutes.

The formula will check to make sure the current commission will not go
over the 150000 ceiling. If so it only pays the amount up to the 150000
ceiling.

Also as an idea instead of hardcoding the 150000 set up a cell that
allows you to change the ceiling at a later time. Then substitue this
cell value for the 150000 entry in the formula.

Hope this helps

 

[J.E. McGimpsey]

One way:

Assume Gross Commission cell is B1 (and therefore the "cell before
it" is A1):

= A1 * 2% * (B1<150000)

XL will convert the Boolean TRUE/FALSE to 1/0, respectively.

You don't say what should happen if B1 = 150000, so I assumed your
result should be 0. If not, use

= A1 * 2% * (B1<=150000)

 

[CathyY]

Hello rbyrns, thank you for post. I used your logic, and it effectivel
populates the royalties cell with the 2% of the commission amoun
cell... however... when the gross commissions (sum of commission
cells) reaches 150000, then all cells in the royalties column ar
blanked out... not just the immediate cell. It should look lik
this...
Commission royalty(2%)
$5,000 $100
$50,000 $1,000
$50,000 $1,000
$40,000 $800
$5,000 $100


$150,000 $3,000

After the total of 150000, the royalty cell's should be $ -, so we kno
we've paid 3000 on 150000 worth of commissions.
however, I've realized that this may be trickier than I first thought.
The commissions I've used add up to 150,000 evenly for example sakes..
but not true of the real world. What would I do should 150000 cap b
met in part commission

 

[rbyrns]

Cathy,

I pulled my worksheet back up and verified that the formula in my first
reply is what is in my workbook. My workbook does not clear out the
commission when 150000 threshold is reached. It will stop calculating
commission for anything over 150000 and will only calculat a partial
commission if a sale will go over the 150000.

Please check your formula to make sure that all the absolute values are
locked in and not locked where they should not be.

I have attached the workbook

Attachment filename: iflogic.xls
Download attachment: http://www.excelforum.com/attachment.php?postid=389389

 

[rbyrns]

Cathy,

The third column (Columne C) in the workbook is not required. I used
it to verify what the prior commission total was to debug the formula.