Adding a fee for not reaching a minimum value without a circular reference

[spongebob]

Hello All,

On an invoice type of spreadsheet we have some rows that get totaled, we can see in my below example that the total is $6.00, however we have a $15.00 minimum
How can I add in the difference in fee ($9) without creating a circular reference?
I have tried a few ways without success.

Sub total	5.00
Tax	1.00
Minimum Fee
Total	6.00

Thanks!

 

[Fluff]

How about

+Fluff v2.xlsm
	A	B
1
2	Sub total	5
3	Tax	1
4	Minimum Fee	15
5	Total	15
Data

Cell Formulas
Range	Formula
B5	B5	=MAX(B4,SUM(B2:B3))

 

[spongebob]

Thank you.
I would like the minimum fee to show 0 when there is no fee, people may think it's been added in to the grand total.

 

[Fluff]

Ok, how about

+Fluff v2.xlsm
	A	B
1
2	Sub total	5
3	Tax	1
4	Minimum Fee	9
5	Total	15
Data

Cell Formulas
Range	Formula
B4	B4	=MAX(0,15-SUM(B2:B3))
B5	B5	=SUM(B2:B4)

 

[kvsrinivasamurthy]

In B4
=IF(SUM(B2:B3)<15,15-SUM(B2:B3),0)

OR
=MAX(0,15-SUM(B2:B3))

 

[spongebob]

Thank you.

It looks like no matter what I do, it's a circular reference.
If I simply make the total have the max function, it will show the correct amount, but it will never have a row reflecting that it's a minimum fee and how much additional that is.

 

[Fluff]

I'm afraid I don't understand, the formulae I showed in post#4 do not involve a circular reference.
If post#4 is not what you are looking for, then please post some sample data of your actual layout, using the XL2BB add-in & include expected results.

 

[spongebob]

I see what is happening.
The SUB is getting the numbers from above, which includes the fee, so visually it's not the same.
And therefore its circular... I have to reorganize that part to be more like the example.

09.10.2020_15.39.38_REC

Recorded with ScreenRec

screenrec.com