Category Archives: Uncategorized

Importing Custom Lists

For those who make extensive use of custom lists in Excel.

Allen Wyatt’s ExcelTips

via Importing Custom Lists.

Does the location of a formula affect its accuracy?

Daily Dose of Excel reported a possible bug in the calculation in Excel documents.

This is the reason why some checks in financial models does not work perfectly.

After reading this, it would seems that it is better to use A1-A2 to check that the two value zeros rather than A1=A2 to check if the two values are identical.

Wonder if there will be a fix for this bug.

Read more in the link below

via Does the location of a formula affect its accuracy?.

Help Wanted: Calculating the First Business Day of the Month

I came across this question on my RSS feed on Excel solutions from http://excel.tips.net/.
Strangely, when I clicked on it, I can’t find any answers.

When I tried to search online as I’d always done, I can’t find a straight answer or formula to this question. Feeling challenged, I decide to decompose what Excel can do, and see if I can contribute to the Excel community.

This is helpful for calculating deadlines, timesheets etc.

Before constructing a formula, let’s see what Excel offers that can help in this.

Date – This is the basic function for dates, that return a date where you key in a specific day, month and year.

Networkdays – This function calculates the number of working days between 2 dates.  For it to work with your country’s calendar, you need to maintain a list of public holidays.

Weekday – This function returns the day of the week (e.g. 1 for Sunday, 2 for Monday, etc)

Workday – This function which is the one I’ll be using together with the Date function, returns the date after number of working days from a specified date.

The logic of it is simple: the first working day of the month is 1 working day after the end of the prior month. (i.e. April 2011 ends on a Saturday, 1 working day after is 2 May 2011)

My Excel function to solve the above question will be:
=WORKDAY(DATE([current] Year,[current] Month,[0] Day),1,[Public Holidays])
You key in 0 for Day to indicate last day of the prior month
[Public Holidays] is a list of dates of the holidays in your country maintained by yourself

Do drop in the comments if you find anything that is unclear and need extra clarifications

Get Data from Website that Requires a Login

I’m not sure how this will work out when you try to get your bank account information as most of them will require 2FA (two-factor authentication).  But for other sites that require a login, this is just real cool!

Daily Dose of Excel

via Get Data from Website that Requires a Login.

Understanding Column Widths – A Bit of History

This is some cool trivia information on how column with is determined.

Ever wondered what does 8.43 in the default column width mean?  read the link to find out.

ExcelTips Daily Nuggets

via Understanding Column Widths.

Setting the Default Font Size for Comment Balloons

Another gold nugget found.

This has always been a pain as the font size may be a little small.

These simple steps will solve it. 🙂

  1. Right-click anywhere on the desktop. Windows displays a Context menu.
  2. Choose Properties. Windows displays the Display Properties dialog box.
  3. Make sure the Appearance tab is selected. (Click here to see a related figure.)
  4. In some versions of Windows you should click the Advanced button. Windows displays the Advanced Appearance dialog box. (Click here to see a related figure.)
  5. Using the Item drop-down list, choose ToolTip.
  6. Using the other controls in the dialog box, change the font specifications as desired.
  7. Click OK, as necessary, to close all the open dialog boxes.

from: ExcelTips Daily Nuggets

via Setting the Default Font Size for Comment Balloons.

Excel 2010 Games: Missile Command and Tower Defense

Free games coded in Microsoft Excel.  Now isn’t this amazing 🙂

I’m gonna download them to try

via Excel 2010 Games: Missile Command and Tower Defense.