Excel if formula

[sabelow]

I have a row in excel that calculates commissions. Once the total gets to $8,000 (B6 in the below example), I want the formula to no longer calculate commission. Commission should be zero.
What formula do I put into the B column? Whenever I do an IF formula, I get a circular reference because I am referring to B6 in the formula (which is summing all of B column)


[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD][/TD]
[TD]20%[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Commision[/TD]
[TD]Cap[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]5,000[/TD]
[TD]=A3*B1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]3,000[/TD]
[TD]=A4*B1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD][/TD]
[TD]=SUM(B2:B5)[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[Michael M]

Do you mean like this

=IF(A6>=8000,0,SUM(B2:B5))

 

[sabelow]

Thanks for the suggestion!

No, I need something more like this....=IF(B6<=8000,A3*B$1,0).
The issue is that since B6 is the sum of all the B formulas, I am getting a circular reference and it doesn't calculate.
So the current formula I have in the B column is =A3*B1. Which is the commission * 20%. But once I have paid up to $8,000 in the "CAP" column, I no longer need to pay 20% commission.
So once the "B6" cell equals $8000 or more, the formula should no longer calculate a 20% rate. It should be zero or blank.

 

[Michael M]

you can't use a formula to refer to a cell that the formula resides in !!
you will have to put the formula in an adjacent column.

 

[MB110]

It seems like this should work in B6.

=IF(SUM(B2:B5)>=8000,0,SUM(B2:B5))

 

[jtakw]

Hi,

Think this is what you're looking for.

B3 formula copied down to B5.

Book1
	A	B
1		20%
2	Commision	Cap
3	5,000	1000
4	3,000	600
5	999	0
6	Total	1600
Sheet654

Cell Formulas
Range	Formula
B3	=IF(SUM(A$3:A3)<=8000,A3*B$1,0)
B6	=SUM(B3:B5)