Invoice Total calculation

Sirpeterjones · Jan 22, 2025

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 · Jan 22, 2025

A bit of a guess (it would help if you showed your required results) ...

Sirpeterjones said:
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'

Can you please explain these two in more detail?

1. For Cameroon, does this mean each 20' is $61, and each 40' is $122?
2. For Congo, does this mean $110 for the first 20'/ first 2 x 20' / first 40'? And $86 for each subsequent 40' unit (or part thereof). So that, for example, 3 x 20' or 4 x 20' would both cost $196?

A

B

C

D

E

F

G

H

I

J

K

L

M

1

Cert

Amendment

Container

Total

2

First 20

First 40

Extra 20

Extra 40

Unit

Fee

Fee 1

Fee 2

No 20

No 40

Fee

Invoice

Amendment

3

$120.00

$175.00

$75.00

$130.00

1

$95.00

$30.00

25%

3

4

$835.00

$1,280.00

$350.00

4

$27.50

5

$95.00

$30.00

25%

6

3

$82.50

$276.67

$99.17

5

$61.00

1

$95.00

$63.00

2

4

$366.00

$524.00

$63.00

Sheet1

Cell Formulas
Range	Formula
K3:K5	K3	=IF(E3=1,I3A3-(C3>0)(I3-1)(A3-C3)+J3B3-(D3>0)(J3-1)(B3-D3),CEILING(I3,E3)/E3A3+CEILING(J3,E3)/E3B3)
L3:L5	L3	=(K3+SUM(F3:G3))/(1-H3)
M3:M5	M3	=H3*L3+G3

Sirpeterjones · Jan 22, 2025

Hi Stephen,
You have pretty much nailed it - it's just for row 4 the container fee should be $55 since there are a total of 9 containers, so 2 x multiples of 5.

Here are the desired results
For the last one - the first 2 x 20' equivalent units is $110, each multiple of 2 thereafter is $86

I have added the yellow columns to try show the combination total of 20' and 40'

I tried to play with your formula, but I couldn't get it to work.

StephenCrump · Jan 23, 2025

Ok thanks, here's my latest take ....

A

B

C

D

E

F

G

H

I

J

K

L

M

N

1

First 20

Extra 20

Cert

Amendment

Container

Total

2

or first unit

First 40

or extra unit

Extra 40

Unit

Fee

Fee 1

Fee 2

No 20

No 40

No Units

Fee

Invoice

Amendment

3

$120.00

$175.00

$75.00

$130.00

1

$95.00

$30.00

25%

3

4

4

$27.50

5

$95.00

$30.00

25%

6

3

5

$27.50

$25.00

5

$95.00

$30.00

25%

6

3

6

$61.00

10x20

$95.00

$63.00

2

22

7

$61.00

$51.00

10x20

$95.00

$63.00

2

22

8

$110.00

2x20

$95.00

$30.00

25%

3

8

9

$110.00

$86.00

2x20

$95.00

$30.00

25%

2

8

Sheet1

Cell Formulas
Range	Formula
K3:K5	K3	=IF(E3="10x20",I3+2*J3,IF(E3="2x20",ROUNDUP(I3/2+J3,0),ROUNDUP(SUM(I3:J3)/E3,0)))
L3:L9	L3	=IF(E3=1,I3A3-(C3>0)(I3-1)(A3-C3)+J3B3-(D3>0)(J3-1)(B3-D3),K3A3-(C3>0)(K3-1)*(A3-C3))
M3:M9	M3	=(L3+SUM(F3:G3))/(1-H3)
N3:N9	N3	=H3*M3+G3
K6	K6	=IF(E6="10x20",I6+2*J6,IF(E6="2x20",ROUNDUP(I6/2+J6,0),SUM(I6:J6)/E6))
K7:K9	K7	=IF(E7="10x20",I7+2*J7,IF(E7="2x20",ROUNDUP(I7/2+J7,0),""))

I'm grossing up the invoice differently to you, on the assumption (perhaps incorrect) that, for the first line

Total Invoice = $1,280 = Container Fee ($835) plus Cert Fee ($95) plus Amendment ($30 +25% of Total Invoice = $350)

It would be really helpful if you updated your Account details so we know what version of Excel you're using? You haven't replied to similar queries in your other threads, so we still don't know whether an Excel 365 solution would work for you.

Sirpeterjones · Jan 23, 2025

StephenCrump said:
Ok thanks, here's my latest take ....

A B C D E F G H I J K L M N
1 First 20 Extra 20 Cert Amendment Amendment Container Total
2 or first unit First 40 or extra unit Extra 40 Unit Fee Fee 1 Fee 2 No 20 No 40 No Units Fee Invoice Amendment
3 $120.00 $175.00 $75.00 $130.00 1 $95.00 $30.00 25% 3 4 7 $835.00 $1,280.00 $350.00
4 $27.50 5 $95.00 $30.00 25% 6 3 2 $55.00 $240.00 $90.00
5 $27.50 $25.00 5 $95.00 $30.00 25% 6 3 2 $52.50 $236.67 $89.17
6 $61.00 10x20 $95.00 $63.00 2 22 46 $2,806.00 $2,964.00 $63.00
7 $61.00 $51.00 10x20 $95.00 $63.00 2 22 46 $2,356.00 $2,514.00 $63.00
8 $110.00 2x20 $95.00 $30.00 25% 3 8 10 $1,100.00 $1,633.33 $438.33
9 $110.00 $86.00 2x20 $95.00 $30.00 25% 2 8 9 $798.00 $1,230.67 $337.67
Sheet1
Cell Formulas
Range Formula
K3:K5 K3 =IF(E3="10x20",I3+2*J3,IF(E3="2x20",ROUNDUP(I3/2+J3,0),ROUNDUP(SUM(I3:J3)/E3,0)))
L3:L9 L3 =IF(E3=1,I3*A3-(C3>0)*(I3-1)*(A3-C3)+J3*B3-(D3>0)*(J3-1)*(B3-D3),K3*A3-(C3>0)*(K3-1)*(A3-C3))
M3:M9 M3 =(L3+SUM(F3:G3))/(1-H3)
N3:N9 N3 =H3*M3+G3
K6 K6 =IF(E6="10x20",I6+2*J6,IF(E6="2x20",ROUNDUP(I6/2+J6,0),SUM(I6:J6)/E6))
K7:K9 K7 =IF(E7="10x20",I7+2*J7,IF(E7="2x20",ROUNDUP(I7/2+J7,0),""))

I'm grossing up the invoice differently to you, on the assumption (perhaps incorrect) that, for the first line

Total Invoice = $1,280 = Container Fee ($835) plus Cert Fee ($95) plus Amendment ($30 +25% of Total Invoice = $350)

It would be really helpful if you updated your Account details so we know what version of Excel you're using? You haven't replied to similar queries in your other threads, so we still don't know whether an Excel 365 solution would work for you.

Account details updated - I am using Office 365

Amendment fee does not apply to Total Invoice - this only applies if an Amendment is required and hence calculated separately

I have the calculations working properly now - Thank you!

Last question... How do I make Total Invoice and Amendment read as ZERO when there are no containers? See highlighted below in Yellow

StephenCrump · Jan 23, 2025

Great, I'm glad it's working for you, and thanks for clarifying the version, and the calculation.

Sirpeterjones said:
Last question...

Like this perhaps:

F

G

H

I

J

K

L

M

N

1

Cert

Amendment

Container

Total

2

Fee

Fee 1

Fee 2

Fee

Invoice

Amendment

3

$95.00

$30.00

25%

$835.00

$930.00

$262.50

4

$95.00

$30.00

25%

$90.00

$185.00

$76.25

5

$95.00

$30.00

25%

$55.00

$150.00

$67.50

6

$95.00

$30.00

25%

$52.50

$147.50

$66.88

7

$95.00

$63.00

$2,806.00

$2,901.00

$63.00

8

$95.00

$63.00

$0.00

ZERO

9

$95.00

$30.00

25%

$798.00

$893.00

$253.25

10

$95.00

$30.00

25%

$24.00

$119.00

$59.75

Sheet1

Cell Formulas
Range	Formula
M3:M10	M3	=IF(L3=0,"ZERO",L3+F3)
N3:N10	N3	=IF(L3=0,"ZERO",H3*M3+G3)

Sirpeterjones · Jan 23, 2025

StephenCrump said:
Great, I'm glad it's working for you, and thanks for clarifying the version, and the calculation.

Like this perhaps:

F G H I J K L M N
1 Cert Amendment Amendment Container Total
2 Fee Fee 1 Fee 2 Fee Invoice Amendment
3 $95.00 $30.00 25% $835.00 $930.00 $262.50
4 $95.00 $30.00 25% $90.00 $185.00 $76.25
5 $95.00 $30.00 25% $55.00 $150.00 $67.50
6 $95.00 $30.00 25% $52.50 $147.50 $66.88
7 $95.00 $63.00 $2,806.00 $2,901.00 $63.00
8 $95.00 $63.00 $0.00 ZERO ZERO
9 $95.00 $30.00 25% $798.00 $893.00 $253.25
10 $95.00 $30.00 25% $24.00 $119.00 $59.75
Sheet1
Cell Formulas
Range Formula
M3:M10 M3 =IF(L3=0,"ZERO",L3+F3)
N3:N10 N3 =IF(L3=0,"ZERO",H3*M3+G3)

The issue I have is that L3 does not equal ZERO, using the formula provided, see row 4 below highlighted in yellow - despite "Number of 20'" and "Number of 40'" being 0 - it's showing $90, I don't know where this $90 is coming from?

StephenCrump · Jan 23, 2025

Sorry, my mistake - didn't check exhaustively. If there are zero containers, and a non-zero "extra" price, my formula was taking 1 x First price + (-1) x Extra Price.

Try:

A

B

C

D

E

F

G

H

I

J

K

L

M

N

1

First 20

Extra 20

Cert

Amendment

Container

Total

2

or first unit

First 40

or extra unit

Extra 40

Unit

Fee

Fee 1

Fee 2

No 20

No 40

No Units

Fee

Invoice

Amendment

3

$120.00

$175.00

$75.00

$130.00

1

$95.00

$30.00

25%

3

4

4

$120.00

$175.00

$75.00

$130.00

1

$95.00

$30.00

25%

0

$0.00

ZERO

5

$27.50

5

$95.00

$30.00

25%

6

3

6

$27.50

$25.00

5

$95.00

$30.00

25%

6

3

7

$61.00

10x20

$95.00

$63.00

2

22

8

$61.00

$51.00

10x20

$95.00

$63.00

0

$0.00

ZERO

9

$110.00

$86.00

2x20

$95.00

$30.00

25%

2

8

10

$110.00

$86.00

2x20

$95.00

$30.00

25%

0

$0.00

ZERO

Sheet1

Cell Formulas
Range	Formula
K3:K6	K3	=IF(E3="10x20",I3+2*J3,IF(E3="2x20",ROUNDUP(I3/2+J3,0),ROUNDUP(SUM(I3:J3)/E3,0)))
L3:L10	L3	=IF(K3<1,0,IF(E3=1,I3A3-(C3>0)(I3-1)(A3-C3)+J3B3-(D3>0)(J3-1)(B3-D3),K3A3-(C3>0)(K3-1)*(A3-C3)))
M3:M10	M3	=IF(L3=0,"ZERO",L3+F3)
N3:N10	N3	=IF(L3=0,"ZERO",H3*M3+G3)
K7	K7	=IF(E7="10x20",I7+2*J7,IF(E7="2x20",ROUNDUP(I7/2+J7,0),SUM(I7:J7)/E7))
K8:K10	K8	=IF(E8="10x20",I8+2*J8,IF(E8="2x20",ROUNDUP(I8/2+J8,0),""))

I don't know if it's an option for you, but I'd be tempted to use a VBA function when calculations reach around this level of complexity - especially if there's potential for other fee arrangements to be added. I find it easier to debug, amend and add new cases in something that looks like this:

VBA Code:

Function ContainerFee(Units As String, ... etc ) As Double
    
    Select Case Units
    
    Case "Per container"
        ContainerFee = IIf(No20 > 0, First20 + (No20 - 1) * Extra20, 0) + IIf(No40 > 0, First40 + (No40 - 1) * Extra40, 0)
    
    'Case ....
    
    
    'Case ....
    
    
    End Select

End Function

Sirpeterjones · Jan 23, 2025

StephenCrump said:
Sorry, my mistake - didn't check exhaustively. If there are zero containers, and a non-zero "extra" price, my formula was taking 1 x First price + (-1) x Extra Price.

Try:

A B C D E F G H I J K L M N
1 First 20 Extra 20 Cert Amendment Amendment Container Total
2 or first unit First 40 or extra unit Extra 40 Unit Fee Fee 1 Fee 2 No 20 No 40 No Units Fee Invoice Amendment
3 $120.00 $175.00 $75.00 $130.00 1 $95.00 $30.00 25% 3 4 7 $835.00 $930.00 $262.50
4 $120.00 $175.00 $75.00 $130.00 1 $95.00 $30.00 25% 0 $0.00 ZERO ZERO
5 $27.50 5 $95.00 $30.00 25% 6 3 2 $55.00 $150.00 $67.50
6 $27.50 $25.00 5 $95.00 $30.00 25% 6 3 2 $52.50 $147.50 $66.88
7 $61.00 10x20 $95.00 $63.00 2 22 46 $2,806.00 $2,901.00 $63.00
8 $61.00 $51.00 10x20 $95.00 $63.00 0 $0.00 ZERO ZERO
9 $110.00 $86.00 2x20 $95.00 $30.00 25% 2 8 9 $798.00 $893.00 $253.25
10 $110.00 $86.00 2x20 $95.00 $30.00 25% 0 $0.00 ZERO ZERO
Sheet1
Cell Formulas
Range Formula
K3:K6 K3 =IF(E3="10x20",I3+2*J3,IF(E3="2x20",ROUNDUP(I3/2+J3,0),ROUNDUP(SUM(I3:J3)/E3,0)))
L3:L10 L3 =IF(K3<1,0,IF(E3=1,I3*A3-(C3>0)*(I3-1)*(A3-C3)+J3*B3-(D3>0)*(J3-1)*(B3-D3),K3*A3-(C3>0)*(K3-1)*(A3-C3)))
M3:M10 M3 =IF(L3=0,"ZERO",L3+F3)
N3:N10 N3 =IF(L3=0,"ZERO",H3*M3+G3)
K7 K7 =IF(E7="10x20",I7+2*J7,IF(E7="2x20",ROUNDUP(I7/2+J7,0),SUM(I7:J7)/E7))
K8:K10 K8 =IF(E8="10x20",I8+2*J8,IF(E8="2x20",ROUNDUP(I8/2+J8,0),""))

I don't know if it's an option for you, but I'd be tempted to use a VBA function when calculations reach around this level of complexity - especially if there's potential for other fee arrangements to be added. I find it easier to debug, amend and add new cases in something that looks like this:

VBA Code:

Function ContainerFee(Units As String, ... etc ) As Double Select Case Units Case "Per container" ContainerFee = IIf(No20 > 0, First20 + (No20 - 1) * Extra20, 0) + IIf(No40 > 0, First40 + (No40 - 1) * Extra40, 0) 'Case .... 'Case .... End Select End Function

You are such a legend!! Thank you for all your help and your patience in helping me out! It is very much appreciated.

The VBA looks impressive, but outside of my skillset unfortunately - thank you anyway!

Sirpeterjones · Friday at 12:59 AM

StephenCrump said:
Ok thanks, here's my latest take ....

A B C D E F G H I J K L M N
1 First 20 Extra 20 Cert Amendment Amendment Container Total
2 or first unit First 40 or extra unit Extra 40 Unit Fee Fee 1 Fee 2 No 20 No 40 No Units Fee Invoice Amendment
3 $120.00 $175.00 $75.00 $130.00 1 $95.00 $30.00 25% 3 4 7 $835.00 $1,280.00 $350.00
4 $27.50 5 $95.00 $30.00 25% 6 3 2 $55.00 $240.00 $90.00
5 $27.50 $25.00 5 $95.00 $30.00 25% 6 3 2 $52.50 $236.67 $89.17
6 $61.00 10x20 $95.00 $63.00 2 22 46 $2,806.00 $2,964.00 $63.00
7 $61.00 $51.00 10x20 $95.00 $63.00 2 22 46 $2,356.00 $2,514.00 $63.00
8 $110.00 2x20 $95.00 $30.00 25% 3 8 10 $1,100.00 $1,633.33 $438.33
9 $110.00 $86.00 2x20 $95.00 $30.00 25% 2 8 9 $798.00 $1,230.67 $337.67
Sheet1
Cell Formulas
Range Formula
K3:K5 K3 =IF(E3="10x20",I3+2*J3,IF(E3="2x20",ROUNDUP(I3/2+J3,0),ROUNDUP(SUM(I3:J3)/E3,0)))
L3:L9 L3 =IF(E3=1,I3*A3-(C3>0)*(I3-1)*(A3-C3)+J3*B3-(D3>0)*(J3-1)*(B3-D3),K3*A3-(C3>0)*(K3-1)*(A3-C3))
M3:M9 M3 =(L3+SUM(F3:G3))/(1-H3)
N3:N9 N3 =H3*M3+G3
K6 K6 =IF(E6="10x20",I6+2*J6,IF(E6="2x20",ROUNDUP(I6/2+J6,0),SUM(I6:J6)/E6))
K7:K9 K7 =IF(E7="10x20",I7+2*J7,IF(E7="2x20",ROUNDUP(I7/2+J7,0),""))

I'm grossing up the invoice differently to you, on the assumption (perhaps incorrect) that, for the first line

Total Invoice = $1,280 = Container Fee ($835) plus Cert Fee ($95) plus Amendment ($30 +25% of Total Invoice = $350)

It would be really helpful if you updated your Account details so we know what version of Excel you're using? You haven't replied to similar queries in your other threads, so we still don't know whether an Excel 365 solution would work for you.

I found an error in the number of units for 10x20

Where Unit = "10x20" - 2 x 20' and 22 x 40' - total units should be 5 since there are 46 x 20' equivalent units, which = 5 x multiples of 10

Where Unit = "2x20'" the calculation is working

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

This should be my last question... sorry!

Invoice Total calculation

Sirpeterjones

New Member

Excel Facts

StephenCrump

MrExcel MVP

Sirpeterjones

New Member

StephenCrump

MrExcel MVP

Sirpeterjones

New Member

StephenCrump

MrExcel MVP

Sirpeterjones

New Member

StephenCrump

MrExcel MVP

Sirpeterjones

New Member

Sirpeterjones

New Member

Similar threads

Forum statistics

Share this page

Invoice Total calculation

New Member

Excel Facts

MrExcel MVP

New Member

MrExcel MVP

New Member

MrExcel MVP

New Member

MrExcel MVP

New Member

New Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock