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

In Excel: discount 25.00 becomes -25.00.

The formula

The formula, with a numbered bracket under each part.
NumberPartWhat it doesResult so far
1OR(A8="deduction", … ),-B8Makes deductions and discounts negative.-25.00
2IF(OR(A8="base", … ),B8Keeps base, surcharge and tax positive.Not used for row 8
3IF(A8="informational",0Counts 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
5IF( … )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.

  1. See it work. The Example tab shows 20 made-up examples with the formula already in.
  2. 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.
  3. 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