ElyxAI

Customs entries in Excel: which declared value does not match the invoice

Customs BrokerCustoms Value Gap

A customs value list is one row per entry. You type the entry, the client, the invoice value, the declared value, and the duty amount copied from the document you already hold. The gap is invoice minus declared while the row is open and both values are filled. The window says under when that gap is above zero, over when it is below zero, and match when the two values are equal. The duty amount is not in the gap. The sheet does not multiply by a duty rate. The first screen is a sheet named Entries. Row 1 is the header. Row 2 is one entry you can check by hand: invoice 10000, declared 8000, duty 400, status open. The gap is 2000. The window is under. There is no delivery date and no selling price on that screen. This sheet is not an export price grid. That grid is a shelf price by product. This list uses the invoice value and the declared value you typed for one entry. It is not a supplier invoice register, and it is not a delivery tracker of promised dates. The duty cell is a number you typed. 400 on the sample row is that typed number, not a rate applied to 10000. The question is which open row does not match. Closed rows drop out of the window. They stay on the sheet. A negative gap is over, not a number to hide. A blank declared value keeps the gap blank. Elyx can write the gap and the window. You still type both values and the duty. The trial lasts 7 days, with no card.

The Problem

The entry fails when the two values live in two files. A sentence says the declaration is short of the invoice. Nobody can add the gaps, because the invoice value and the declared value are not on the same row. A sentence without those two numbers is not a value list. A second failure is mixing this sheet with an export price, a supplier bill, or a delivery date. An export row answers a selling price. A supplier row answers a bill to pay. A delivery row answers a promised date against an actual date. If you paste prices, supplier bills, or dates here, the morning sum no longer means invoice minus declared. Keep this sheet to entries and the two values. A third failure is deleting an entry when it is filed. Set the status to closed and leave the row. The gap goes blank. Deleting the row removes the only record of the 10000 and the 8000 you already typed. The sheet still does not decide what the declared value should have been, and it still does not look up a duty rate.

Benefits

Each entry is one row with an invoice value and a declared value you typed.

The gap is invoice minus declared while the row is open.

Under means the declared value is below the invoice. Over means it is above. Match means the two values are equal.

The duty amount is a number you typed. It is not a rate times the invoice.

Closed rows stay on the sheet and drop out of the under count.

Step-by-Step Tutorial

1

Create the list and the header

Name the sheet Entries. In row 1 type: Entry, Client, Invoice, Declared, Duty, Status, Gap, Window, Note. Entry is the file reference. Client is the name you use. Invoice and Declared are values you typed. Duty is the duty amount copied from the document, not a formula. Gap and Window are formulas. Status is a word from a list. Note is optional. Freeze row 1. Do not merge cells.

One row per entry. A second declaration for the same client is a second row. Do not pack two values into one cell.

2

Lock the status words

Select Status from row 2 down. Data, Data Validation, List. The source on a sheet named Lists is open and closed. Open means the gap is still yours to read. Closed means you are done with that entry. Only open stays in the window. People must pick a word. A synonym such as filed or sent will not match the formulas.

Invoice, Declared, and Duty are numbers. Do not type the word under in those columns.

3

Enter five entries you can check by hand

On row 2 type entry E1, invoice 10000, declared 8000, duty 400, status open. You will predict gap 2000 and window under. On row 3 type E2, invoice 5000, declared 5000, duty 150, status open. Gap should be 0 and window match. On row 4 type E3, invoice 2400, declared 3000, duty 90, status open. Gap should be -600 and window over. On row 5 type E4, invoice 1200, declared blank, duty 40, status open. Gap and window should go blank. On row 6 type E5, invoice 10000, declared 8000, duty 400, status closed. Gap and window should go blank. Use these amounts before you trust the formulas. The 400 is a number you typed. It is not 4 percent of 10000.

If you do not know the declared value yet, leave Declared blank. The gap stays blank. Do not type zero unless the declared value really is zero.

4

Write the gap

The gap is blank when status is closed, or when Invoice or Declared is blank. Otherwise it is Invoice minus Declared. With Invoice in C2, Declared in D2, and Status in F2, use =IF(OR(F2="closed",C2="",D2=""),"",C2-D2). Format as a number. A positive number means the declared value is below the invoice. Zero means the two values match. A negative number means the declared value is above the invoice. Duty is not in this formula.

=IF(OR(F2="closed",C2="",D2=""),"",C2-D2)

10000 minus 8000 is 2000. 2400 minus 3000 is -600. If you see another number, the cells are not the values you typed.

5

Write the window

The window is blank when status is closed, or when either value is blank. Otherwise it is under when Invoice minus Declared is above zero, over when that difference is below zero, and match when it is zero. Use =IF(OR(F2="closed",C2="",D2=""),"",IF(C2-D2>0,"under",IF(C2-D2<0,"over","match"))). Under means the declaration is short of the invoice. Over means the declaration is above the invoice. The duty amount and the client are not in this formula.

=IF(OR(F2="closed",C2="",D2=""),"",IF(C2-D2>0,"under",IF(C2-D2<0,"over","match")))

Do not wrap the gap in ABS. Hiding the minus sign hides the row where declared is 3000 against an invoice of 2400.

6

Count the under rows and add the duty you typed

On a sheet named Morning, the under rows are =COUNTIFS(Entries!H:H,"under"). With the five rows above, that count is 1: the gap of 2000. The under sum is =SUMIFS(Entries!G:G,Entries!H:H,"under"), which is 2000. The over count is =COUNTIFS(Entries!H:H,"over"), which is 1, the gap of -600. The duty on open rows is =SUMIFS(Entries!E:E,Entries!F:F,"open"), which is 680: 400 plus 150 plus 90 plus 40. Closed E5 is out of the window and out of the duty sum, even though its duty cell says 400. A blank window is not under. Write the rule next to the count.

=SUMIFS(Entries!G:G,Entries!H:H,"under")

Do not add the -600 into the under sum. Over is a different word, so SUMIFS leaves it out. Do not multiply 10000 by a rate to replace the 400 you typed.

7

Read a negative gap as a signal, not as a deletion

If the gap is negative and status is still open, the declared value is above the invoice and the row was not closed. That is a row to open, not a row to delete. Either the declared value was typed on the wrong entry, or the invoice value is short of what you meant. Fix the cell that is wrong. The -600 on E3 does not say a duty is due. It says 3000 was typed against an invoice of 2400. A gap on a closed row should not happen, because closed blanks the cell. If you see a number on a closed row, the formula was overwritten.

Filter Window to under for the morning list. Filter Status to closed when you need history. Duty stays a typed number on the row.

8

Check the five rows before you share the sum

Invoice 10000 and declared 8000, duty 400, status open: gap 2000, window under. Invoice 5000 and declared 5000, duty 150: gap 0, window match. Invoice 2400 and declared 3000, duty 90: gap -600, window over. Invoice 1200 and declared blank, duty 40: gap blank, window blank. The same 10000 and 8000 with status closed: gap blank, and the under sum ignores the row. The under count is 1, the under sum is 2000, and the open duty sum is 680. If a check fails, fix the values or the status word before you send the sum. The sum is only this sheet. It is not a delivery date, and it is not a duty rate.

Write the five checks on Morning if more than one person updates the entries.

Template Features

Gap while the entry is open

Invoice minus declared. Blank when status is closed or a value is empty.

=IF(OR(F2="closed",C2="",D2=""),"",C2-D2)

Window from that gap

Under when the gap is above zero, over when it is below zero, match when it is zero.

=IF(OR(F2="closed",C2="",D2=""),"",IF(C2-D2>0,"under",IF(C2-D2<0,"over","match")))

Under count

COUNTIFS reads the window word. With the sample rows the count is 1.

=COUNTIFS(Entries!H:H,"under")

Under sum

SUMIFS adds only gaps whose window is under. The sample sum is 2000.

=SUMIFS(Entries!G:G,Entries!H:H,"under")

Duty you typed on open rows

SUMIFS adds the duty column where status is open. The sample sum is 680. It is not a rate.

=SUMIFS(Entries!E:E,Entries!F:F,"open")

Concrete Examples

A declared value 2000 below the invoice

Entry E1 has an invoice of 10000 and a declared value of 8000. The duty you copied is 400.

Invoice: 10000. Declared: 8000. Duty: 400. Status: open.

Result: Gap is 2000. Window is under. The 400 stays the number you typed. It is not calculated from 10000.

A declared value above the invoice

Entry E3 has an invoice of 2400 and a declared value of 3000. You do not want that row in the under sum.

Invoice: 2400. Declared: 3000. Duty: 90. Status: open.

Result: Gap is -600. Window is over. The under sum stays 2000.

The declared value is not typed yet

Entry E4 has an invoice of 1200 and a duty of 40. Declared is empty.

Invoice: 1200. Declared: blank. Duty: 40. Status: open.

Result: Gap is blank. Window is blank. The open duty sum still includes 40, because duty is summed from status, not from the window.

Pro Tips

Zero declared is a number

Type 0 only when the declared value really is zero. A blank cell means you do not know yet, and the gap stays blank. 1200 minus 0 is a gap of 1200 and a window of under.

Duty is not a formula

Copy the duty amount from the document you hold. Do not write a percent of the invoice in that cell and present it as the duty. The sample 400 is only a typed example.

Text values do not subtract

If Invoice was pasted as text, C2-D2 will not be 2000. Re-enter 10000 and 8000 as numbers. Then read the window again.

Distinct from a price grid and a delivery date

An export price grid is a selling price. A supplier register is a bill to pay. A delivery tracker uses dates. Do not paste those rows here and expect this sum to mean the same thing.

Formulas Used

Don’t want to do it by hand? Ask Elyx

Example request

“Build this in my workbook: Invoice value against declared value, with the formulas, totals and formatting”

Elyx does it directly in your Excel file.

7 days free · No credit card required

Related guides

Frequently Asked Questions