Here’s a cool way to highlight the active row in Excel (all sheets). This is done using Excel 2007, but it also applies to previous versions of Excel.

You can watch the first part of: **Excel 2007: Highlight Active Row (Take 2 – Part 1)**

]]>

Excel 2007 introduced Icon Sets to Excel Conditional Formatting. Excel 2010 introduces No Cell Icon to Conditional Formatting.

In Excel 2007, after applying your Icon Set Conditional Format, you would get something like this:

The problem, of course, is that the whole this is a mess. If the idea is to identify poor grades, you&rsquo;d need to create extra Conditional Formats so that you could hide the undesired icons. Excel 2010 introduces No Cell Icon which removes the need for extra formulas and extra Conditional Formats. Hereâ€™s how it looks like:

By choosing No Cell Icon, Excel 2010 will omit the icons for the selected conditions saving you all the hassle of extra conditioning

]]>

If you use Excel and enter the following formula: **=FACT(0.5)** the answer will be one. However, if you open a more rustic tool such as the Windows Calculator and try again, you will get **0.886226925510**.

So, how can a tool such as the Windows calculator come up with the correct answer when Excel can only return 1? What about the factorial of 0.75 (0.75!)? Again, Excel returns 1 whereas the Windows calculator gets the correct answer: **0.919062526898**…

Both numbers are close to one, but they are not exactly one.

The trouble is that Excel is using the general rule that states that the factorial is, in fact, the product of all integers in the series. So 4! Is given by **=(4*3*2*1)=24**. Unfortunately, the story does not end there for factorials.

When dealing with factorials the above works fine for integers. When dealing with non-integers (greater than zero), then we need the natural logarithm of the gamma function to get it to work. This function uses the concept of limits to determine the factorial of a number, so the factorial of 5 (**5!**) would be **119.99999998** when using gamma instead of the integer 120 (you can obviously round to 120, since it is effectively this number). For a discussion on the gamma function check this link:

http://numbers.computation.free.fr/Constants/Miscellaneous/gammaFunction.html

Supposing the number you wish to find its factorial is located in cell A1, your formula using the gamma function would be:

`=A1*EXP(GAMMALN(A1))`

The last thing to remember is that this is for positive numbers only.

]]>This tip shows you how to use VBA to alternate the row color of an Access report. Many times, when creating a report, we find it hard to read the details. By alternating the row color we can make life much simpler:

1 2 3 4 5 6 7 8 9 10 | Private rowCount As Long Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer) rowCount = rowCount + 1 If rowCount / 2 = CLng(rowCount / 2) Then Me.Detail.BackColor = 16777215 Else Me.Detail.BackColor = 15263976 End If End Sub |