Tags: COUNT

Analysing customer walkin data by date and service taken

{5 Comments}

Assume data is in range A3:E10.  In A4:A10, various service types are mentioned.  In B3:E3, dates are mentioned from June 1, 2012 to June 4, 2012.  In range B4:E10 are numbers representing number of customers.  One may want to answer the following questions from this data:

1. For every date, total number of customer walkins and total number of services taken; and
2. For every date, new customer walkins and new services taken; and
3. For every date, repeat customer walkins and repeat services taken

While the first and third questions are fairly straight forward to solve, some deliberation would be required for the second question.  A new service type taken on June 3, 2012 would be one that has not been taken by any customer from June 1 - 2, 2012.  So if cell A8 has Service type E and cell D8 (data for June 3, 2012) has 3 (3 customer took service type E on June 3, 2012), then this service should be counted only if there is no figure in range B8:C8 i.e. no customer took this service on June 1 - June 2, 2012.

Depending upon the version of MS Excel which you are using, there could be two ways to solve this problem

Solution for MS Excel 2010 and higher versions

If you are using the Power Query add-in and the PowerPivot add-in, then a few simple steps and minimal DAX formulas can solve this problem.  The result will be dynamic and refreshable (just as in a Pivot Table).

Solution for all versions of MS Excel

While this solution works for all versions of MS Excel, it uses an array formula (Ctrl+Shift+Enter).  Array formulas, if used extensively in the workbook, adversely effect the system's performance.

You may refer to my solution in the this workbook.

Shade alternate band of rows in a filtered range

{0 Comments}

Assume the following numbers in range C5:C22.  Heading is in cell C4.

Range C5:C7 - 11
Range C8:C11 - 14
Range C12:C13 - 23
Range C14-C19 - 56
Range C20-C22 - 78

One may want to colour cells for every change in number in range C5:C22 with the following two modifications:

1. Shading should be for alternate band of rows i.e. C5:C7 should be coloured, C8:C11 should not be coloured, C12:C13 should be coloured, C14:C19 should not be coloured and C20:C22 should be coloured.

2. Shading should vary with the range being filtered i.e. If range C5:C22 is filtered to exclude 14 and 56, then numbers 11 and 78 should be coloured i.e. range C5:C7 and C20:C22 should be coloured.

To accomplish this, try the following:

a. Select cell C5 and go to Home > Format > Conditional formatting > New Rule > Use a formula to determine which cells to format

b. In the formula box there, enter the following formula

=AND(ISODD(COUNT(1/FREQUENCY(IF(SUBTOTAL(3,OFFSET(C$5,ROW(C$5:C5)-ROW(C$5),)),MATCH(C$5:C5,C$5:C5,0)),ROW(C$5:C5)-ROW(C$5)))),COUNT(1/FREQUENCY(IF(SUBTOTAL(3,OFFSET(C$5,ROW($C$5:$C$22)-ROW(C$5),)),MATCH($C$5:$C$22,$C$5:$C$22,0)),ROW($C$5:$C$22)-ROW(C$5)))>1)

c. Click on Format and select any Fill colour

d. Click on OK > OK > Apply

e. Copy cell C5, select range C6:C22 and right-click > Paste Special > Formats

With no filter in place, colouring will happen as mentioned in point 1 above.  Auto filter the range now on some values and the colour banding should change.