Debit amount format to minus value

I downloaded a bank statement. Although the transactions are labeled Credit/Debit the amount column has no format so when I insert the data into a pivot table the sum is not correct, it simply total...

excel

SnowMan55
May 21, 2023
Linda_Meredith You could add a helper column.

Let me assume that the label column (containing "Credit" or "Debit") is column B and the monetary amount is in column E. Then, with data starting in row 2 (just below column headers), you can put this formula into the helper column, and copy it down:
=IF( B2="Credit", E2, IF(B2="Debit", E2*-1, "What?? " & E2) )
(The spaces are included for readability; they are not required.)

SnowMan55

Bronze Contributor

May 21, 2023

Linda_Meredith You could add a helper column.

Let me assume that the label column (containing "Credit" or "Debit") is column B and the monetary amount is in column E. Then, with data starting in row 2 (just below column headers), you can put this formula into the helper column, and copy it down:

=IF( B2="Credit", E2, IF(B2="Debit", E2*-1, "What?? " & E2) )

(The spaces are included for readability; they are not required.)

Linda_Meredith

Copper Contributor

May 21, 2023

My gosh! Thank you so much. I've racked my brain trying to get a resolution and still managing the values manually. This is great!

Than you so much.

Linda

SnowMan55

Forum Discussion

Debit amount format to minus value

Resources