AutoCat issue with Amount Min and Amount Max

Google Sheets
1

@randy I’m trying to better automate two of my AutoCat rules. I would like to have all transactions for the Account “Discover Credit Card” that have “PayPal” in the Full Description be put into one of two categories:

  • If the Amount is negative, I’d like it to be categorized as Transfer Out
  • If the Amount is positive, I’d like it to be categorized as Transfer In

I thought I could use the “Amount Min” and “Amount Max” columns to do this:

  • If the “Amount Min” column is set to 0, then it should only apply to numbers larger than 0, which would apply the “Transfer In” category to positive amounts
  • If the “Amount Max” column is set to 0, then it should only apply to numbers smaller than 0, which would apply the “Transfer Out” category to negative amounts

minmax
minmax1144×79 9.97 KB

I set this up, and the “Transfer In” rule is applied to all the “Discover Card”/“PayPal” transactions, regardless of Amount. I thought maybe something wasn’t happy with Zeros, so I changed the values to “.01” and “-.01”, but it still doesn’t work properly.

Seems the “Amount Min” and “Amount Max” values are not working with negative values. Is this by design or just an edge case that fell through a crack (or maybe I’m cracked)?

2

I agree with you @jpfieber that the filter behavior can be a little bit fussy. When we built Autocat we thought the charge polarity may make the rules complicated (e.g. min and max amount ranges on expenses would both be negative) so we made the Min & Max filters work on absolute values. Most of the time, I believe this approach is helpful, but, in your case, it is confusing.

I think you will have more luck with the AutoCat “polarity” filter criteria. Just add a column to your AutoCat rules sheet called Amount Polarity then enter either Positive or Negative to the relevant filters.

Let me know if this solves the problem for you.
Randy

P.S. This functionality is documented here (if you search on “polarity filter criteria”) but it is certainly not well advertised or well known.

3

Yup, wasn’t aware of that criteria option. Just tried it, worked beautifully! Much cleaner option than using Min and Max. Thanks!

1 Like