Bank of America, bless their hearts, re-did their website and at the same time changed the format of the information I download with my credit card transactions. I have multi-year year history of my transactions that I keep in an Excel spreadsheet that is over 1,000 rows long now. The new format is incompatible with my existing data because they apparently decided that showing charges as positive numbers and credits as negative numbers was too confusing and they reversed them.

OK, it's not too difficult to add another column to my spreadsheet that contains new cells along the line of "=G1002*-1", but that leaves me with a column full of formulas, and I do other manipulations on the data such that I would prefer to have the actual value in the cell rather than the formula that creates the value.

The "Value" function doesn't do what I need, like: =value(G1002*-1) because it still leaves the formula in the cell, rather than just the actual value. I suppose I could go through the existing rows line by line and manually change the sign on each value and then just go with B of A's new format from now on, but I'd prefer to keep things the way they were.

I seem to recall a function in Excel to convert formulas in cells to the actual value created by the formula, but I can't find it, or perhaps I am thinking of some other program.

Does anybody know of such a function?

tanstaafl.

If you type in a formula, you'll always have a formula. What you want to do, most likely, is create a row with the formulas, select and copy those cells, then select a new row you want to paste them in, right click and select "Paste Special...". It'll give you a dialog box. Click the "Values" radio button and then click "OK". You'll have a new row with the new values.

You can then delete the old rows and replace the original data with the new data in the new row.

All that being said, I can't imagine what you could possibly be doing within Excel that would prevent you from having those numbers calculated by formula. When you reference another cell, Excel does its thing on the value, not the formula.

I think it would make more sense to keep the data as is and just add a new row with the inversed numbers as a formula.

Oh, because you have an automated import of the new data and you need to keep the same column structure. Fair enough. I'd probably still do it differently, but I really hate changing original data. Perhaps too much so.

Perhaps it's time to switch the signage of the old to match the new.

There is also custom formatting where you can make excel lie about the sign, or have blue for expenses and green for payments, no sign at all - except when you need to hand key values.

Just curious, have you considered using Quicken instead? I've been a Bank of America customer through two name changes (Summit -> Fleet -> BoA) and Quicken hasn't missed a beat.

Unless you're a Mac user. They don't support Quicken on the Mac for some reason. Every other bank I deal with does.

Just curious, have you considered using Quicken instead?

Yes, that would work, Quicken is one of the formats I can save the data into. But I have never used Quicken, and Luddite that I am, don't really want to purchase, install, and learn to use yet another computer program.

I am pretty good with Excel, and the analyzing I have to do on the data is pretty minimal, to the point where Quicken would probably be overkill. Plus, should I need to do something weird with the data (like sum all of the charges within a given date range that can be attributed to a particular family member) the sheer power built into Excel is hard to beat. For all I know, Quicken could do the same... but I already know how to do things like that in Excel.

tanstaafl.

I see it as a matter of personal style: Quicken is stupid simple to use and has a lot of great customizable reports that I use every year during tax season. Excel gives you the maximum power and flexibility to do whatever you want with your data (Feels odd using those words to describe a Microsoft product, but hey it's pretty damn good.)

Seems to me you'd probably be able to learn Quicken in about 20 minutes, and it's cheap (US$29).

Though, I imagine trying to import your existing data into Quicken might be a major project if you have a lot of custom fields. I've never tried it so can't comment.