Spy Journal 3.0 Filtering the web for you

I have decided to write a short post once a week looking at a single Excel function. This week we are going to look at the IF function.

Definition from Excel Help

The IF function returns one value if a condition you specify evaluates to TRUE, and another value if that condition evaluates to FALSE. For example, the formula =IF(A1>10,"Over 10","10 or less") returns "Over 10" if A1 is greater than 10, and "10 or less" if A1 is less than or equal to 10.

Syntax

IF(logical_test, value_if_true, [value_if_false])

My explanation

The IF function is best thought of as a solution to “either - or” scenarios. Here are some good examples with the syntax to use for each one.

I much prefer working in Excel 2007 to Excel 2003. Despite the issues with backward compatibility, there are a lot of advantages and benefits to using the new version.

Some little things that have been changed are

The previous limit on nested brackets in formulas from 7 has been increased to 64. I used this today

The number of columns and rows has increased. I used this today.

I had to develop a file for a client that involved a complex work roster arrangement to calculate days off in repeating 2, 3 or 4 week cycles for the next 20 years.

Here is the nested formula that got me the logic for a roster.

=IF($X5>=AJ$4,$X$4,IF($Y5>=AJ$4,$Y$4,IF($Z5>=AJ$4,$Z$4,IF($AA5>=AJ$4,$AA$4,IF($AB5>=AJ$4,$AB$4, IF($AC5>=AJ$4,$AC$4,IF($AD5>=AJ$4,$AD$4,IF($AE5>=AJ$4,$AE$4,IF($AF5>=AJ$4,$AF$4,$AG$4)))))))))

I then used one formula to generate over 600,000 cells and create a map that looks like this.

Time to clear the tabs in Google Chrome.

SBS2008 news is buzzing with the wrap up of the conferences stateside. Here is some important stuff.

• Susan the SBS Diva screenshots a Hyperv install of SBS2008 and follows up with some Nic settings for HyperV
• Robbie from MSFT Australia has some good technical content on SBS2008 and EBS2008.
• Australians can get their hands on training for SBS2008 and EBS2008 at discounted prices.

Robin Good’s Sharewood guide has a list of the Top 25 screen sharing tools.

Sonia Coleman has 208 free Microsoft PowerPoint templates.

I am now using Tweetdeck to manage my twitter conversations – its great!

TwitterAgency is a Virtual Advertising Agency on how to use / not use Twitter.

Brisbane mountain bike riders, check out Bushranger. They also run adventure tours in New Zealand. And if you are into horse riding the Mountain Lake Adventures crew look as if they provide a pretty decent trail riding experience just west of Brisbane.

Joseph Chirilov writes an Excel blog for the MSDN website. He often gets Excel writers to contribute.

Today I want to highlight two recent articles from Joseph’s site.

The first written by Mark Gillis is How to Create a Perpetual Yearly Calendar in Excel. The information about dates in here is very useful even if you don’t need to create a calendar. I recommend reading this if you have ever had problems managing dates in Excel.

The second one is a great example of building a complex application. Charlie Ellis, a Program Manager on the Excel team, shares a spreadsheet he built in Excel for solving Sudoku puzzles.

Building a Basic, Understandable Sudoku Solver Using Excel Iterative Calculation - Part 1/2

Building a Basic, Understandable Sudoku Solver Using Excel Iterative Calculation - Part 2/2

Enjoy.