Formual Assistance (Circular Ref)

[rudebwoyrohan]

I need a formula in B7 to pick the lesser of B5 and B6. I keep getting a circular reference. Please assist if you can. Thank you.

[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Rate 1[/TD]
[TD]0.57[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Rate 2[/TD]
[TD]0.75[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Rate 3[/TD]
[TD]200[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Rate 4[/TD]
[TD]0.001[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]Cap[/TD]
[TD]=ROUND(IF(ROUND(B3*B4,6)>B8*30%,B8*30%,ROUND(B3*B4,6)),6)[/TD]
[TD][TABLE="width: 378"]
<tbody>[TR]
[TD]B3 x B4 cannot be >30% of B8 so capped at 30% of B8[/TD]
[/TR]
</tbody><colgroup><col></colgroup>[/TABLE]
[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]Calculated Fee[/TD]
[TD]=B3*B4[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]Admin Fee[/TD]
[TD][/TD]
[TD][TABLE="width: 378"]
<tbody>[TR]
[TD]This should be the lesser of B5 and B6[/TD]
[/TR]
</tbody><colgroup><col></colgroup>[/TABLE]
[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]Rate A[/TD]
[TD]=B1-B7[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]Rate B[/TD]
[TD][TABLE="width: 438"]
<tbody>[TR]
[TD]=ROUND(B8/B2,6)[/TD]
[/TR]
</tbody><colgroup><col></colgroup>[/TABLE]
[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[Scott T]

Is this what you need? Remove space around the < in formula.

=IF(B5 < B6,B5,B6)

 

[Eric W]

Try:

=MIN(B5, B6)

You're getting the circular reference warning since B8 references B7, which references B5, which references B8.

 

[Iceman3395]

Here is a solution. If you need to hide the cell that has the calculation in C3XC4 then do the calculation in an another column by itself and hide the column or you can also put it in a separate worksheet in your workbook. Please note the column letters have changed from what you described above.

[TABLE="width: 693"]
<colgroup><col><col><col span="2"><col></colgroup><tbody>[TR]
[TD]A
[/TD]
[TD] B
[/TD]
[TD] C
[/TD]
[TD] D
[/TD]
[TD] E
[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Rate 1[/TD]
[TD="align: right"]0.57[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Rate 2[/TD]
[TD="align: right"]0.75[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Rate 3[/TD]
[TD="align: right"]200[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Rate 4[/TD]
[TD="align: right"]0.001[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]Cap[/TD]
[TD="align: right"]0.018[/TD]
[TD] [/TD]
[TD]C3 x C4 cannot be >30% of C8 so capped at 30% of C8[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]Calculated Fee[/TD]
[TD="align: right"]0.2[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]Admin Fee[/TD]
[TD="align: right"]0.2[/TD]
[TD] [/TD]
[TD]This should be the lesser of C5 and C6
[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]Rate A[/TD]
[TD="align: right"]0.06
[/TD]
[TD] 0.2
[/TD]
[TD]D8 is the C3 X C4 calculation in your formula in C8 is referred to in C5 creating circular reference, Therefore you need to do the C3 X C4 calculation in a separate cell solved the circular reference.
[/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]Rate B[/TD]
[TD="align: right"]0.08[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]