Blankety blank, blankety blank...

To make a cell blank when zero in Excel you could do so via conditional formatting, setting the font colour to be the same as the background colour when it equals zero.

This technique is a bit limited however, and hits problems if you need to use other conditional formatting or when the background colour varies from cell to cell.

Another more flexible method is to use the following formatting string for your cell.
Just go to Format > Cells > Custom and plonk it in to the Type box.

0;-0;;@

The above format doesn't show a thousands delimiter. To get that in use: -

#,##0;-0;;@

Why Microsoft made this expression so bloody complex I'll never know. But hey, Microsoft are Microsoft, they'd make the complete works of Shakespeare out of a crisis.

Have a play with varients of this for other effects.

Enjoy!

Things You Do In Your Lunch Break - How to get the Ratio between two numbers in Excel

A while ago I was asked how to get the ratio between two numbers in Excel. One lunchbreak and two VBA functions later, this is what I came up with.

I ended up creating two functions. One returns the highest common denominator for two numbers HighDenom and the second, Ratio, returns a string of the ratio between two numbers (e.g. 5:1).

First off you need to add a couple of functions to your spreadsheet...

1. Open the workbook you're working on

2. Go into VBA (Alt+F11)

3. Insert a new module (Insert > Module)

4. Paste the following 2 functions into your module: -

Public Function HighDenom(intNum1 As Integer, intNum2 AsInteger) As Integer

' This procedure returns the Highest common denominator fortwo numbers.

If intNum1 > intNum2 Then
intMax = intNum1
Else intMax = intNum2
End If

For i = intMax To 1 Step -1
If (intNum1 / i = Int(intNum1 / i) And (intNum2 / i =Int(intNum2 / i))) Then
HighDenom = i
Exit Function
End If
Next

End Function

Public Function Ratio(intNum1 As Integer, intNum2 AsInteger) As String

' This procedure returns the Highest common denominator fortwo numbers.

intHD = HighDenom(intNum1, intNum2)
intDiv1 = intNum1 / intHDintDiv2 = intNum2 / intHD
Ratio = intDiv1 & ":" & intDiv2
End Function

5. Get out of VBA (Press Alt+Q)

6. Use the functions (They will appear in the Paste Function dialogbox (Shift+F3) under the "User Defined" category.