Unusually busy on the Excel helpdesk today – Jane's follow-up question was how to do the same thing with a range of cells. eg.
My initial suggestion was to use COUNTIF(range, criteria) where the criteria is "*", but it turns out there's a better answer – COUNTA(range) which counts the number of cells in the range with are non-empty. So the formula becomes:
=IF(COUNTA(B3:C3), "Y", "N")
If you wanted to do the inverse and only return "Y" if all values in the range are blank, then COUNTBLANK(range) will do the job. Also check out KB 274537 for details on some scenarios when COUNTBLANK doesn't work so well.
This might seem trivial to us 'developer' types, but it was today's "Jane's Excel question".
You have one or more cells in a spread sheet. You want to display some text "Y" or "N" depending on whether the cell has anything in it. eg.
One solution is to use this formula:
=IF(LEN(B3)>0, "Y", "N")
This checks the length of the text in the cell. An empty cell will have a length of zero. Anything greater than zero means the cell has something in it.
It's nice to live on the cutting edge – using the latest version of everything, especially web browsers. But sometimes you may have to support users running older versions, and it is a lot easier to support a user if you can be using the same software that they have in front of them.
The problem is that you can normally only have one version of IE installed in Windows.
Roger showed me IE Tester which looks quite impressive. It works by hosting separate version-specific instances of the IE rendering engine. Probably a good option if you're a web developer.
If you have access to Microsoft's Expression Web, then Expression Web SuperPreview offers similar functionality.
Another option is to utilise the free virtual machines provided by Microsoft. These are complete standalone copies of Windows XP and Vista with versions of IE6, 7 and 8. To run these, you need either Virtual PC 2007, Windows Virtual PC (Windows 7 Pro or Ultimate only) or Hyper-V.
If you're using Virtual PC 2007, one nice thing you can do is add a desktop shortcut to the VMs so they can be launched easily:
- Create a new shortcut on the desktop
- Point it at "C:\Program Files\Microsoft Virtual PC\Virtual PC.exe"
- Give it a useful name – eg. "IE6 on XPSP3"
- Edit the shortcut target and append
-pc "VM Name" –launch
- Replace "VM Name" with the exact name of the appropriate virtual machine (Include the double-quotes if the name has spaces in it)
- Change the shortcut icon to point to one from "C:\Program Files\Internet Explorer\iexplore.exe"
Ryan Adams has posted a comprehensive list of all the available command-line options.
One thing to note – the VMs do have an expiry date. Updated VMs are usually made available before then though.
I just encountered a problem upgrading a client’s website from ASP.NET MVC2 to the preview of MVC3:
Compiler Error Message: CS1061: ‘object’ does not contain a definition for ‘StartDate’ and no extension method ‘StartDate’ accepting a first argument of type ‘object’ could be found (are you missing a using directive or an assembly reference?)
This error happens at runtime when I go to view a particular View in the browser. The view’s Model property was not being typed as my custom type and instead was reverting to the default type of System.Object.
Turns out it’s a known bug that is caused by the fact that the particular view’s .aspx page also has a
<% Import %> directive after the
<% Page %> directive.
The workaround (until the next preview is released) is to swap the two lines around, so that the
<% Page %> is the last directive in the file. eg.
<%@ Import Namespace="Client.Models" %>
<%@ Page Language="C#" MasterPageFile="~/Views/Shared/Site.Master" Inherits="System.Web.Mvc.ViewPage<Client.Models.CustomViewModel>" %>
I was looking for a book on learning about Excel pivot tables to give as a gift recently (makes a change from chocolates or a gift voucher – I know!) I found Pivot Table Data Crunching For Microsoft Office Excel 2007 on Amazon for
$AU37.14 (25.93 + 11.21P&P).
Figuring it would be quicker to get it from a real bookshop, I dropped into the Adelaide Borders bookshop, but they didn't have it on the shelf
I then tried the Borders website
- their online price for the same book is just $AU35.95, and they also happen to have an online price guarantee that they'll beat Amazon's standard price + shipping (not required in this case).
It would have been interesting to compare Borders online price to in-store price to see if they were the same. In any case, I ordered from them so we'll see how quickly they deliver.
This all does make me wonder with the advent of Kindle
eBook readers and downloadable movies what the future is for traditional book shops and video rental stores. Not particularly bright I suspect.
BenMc suggested I have a look at The Book Depository. They have the same book for $AU25.33 with free shipping. Might try them next time!