Friday, December 02, 2011

Tip: Suppress #DIV/0 errors in Excel with a formula

If you ever receive a #DIV/0 error in Excel that you expect to be there (i.e. calculating percentages from other data which may not have accumulated yet), you can suppress  it by using an IF statement in the formula.

Let's say you have three columns -- A, B, and C. C calculates the percentage of B and A (i.e B/A), but if A is 0 or empty, you'll get the DIV/0 error which throws off formulas, coloring, highlighting, etc.

Instead of :

= B1/A1

Do something along the lines of the following:

=IF(A1=0, 0, B1/A1)


=IF(A1=0, "", B1/A1)

This does a simple check first: If A is zero, it will substitute a default value. Otherwise, your calculation will go on as planned.

I've used this tip many a time to help with the presentation of an Excel sheet.

Thursday, December 01, 2011

SQL Server: Save Not Permitted Dialog Box [Field Notes]

In a new install of SQL Server 2008, when attempting to save changes to a table in a new database, I occasionally get the following error:

Saving changes is not permitted. The changes you have made require the following tables to be dropped and re-created. You have either made changes to a table that can't be re-created or enabled the option Prevent saving changes that require the table to be recreated.

Per this excellent article on MSDN help, take the following steps to rid yourself of this error:

  • Tools --> Options
  • Expand the Designers Section
  • Click the Table and Database Designers section
  • Uncheck the box labeled "Prevent saving changes that require table re-creation."

Don't do this on a production database. Just don't do it. I only needed this for a quick temporary database, or when working in design mode. Otherwise, this option should always be enabled.

Save (Not Permitted) dialog box [MSDN]

Tuesday, November 29, 2011

Fix: VirtualBox doesn't allow to re-name a registered drive and readd to a VM [Field Notes]

In VirtualBox, if you take the following steps:

  • Have HDD1.vdi and HDD2.vdi as storage
  • Remove HDD1.vdi (and delete it) 
  • Rename HDD2.vdi to HDD1.vdi
  • Attempt to add the (new) HDD1.vdi to the VM
You will receive an error along the lines of: 
Failed to open the hard disk [Path to your VDI file].
Cannot register the hard disk [Path to your VDI file] {[UUID of your new VDI file]} because a hard disk [Path to your VDI file] with UUID {[UUID of your old VDI file} already exists.
  • In VirtualBox, select File --> Virtual Media Manager.
  • Click the hard disk in question.
  • Click "Release" and then "Remove to remove from the media library.
You will now be able to add your renamed VDI file.

Fix: CentOS 5.x hangs during VirtualBox install [Field Notes]

Technology Involved
  • Win7 Pro x64
  • VirtualBox 4.1.x
  • CentOS 5.x

When attempting to install a CentOS Guest VM from a VirtualBox Win7 x64 host, the installation appears to hang shortly after beginning.

The last line of text on the screen is:
NET: Registered protocol family 2
Per this thread (referencing an older version of VirtualBox but still relevant), the fix is as follows:
  • Go into the settings of the VM
  • Click the System section.
  • Check Enable IO APIC.
  • Save the settings.
When you start the install again, it will proceed normally.

Monday, November 28, 2011

Update on the New Desktop Setup

This is the desktop, assembled, without the enhanced airflow in the rear and with no overclocking yet.

Windows Experience Score (out of 7.9)
  • Processor: 7.8 
  • Memory (RAM): 7.8
  • Graphics: 7.7
  • Gaming Graphics: 7.7
  • Hard Drive: 6.0 (I think the SSD caching hasn't fully kicked in yet)
PCMark 7 Score: low 4,000 (~4,100? Forgot the actual number)

Standard Temperature: ~89F (hottest of the 5 temp sensors)

Gaming Temp: ~93F (tested with Call of Duty: Black Ops)

Next Steps
  • The "overclockening" -- when I feel like it. It runs smooth like butter now and I have no reason to push it (this is going to be part server, after all)
  • Increasing the air flow
  • Ensuring everything is backed up (Backblaze is chugging along now)
  • Moving media/content from External HDD onto Desktop
  • Erasing External HDD
  • Configuring Windows System backup on HDD
  • Virtualization, and tons of it. Setting up a bunch of systems (more posts on why later)
Also, a Big Thank You
Thanks to John Herron, a former co-worker and excellent techie, I also now have a 20" LCD monitor. The kindness of nerds astounds.

Any ideas for other benchmarks? Let me know!

Notes on Sub-Reports in Crystal Reports 2008 [Field Notes]

For starters, in CR 2008, you can't create two separate groups of data in one report (for example, all labor for a work order and then all materials for a work order).

To do this, you need to create a sub-report, format it, and pass any values back up to the main report using shared variables.

This forum post that I found has a great quick run-down on how to accomplish shared variables from sub-reports to main reports. It was very helpful in understanding the wonky Crystal syntax behind what should be a simple thing.

The post is excerpted (though poorly formatted) below in case the originating site ever goes away:

Shared variables, introduced since Crystal Reports version 7, make it
easier to pass values from a subreport to the main report. 
Using shared variables requires two formulas:
one to store the value in a shared variable, the other to retrieve the
value from the shared variable.
The most important thing to remember when using shared variables is
that Crystal Reports must first evaluate the formula where the value is
stored before evaluating the formula that retrieves the shared variable.
For example if you want to pass a grand total from the subreport to do
a calculation in the main report, follow these steps:
1. In the subreport, create a formula similar to the one below:
//Stores the grand total of the {Orders.Order Amount} field
//in a currency variable called 'myTotal'
Shared CurrencyVar myTotal := Sum ({Orders.Order Amount})
2. Place this formula in your subreport.
3. In the main report, create a formula that declares the same variable name:
//Returns the value that was stored in the shared currency variable called
//myTotal in the subreport
Shared CurrencyVar myTotal;
4. Place @MainFormula in a main report section that is beneath the section
containing the subreport. For the shared variable to return the correct value in the main report,
you must place @MainFormula in a main report section that is beneath
the section containing the subreport. This ensures Crystal Reports evaluates the @SubFormula before @MainFormula.
One way to do this is to insert a section below the section containing
the subreport, and place @MainFormula in this new sub-section:
· On the 'Format' menu, click 'Section'.
· On the 'Sections' list, click the section containing the subreport.
· Click 'Insert' (at top of dialog box). This inserts an additional
· Click 'OK' to return to the report, and insert @MainFormula into this
new subsection.
The next time you preview the report, @MainFormula displays the value
from the subreport.
In this particular example, that value was the grand total of the
{Orders.Order Amount} field. 
5. Once you have verified that @MainFormula is returning the correct
value from the subreport, you can include this formula in other
main report formulas, such as:

//includes data from subreport
{@MainFormula}+ Sum ({Customer.Last Year's Sales})
Place this formula in the same section as @MainFormula, or in a section
further down on the report.

How to Get Formula Field Value From Subreport to Main Report [CodeGuru]