Monthly Archives: January 2014

Excel Irregular Banding on a Filtered List

Yesterday I was looking up some information about Excel banding on the Net, and I came across this article on Dick Kusleika’s web site.

http://dailydoseofexcel.com/archives/2006/06/30/irregular-color-banding/

Although he had cited me when he used a formula I created about 20 years ago as part of his banding solution, I never saw the article because he had misspelled my name (Hagar instead of Hager) so it never came up in a search.

=ROUND(MOD(SUM(1/COUNTIF($A$3:A3,$A$3:A3)),2),2)=1

Anyway, I thought that his formula was very cool and it got me to thinking about how the same feature might be applied to a filtered list. I quickly realized that another formula for counting unique items would have to be used, since the “range” argument in the COUNTIF function has to be an actual range and cannot be an array. Luckily, I was able to remember that I had created another formula long ago to return unique items and this formula could accept arrays. Furthermore, I had already adapted this formula to work with a filtered list by using the SUBTOTAL formula invented by Laurent Longre shown at the link below (my old EEE publication on John Walkenbach’s site).

http://spreadsheetpage.com/index.php/eee/issue_no_20_july_8_2001/

So, I modified the SUBTOTAL formula shown below to include expanding ranges as per Dick’s formula.

define filtRange as:

=IF(SUBTOTAL(3,OFFSET($B$2:$B2,ROW($B$2:$B2)-MIN(ROW($B$2:$B2)),,1)),$B$2:$B2,””)

(where column B contains sorted information organized in blocks)

and replaced the “unique formula” used in his formula with one from the EEE article to come up with a truly powerful formula.

=ROUND(MOD(SUM(N(IF(ISNA(MATCH(“”,filtRange,0)),MATCH($B$2:$B2,$B$2:$B2,0),IF(MATCH(filtRange,filtRange,0)=MATCH(“”,filtRange,0),0,MATCH(filtRange,filtRange,0)))=ROW($B$2:$B2)-MIN(ROW($B$2:$B2))+1)),2),2)=1

Caveat: In fact, it is so powerful that it takes up an enormous amount of computing power when used on a large list, so use it with care.

It can be used directly as a conditional formatting formula or added as an additional column in the list, which would then be used with the conditional format (i.e. – =$D2).

As an example, the figure below would be the result before filtering:

Pic1_xlBanding

and the next figure would be the result after filtering (5 & 7 filtered from result column):

Pic2_xlBanding

I hope that this provides another useful bullet to your Excel formatting arsenal.

Scaling in-cell charts with an Excel formula

 

You can scale   “in-cell charts” by using the following formula:

where:

data_point is the relative reference of   the 1st point in the data_range

data_range is the absolute horizontal   reference of the data

this_cell is the relative reference of   the cell containing the formula

 

Formula is not completely volatile, so   you must press F9 after adjusting column widths.

Scaling is not perfect (zooming affects it) but works OK.

=REPT(“|”,(data_point*100/MAX(data_range))*CELL(“width”,this_cell)/(14+NOW()*0))

An Oldie But A Goodie

Here is an oldie but a goodie to start the New Year. If you still use xlm functions in your apps, you can use this code to run them in VBA. Happy New Year!

 

Function RunXLM(fname As String, bBuiltIn As Boolean, ParamArray xArg())

 

    Dim n As Integer

    Dim num_arguments As Integer

 

    num_arguments = UBound(xArg)

   

    With Application

 

    If bBuiltIn Then

 

        Select Case num_arguments + 1

        Case 0

            RunXLM = .ExecuteExcel4Macro(fname)

        Case 1

            fname = Mid(fname, 1, Len(fname) – 1) & “,”

            RunXLM = .ExecuteExcel4Macro(fname & xArg(0) & “)”)

        Case 2

            fname = Mid(fname, 1, Len(fname) – 1) & “,”

            RunXLM = .ExecuteExcel4Macro(fname & xArg(0) & “,” & xArg(1) & “)”)

        Case 3

            fname = Mid(fname, 1, Len(fname) – 1) & “,”

            RunXLM = .ExecuteExcel4Macro(fname & xArg(0) & “,” & xArg(1) & “,” & xArg(2) & “)”)

        Case Else

            RunXLM = “”

        End Select

 

    Else

 

        Select Case num_arguments + 1

        Case 0

            RunXLM = .Run(fname)

        Case 1

            RunXLM = .Run(fname, xArg(0))

        Case 2

            RunXLM = .Run(fname, xArg(0), xArg(1))

        Case 3

            RunXLM = .Run(fname, xArg(0), xArg(1), xArg(2))

                                              

        Case 4

            RunXLM = .Run(fname, xArg(0), xArg(1), xArg(2), xArg(3))

                                              

        Case 5

            RunXLM = .Run(fname, xArg(0), xArg(1), xArg(2), xArg(3), xArg(4))

                                              

        Case Else

            RunXLM = “”

        End Select

 

    End If

   

    End With

 

End Function