• Microsoft Excel:
- Books I endorse: fraud prevention “Spreadsheet Checks & Controls”, by O'Beirne; sort by colors, other special sorting and formulas “This isn't Excel, it's Magic”, keyboard shortcuts, print screen paste, mail merge, remove duplicates with one click "Excel, out of the box", by Bob Umlas.
- Excel formulas: If's with 2 requirements, SumIfs, CountIfs, AverageIfs, IfError, GetPivotData, Dsum, SumProduct, Multiply(*), Divide(/), Remainder(Mod), Count, CountA, Average(doesn't count empty cells), Power(^), SubTotal, Vlookup, Hlookup, Choose, Max/Min, Small/Large (2nd Largest or 3rd Smallest), Rank, Exact, Match, Even, Odd, Round, RoundDown, Trunc, Row, Column, Index, Offset, Left, Right, Mid, Len, Trim, Value, Text(date/currency), Today, Now, TimeValue, Rand, RandBetween, Find, Search, Substitute, Char, And, Or, Not, IsError, IsBlank, IsText(T), IsNumber(N), Median, Mode, Concatenate(&), Intersection( ), Indirect.- Math: 1) mortgage payment breakdown by month, illustrates principal repayment vs. interest fee, 2) PMT, 3) NPV Net Present Value, 4) IRR Internal Rate of Return, 5) RATE.
• Microsoft Excel & Access:- Create data entry systems which use "drop down lists", also called "combo boxes", for limiting users to a specific type of data. Examples: Yes/No, AM/PM, States: NY/NJ/CT/PA, other unique lists, positive whole numbers or negative decimals, or a specific length of digits/characters.
- Create a linked MS Access table, to an Excel spreadsheet, and can create a query for matching data.
- Discovered Access error, when exporting to an existent Excel file consisting of >65536 rows.
• Microsoft Access:- Create a "Prompt Query", so when MS Access is opened, a "Form" opens up immediately, which asks the typist for specific criteria before it runs, (can also work with a macro named AutoExec, similar to Excel's Auto_Open).
• Microsoft Excel fun:
- Custom filter for item amounts >100, or items beginning with "department x", or items fulfilling both.
- Copy/Paste absolute references, $A$1, (which is different than normal relative references, A1).
- Excel Error Identification: “column width too small” error, “Vlookup” error, "greater than 15 digits" error, Ref#, Value#, Null#, INT error, sorting error shows wrong month order, Apr, Aug, Dec, as opposed to Jan, Feb, Mar, answer:date=text format.
- Excel Error Identification: for Excel 2003 "greater than 7 nested If's" error, for Excel 2003 "greater than 3 conditional formats" error, 65535 math error in Excel 2007 is fixed by patch via http://support.microsoft.com/kb/943075
- Charts, trend lines, named ranges in formulas, pivot tables, Visual Basic macros, XML import, SQL.
- PageBreak preview, complex conditional formatting, validation, text to columns.
About Me:
• University of Dayton, Ohio
- May 1997, received Bachelor of Business Administration, major of Finance.
• Microsoft certified, Excel 2003, expert test taken and passed in 2008.
• Organize meetup.com/spreadsheets
• 1991-2011 Performed account reconciliations, receivables/payables, auditing, webmaster, SEO and site traffic measurement. Worked as a consultant, usually by designing custom alpha/numeric tabulating systems. Examples: customer order records/documentation, computerized inventory, customized cash registers.
- Pfizer, The New York Times, Citibank, Ryan Beck Investment Bank, H&R Block, ADP, TIAA-CREF, Edward Jones, Mercantile Bank, Cendant, MCI, United HealthCare, Yeshiva Univ., Volvo Const. Equip., Mallinckrodt Finance of Resuscitators, Holiday Statistics, ColorArt Financial Proofreading, inacom.com, accesscom.net, UPS, NJ Transit, Sigma Aldrich Chemical, Suburban Propane, Kuehne Nagel Cargo, Washington Inventory Service.
