Using Excel’s Solver Function to determine which Citi transactions earned bonus points

Hello! Today I have decided to tackle an annoying problem: determining which transactions on my Citi AT&T Access More statement earned 3X bonus points at retail and travel websites (yes, nerdy thing to do). While I’m only concerned about the AT&T card in today’s post, this frustration also applies to other Citi cards that earn Thank You Points, such as Citi Prestige, Premier, Forward, etc. Knowing what counts or doesn’t count for bonus points will help all of us build a valuable database of transactions that are eligible for this generous card, such as the one that Miles to Memories started, and this one from Doctor of Credit.

However, unlike Chase and Barclay, who make it super easy to see exactly how many points each transaction earned, Citi just shows you a total for each bonus category. This is very annoying if you have a lot of transactions (and you should be experimenting a lot!) because you basically have trial-and-error to figure out which ones earned 3X and which ones didn’t. After a few minutes of writing Excel functions and randomly trying to set some Paypal transactions to 3X and others to 1X and not successfully getting the 3X to add up to what Citi claimed I earned, I knew there had to be an easier way to do this quickly. I’m lazy and like to save time. 😛

I googled “use excel to determine which cells add up to a certain number” and found that Microsoft Excel’s Solver function does exactly what I am trying to do. The rest of this post is a tutorial to show you how to use it for our specific purpose. Don’t be overwhelmed, it’s actually really easy! I will include lots of pictures for you to follow along the way, and after you do it once, you’ll be able to do it on your own and save lots of time. 🙂

Activate Solver

You’ll only need to do this once. Go to the menu Tools, and click Excel Add-ins. Check the Solver Add-In box and click OK.

Go to Tools > Excel Add-ins…

Select Solver Add-In

Download The Data From Citi

1. Sign into your account
2. Scroll to your card and click “Download Statements (PDF)”
3. Select your desired statement date, and download two files — the PDF (Adobe) and CSV (Excel) files.
4. Open up the statement PDF and scroll to Page 2 where your Thank You Points earned are listed. Write down the number listed next to “Other Purchases”. This is your 1X category; the purchases that did NOT earn the 3X.
   

   Page 2 of Statement

Set Up the Excel File

1. Open up the statement CSV file in Excel.
2. Delete the rows that indicate payments to your account (leave your returns though).
3. Insert a column to the right of the transaction amounts and type the number 1 and fill down the column.
4. A few cells down from the data in Column C, type in the following formula to multiply all the values from Column B with Column C and add them up (substitute the cell numbers to capture all the data in your Columns B & C):

   =SUMPRODUCT(B1:B5,C1:C5)

Run Solver

1. Open the Solver add-in by going to the menu Tools > Solver, or by going to the Data tab on the ribbon and clicking Solver on the far right.
2. Fill out the Solver Parameters window as follows:
   1. In “Set Objective,” enter the cell number where you had put the formula. In my case above, it’s $C$9.
   2. Set “Value of” to your 1X value from your statement. In my case, it was 220 Thank You Points.
   3. Set “By Changing Variable Cells” to the columns you typed all those 1’s in. Instead of typing, you can click in the field, drag your mouse on the cells, and it’ll fill in the correct range for you.
   4. Click “Add” and set a constraint for that same column to “Bin”, and click Ok. This will add a Constraint where your column of 1’s must remain binary numbers (1’s or 0’s).
   5. You can leave “Make Unconstrained Variables Non-Negative” checked, though it is not relevant to our case.
   6. Select Solving Method “Simplex LP”. Click on Options and uncheck “Ignore Integer Constraints”. If this does not work for you, you can try the other Solving Methods.
      

      Options – Uncheck Ignore Integer Constraints

3. Click Solve! Hopefully, it will tell you that they found a solution. If so, congratulations! Click OK.

Interpreting The Results

This is what your spreadsheet should look like now. You’ll notice that the Solver tool changed some of your 1’s into 0’s. The line items that got changed to 0’s are the transactions that earned 3X TYP, and the ones that remained as 1’s are the unbonused transactions (sad!).  Ideally, you’d like to see more 0’s than 1’s!

If you’re a more visual person and would like to enhance this view, you can also apply Conditional Formatting to highlight the 0’s (or 1’s).

0 = 3X TYP! 😀
1 = 1X TYP 🙁

So there you have it! How to quickly and easily figure out which transactions earned 3X and which didn’t. The example I gave above was my first month of using the card, so I didn’t have many transactions, and most of them earned 3X, so I was lucky. I wouldn’t have needed the Solver tool since I could very quickly and easily see that the 220 unbonused points I earned matched up exactly with one specific transaction. However, my statement after this had maybe 50+ transactions from maybe 20 different vendors, so using Solver helped me figure out which vendors coded as 3X. Hope this helps!

P.S. Founderscard coded as 3X TYP! 😀

P.P.S. CAVEAT: I didn’t address the possibility that there might be different combinations of your transactions that would all add up to the target amount. For example, if you got 90 TYP in the 1X and 30 TYP in the 3X and you had 10 transactions from different vendors that each charged exactly $10. Which vendor gave you the 3X??  I think these scenarios will probably be fairly rare though. Perhaps in this case we can call Citi customer service to find out which of those 10 vendors had the 3X?

TRoubleShooting

If Solver is not finding any valid solutions for you, try grouping transactions together by merchant. If you’re concerned that 3X was lost for a specific merchant after a certain date, try grouping it by merchant AND date. Also consider grouping by type of transaction (i.e. Apple Pay vs. non-Apple Pay transactions for the same merchant). This gives Solver an easier time trying fewer combinations. You can also try rounding numbers. Returns may throw things off, so try to massage the numbers there as well. Also, future exploration may be warranted in relaxing the precision of the Solver results since sometimes really nothing adds up.