Helpful hint on category matching - Income vs Expense

Conversation Helpful Tips
,
1

Howdy!
I found that my category rules in AutoCat sometimes put both, expense and refund of the same vendor into the same account. So I may end up with credit categorized as expense or vice versa. Assuming that credit is always positive, and debit/expense is negative, it helps to add 0.01 MIN amount for credit and -0.01 MAX for expense. This can be achieved with these two formulas (thanks ChatGPT!)

Put this into ‘Amount Min’
=IFNA(SWITCH(VLOOKUP($A2, Categories!$A:$C, 3, FALSE),“Income”, 0.01, “Expense”, “”,“”), “”)

Put this into ‘Amount Max’
=IFNA(SWITCH(VLOOKUP($A2, Categories!$A:$C, 3, FALSE),“Income”, “”, “Expense”, -0.01,“”), “”)

This assumes that category names are in column A of both sheets. Adjust as needed.
Transfers don’t get min/max values.

1 Like
2

… and I did that before I read about the Polarity categorization option. This means just one Amount Polarity column can be used for this formula, setting “positive” for Income and “negative” for Expense.
And I wanted a formula for this because I have 365 rules in AutoCat and didn’t want to classify them all by hand :slight_smile:

3

Thanks for the tip @Evgen

4

The single formula for “Amount Polarity” column is:
=IFNA(SWITCH(VLOOKUP($A2, Categories!$A:$C, 3, FALSE),"Income", "positive", "Expense", "negative",""), "")
Somehow, the Amount Min and Max method didn’t work for me, but this works.