When I create a chart of expenditures, all the numbers are negative (outflow) numbers.
That of course is because any pivot table I create for the chart presents all negative numbers.
That in turn makes all the charts show below zero rather than above. I feel like I need to make a copy of transactions and multiply every cell by -1. Seems painful. Or, ‘reverse the polarity of inflow and outflow’ within the transaction data itself. I’d swear I’ve positive charts in an old template but can’t find it now.
Here are two youtube videos. The first one is going from negative to positive, the second one is going from positive to negative.
Hi @jeffrey.k.chapman and @Blake,
There are a couple of ways to handle this that don’t involve making a copy of your Transactions data.
You could add an “Adjusted Amount” column to your Transactions sheet. And then use a formula to calculate positive values for expenses. In the Foundations template and other Tiller templates, the Transactions sheet doesn’t have a column that tells you if a transaction has been categorized as an income or an expense.
But, you could use the VLOOKUP function to lookup the categorized Category in a Transaction row and see what its type is in the Categories sheet. If it’s an expense, you could multiply it by -1.
This formula would go in the top cell of the new column and it would create the adjustment for all the transactions. You could then use this column in your pivot table and have positive values.
={"Adjusted Amount";ARRAYFORMULA(IF(ISNUMBER(E2:E),IFNA(IF(VLOOKUP(D2:D,{Categories!A2:A,Categories!C2:C},2,FALSE)="Expense",E2:E*-1,E2:E),E2:E),IFERROR(1/0)))}
This formula assumes:
Transactions column E = Amount
Transactions column D = Category
Categories column A = Category
Categories column C = Type (income or expense)
By using the curly brackets, it creates an array that enables the “Adjusted Amount” to go in the top row and the formula starts working in the 2nd row and continue down.
If no category is found in a row, the formula uses the Amount column value. But you could make it give the inverse number by changing the last E2:E to -1*E2:E.
Let us know if that works for you.
Thank You Jonorlin and Blake. You’ve given me some ideas to work with.