Python, R, SQL, Excel VBA Macros and C# for Quant Developers and Data Scientists

Showing posts from March, 2011

Posts

Interviews/Jobs
   3 things to remember before the Interview
   50 Oral VBA Interview Questions
   15 Essential Logical Puzzles (VBA)
   15 Excel Pivot Tables Interview Questions
   50 Excel Formula/Functions Questions
   Jobs (Analyst/Excel/Access/SQL)

Formulas
   BEGINNER
      Absolute/Relative References
      Nested Formulas
      Arithmetic Operations(+,-,/,*)
      Text Functions
         CHAR         CLEAN         TRIM
         LEFT         RIGHT         REPT
         REPLACE
      Mathematical Functions
         ABS         FLOOR         CEILING
         ROUND         INT         SIGN
         ODD         EVEN         PI         TRUNC
      Information Functions
         ISBLANK         ISERR         ISERROR
         ISEVEN         ISLOGICAL         ISNA
         ISREF         ISTEXT
      DATE/TIME Functions
         SECOND         MINUTE         HOUR
         DAY         MONTH         YEAR
         TODAY         NOW
      Statistical Formulas
         SMALL         LARGE         MAX
         MIN         MEDIAN         AVERAGE
         SUM

   INTERMEDIATE
      Auditing Formulas
      Trace Errors/Smart Tag
      Text Functions
         MID         SUBSTITUTE
         LEN         FIND         VALUE
      Mathematical Functions
         QUOTIENT         ROUNDDOWN/UP
         MOD         POWER         MROUND
      Logical Functions
         SUMIFS         COUNTIFS
         AVERAGEIF         IF
         AND         NOT         OR
      IFERROR Function
      Date/Time Functions
         TIMEVALUE         DATEVALUE
         DATEDIF
         WORKDAY         WEEKNUM
      Information Functions
         COUNT       COUNTA       COUNTBLANK
         ERROR.TYPE         N         NA         TYPE

   ADVANCED
      Array Formulas(CSE)
      User Defined Functions(UDF)
      Lookup Functions
         VLOOKUP         HLOOKUP
         MATCH         INDEX         CHOOSE
      OFFSET Function
      INDIRECT Function
      FREQUENCY Function
      Statistical Functions
         FORECAST         GROWTH
         TREND
      Mathematical Functions
         FACT         PERMUT
         COMBIN         MINVERSE         MMULT
         RANDBETWEEN
      Database Functions
         DAVERAGE         DCOUNT         DSUM
         DGET         DMAX         DMIN
      Date/Time Functions
         EOMONTH         NETWORKDAYS

How to use?

Macro Recorder
Excel VBA Macro
Formulas
User Defined Functions(UDFs)
Event Macros
Charts
Header & Footer
Name Manager
Formula Auditor
Sort
Remove Duplicates
What-If Analysis
Subtotal
Scenarios
Data Consolidation
Protection Features
Proofing Features
Sharing Features
Workspace
Install Excel Add-in

Sample Dashboards
Cumulative Sales Dashboard

Miscellaneous
      Contact a Trainer
      Recommended Books
      Sponsored Products
      Ask your Excel Query

Search This Website

Posts

Analyzing Date and Time Related Data in Excel

Create Excel Dashboard using OFFSET, SUM and MATCH functions

ADD ERROR TRAP: Wrap your Formulas with IFERROR or ISERROR