Spy Journal 3.0 Filtering the web for you

Thursday night I went to my first Brisbane Drupal Meetup. It was great. There were 8 or 9 people there so most of the time was actually spend learning a little about each other (and kidding Mark about coding on reel tapes).

Jeff and Sheree and Josh from Marmalade Soul organised it. I also picked up cards from Mark, Chris, and Tony. Nick from Our Brisbane who I had already met because I am hosting and maintaining www.studybrisbane.com.au was there too. He is a real funny guy!

I had decided to present a summary of the tools and methodologies we employ here at Jethro in looking after something like 30 Drupal websites. As promised I have listed below the major tools and their uses below. This is a much bigger subject than the 10 minutes I gave to it on Thursday and I am really interested in seeing what others are doing.

Disclaimer: Yes I live in a Microsoft world. No I do not hate Macs or open source. and yes, sometimes Microsoft products are not the best products to use. On their own. But; most of the Microsoft tools that we do use are either because they are awesome or because of their integration and synchronisation with other MS tools. They are also generally easy for new staff to learn to use.

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.

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.