Forum Discussion
Solved
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 totals all of the transactions into one total val. I know there's a way to make the Debits negative numbers but I can't figure it out.
Thanks for any help.
Linda
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 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.)
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
