Function Problems

Use IF and OR to Negate Payment and Check Amounts in Excel

Anum Mushtaq Nov 12, 2026 869 views

Question details

Create a formula that changes an amount to negative when its transaction type is Payment or Check, without changing other amounts.

Use IF and OR to Negate Payment and Check Amounts in Excel

Product: Microsoft Excel
Device & OS: not provided
Scenario: The transaction type is listed in cell C13 and the amount is listed in cell H13.
Observed behavior: Payment and Check rows should return a negative amount, while every other transaction type should retain the original amount.

Before you start

Confirm that the transaction type text in column C matches the labels Payment and Check exactly. Make sure the amount cell contains a numeric value so the formula can reverse its sign.

Solution 1Recommended

Use IF with OR to reverse Payment and Check amounts

Use this formula when two specific transaction types must display as negative values and all other transaction types must remain unchanged.

The OR function must contain a complete comparison for each transaction type. It returns TRUE when C13 is either Payment or Check, allowing IF to negate the value in H13.

Select the result cell

Click the cell where you want the adjusted amount to appear, such as I13.

Enter the IF and OR formula

Type =IF(OR(C13="Payment",C13="Check"),-H13,H13) and press Enter.

Fill the formula down

Select the formula cell and drag the fill handle down the column to apply the formula to additional transaction rows.

Use IF with OR to reverse Payment and Check amounts

Formula result: The formula returns -H13 for Payment or Check and returns H13 for every other type.

Solution 2

Use multiplication by negative one in the true result

Use this equivalent version if you prefer explicitly multiplying qualifying amounts by negative one.

Spreadsheet formulas made simple

Negate Payment and Check Transactions with WPS Spreadsheets

WPS Spreadsheets lets you use the same IF and OR logic to convert selected transaction amounts to negative values while preserving other values. Enter the formula once, then fill it down your transaction list.

1. Open the transaction workbook: Open your .xlsx or .xls file in WPS Spreadsheets and locate the Type and Amount columns.
2. Choose the result column: Click the first empty cell beside the transaction row where the adjusted amount should be displayed.
3. Enter the conditional formula: Enter =IF(OR(C13="Payment",C13="Check"),-H13,H13), then press Enter.
4. Apply the formula to remaining rows: Drag the fill handle from the formula cell down the result column to calculate each transaction.

Supports IF, OR, and arithmetic formulas for transaction calculationsQuickly fills formulas down across payment recordsCompatible with Microsoft Excel .xlsx and .xls spreadsheet filesProvides a familiar spreadsheet interface for Excel users

microsoft office alternative - wps office

Frequently Asked Questions

Why does OR need to repeat the cell reference for each transaction type?

Each OR argument must be a complete logical test. Use OR(C13="Payment",C13="Check") rather than comparing C13 to one combined text value.

Can I include more transaction types in the formula?

Yes. Add another comparison inside OR, such as =IF(OR(C13="Payment",C13="Check",C13="Withdrawal"),-H13,H13).

What happens if the amount in H13 is already negative?

The formula reverses its sign. For example, if H13 is -25 and C13 is Payment, -H13 returns 25. Use a different formula if qualifying values must always be negative.

How can I force Payment and Check amounts to always be negative?

Use =IF(OR(C13="Payment",C13="Check"),-ABS(H13),H13). The ABS function ensures the qualifying amount is negative whether the original value is positive or negative.

Excel & Data Problems