Structuring your data is extremely important. Comparable information should be in comparable cells to be able to compare and calculate.
So first make a good spreadsheet design.
- reimport from some other system
- retype or copy/paste/edit individual cells
- use character string functions to extract subfields if data is structured enough
- write your own extract functions if data is not structured enough to work with standard formulas
This could be a lot of work. This is a simplified example of one of the Software Engineering rules: "First think, than do. If you don't think before doing, that might mean a lot of work to redo."
Hope this helps.
I have a worksheet that shows mortgage companies and their 15-year, 30-year and 1-year ARM loan rates.
They are currently set up with term, interest, points, and APR in this way:
Company A 15-YR 5.250 1.625 5.622
Phone number 30-YR 5.750 1.875 6.001
1-YR ARM 3.625 1.250 4.556
Company B 15-YR 5.125 1.500 5.479
Phone number 30-YR 5.375 4.000 5.822
1-YR ARM 3.000 1.250 4.607
How can I get all of the 15-year, 30-year and 1-year ARM APRs in separate columns so that I can calculate averages?