MECTILE

Tag: Excel

  • Excel VBA – Can’t find library or project.

    Here is the solution to the puzzling error “Can’t find library or project.” after opening a macro enabled WorkBook. The issue isn’t references or anything like that. Close the WorkBook, don’t need to save changes, and then open the WorkBook up in Safe Mode by holding down the Ctrl key at the same time. This…

  • Get the number of days in a year in Excel

    Here is a quick post on how to calculate the number of days in a year given it in a standard date format. i.e. returns 365 except for 366 on leap years. =IF(OR(MOD(YEAR(A85),400)=0,AND(MOD(YEAR(A85),4)=0,MOD(YEAR(A85),100)<>0)),366, 365)

  • How to calculate comparison interest rate in Excel

    Searching for how comparison interest rates are calculated, I found an article on Tomorrow Finance, but unfortunately a lot of the numbers provided no insight into how they were calculated. I hope to break it down further and explain how to calculate comparison interest rates with Excel. The Excel formula can be quite tricky and…

  • Histogram with normal distribution overlay in Excel

    This tutorial will walk you through plotting a histogram with Excel and then overlaying normal distribution bell-curve and showing average and standard-deviation lines. To produce my random normal samples I used VBA function RandNormalDist by Mike Alexander. I created samples with a mean of 100 and standard deviation of 25, function RandNormalDist(100, 0.25). The actual mean and standard deviation…

  • Calculate last business day in month with Excel

    Here is a quick post to calculate the last business day in a month taking into account holidays. This is useful for with finance calculations and wanting to know a bank’s last banking day in a month. =WORKDAY(EOMONTH(A2,0)+1,-1,holidays!A:A) Where; Cell A2 is the reference month cell Range holidays!A:A is a list of holidays

  • Calculate Stamp Duty with Excel

    A quick post of how to calculate the Stamp Duty of a property with Excel, in my example I am using the Western Australia Residential Rate Dutiable value. Download the Excel WorkBook. Update, next day This also works for Individual income tax rates;

  • In Excel, find the value on the last row as a function, not VBA

    Here is a quick post, how to find the value of the last cell in a column. Exaample below uses Column A with the first 3 rows used for the header: =INDEX(A:A,COUNT(A:A)+3)