Sign invoice amounts by type
A discount of 25.00 becomes -25.00. Base and tax stay positive; informational is 0.
What you'll make

The formula

| Number | Part | What it does | Result so far |
|---|---|---|---|
| 1 | OR(A8="deduction", … ),-B8 | Makes deductions and discounts negative. | -25.00 |
| 2 | IF(OR(A8="base", … ),B8 | Keeps base, surcharge and tax positive. | Not used for row 8 |
| 3 | IF(A8="informational",0 | Counts informational lines as 0. | Not used for row 8 |
| 4 | "Review component type" | Asks for review for any other type. | Not used for row 8 |
| 5 | IF( … ) | Uses the first part that matches the type. | -25.00 |
Read it left to right. Type words match in any capitals. The amounts here are made-up.
=IF(OR(A8="deduction",A8="discount"),-B8,IF(OR(A8="base",A8="surcharge",A8="tax"),B8,IF(A8="informational",0,"Review component type")))How to use it
Yellow bar at the top? Click Enable Editing.
- See it work. The Example tab shows 20 made-up examples with the formula already in.
- Use your own list. Paste each component type and its amount into the green columns of the Template tab. The signed amounts fill in by themselves.
- Move them into your letter. Copy the results. Then use Paste Special and choose Values.
Before you send
Use this only when every amount comes without a sign. Amounts that already have signs would flip twice.
Microsoft's help: IF function
OTHER ROUTES YOU MIGHT LIKE

Pick a charge description by its source
Reference + Lab panel → Lab panel
Get the template →
Label a charge reference or billing code
CI-101 → Resolve ChargeItem reference: CI-101
Get the template →
Pick the net or gross invoice total
120.00 net, 129.60 gross, Net → 120.00
Get the template →
Merge a few test letters before the whole mailing
Whole mailing → records 2 to 4 only
Take this route →