Invoice Total calculation

[Sirpeterjones]

Hi Team, does someone know the right formula to calculate the invoice total where the pricing is a little complex.

I want to be able to enter the number of containers in the first 2 columns of the green section, then the total invoice amount will automatically calculate.

Where Unit price is per container - The first container is a higher rate, then every subsequent container is a lower rate
Where unit price is per 5 containers - we sell in batches of 5, so single price is per 5 units, $27.50 is for 5 containers, regardless if they use 1 or 5 we only charge $27.50, if they use 6-10 then it's another $27.50
Where unit price is per 10 x 20' - similar to per 5 containers, but each 40' counts as 2 x 20'
Where unit price is per 2 x 20' - similar to above, we price once per 2 x 20' and 1 x 40' = 2 x 20'
Certificate fee is only charged once regardless of the number of containers
Amendment fee is 25% of the total calculated invoice plus $30 of in the case of Cameroon it's a flat $63

Appreciate any help I can get to put this calculation together
Thank you

 

[StephenCrump]

No problem. I was going to say you should check the numbers carefully, because you're the only one that knows how the calculations should work.

I have tried to work with the formula but I can't make it work

It would be great if you could figure it out, so you can maintain it and update it in future ....

In Post #10, we have number of units, K6: =IF(E6="10x20",I6+2*J6,IF(E6="2x20",ROUNDUP(I6/2+J6,0),SUM(I6:J6)/E6))

"For 2x20" units, which is working correctly, we count I6/2 + J6, i.e. 0.5 per 20' unit and 1.0 per 40' unit, and roundup to an integer number of units.

I misinterpreted 10x20 as referring to a unit of 10' x 20' (i.e. 1 x 20'), and therefore counted the number of units as I6+2*J6.

How do you think we should adjust I6+2*J6, e.g. to account for the fact that each I6 is actually 0.1 of a unit, and not a whole unit?

 

[Sirpeterjones]

So I tried this...
I6/0.1+J6/.2
as follows
=IF(E6="10x20",I6/0.1+J6/.2,IF(E6="2x20",ROUNDUP(I6/2+J6,0),SUM(I6:J6)/E6))
But I get an error saying I have entered too few arguments

Am I on the right track?

 

[Sirpeterjones]

So I tried this...
I6/0.1+J6/.2
as follows
=IF(E6="10x20",I6/0.1+J6/.2,IF(E6="2x20",ROUNDUP(I6/2+J6,0),SUM(I6:J6)/E6))
But I get an error saying I have entered too few arguments

Am I on the right track?

I really am still lost… any chance you can help me solve this last piece of the puzzle please?

 

[StephenCrump]

Using the Post #8 layout, try:

K3: =IF(E3="10x20",ROUNDUP(I3/10+J3/5,0),IF(E3="2x20",ROUNDUP(I3/2+J3,0),ROUNDUP(SUM(I3:J3)/E3,0)))

 

[Sirpeterjones]

Works perfectly - Thank you!!

 

[Sirpeterjones]

=IF(P3<1,0,IF(M3=1,N3*C3-(E3>0)*(N3-1)*(C3-E3)+O3*D3-(F3>0)*(O3-1)*(D3-F3),P3*C3-(E3>0)*(P3-1)*(C3-E3)))

Container fee calculation is not working properly.

2 x 20' should be $120 + $75, but for some reason it is charging the first container fee twice, instead of first plus additional

But it's working when I have the 2x20

 

[StephenCrump]

Sorry, L3 should be: =IF(K3<1,0,IF(E3=1,I3*A3-(C3>0)*MAX(0,I3-1)*(A3-C3)+J3*B3-(D3>0)*MAX(0,J3-1)*(B3-D3),K3*A3-(C3>0)*(K3-1)*(A3-C3)))