Tags: ARRAY FORMULA

Create employee wise Effort Utilisation Report

{0 Comments}

Assume a "Labour utilisation" report which shows day wise tasks performed by different employees (row labels are task names, column labels are days and data in the matrix shows employee names).  Each employee can perform multiple tasks on the same day and same task on multiple days.

Given a list of all employees, one may want to answer the following questions:

1. Days worked for each employee - If an employee performs multiple tasks on the same day, then that shall be counted as only 1 day of work

2. Tasks performed across all days - If an employee performs the same task across multiple days, that that shall be counted as only 1 task across all days

You may refer to my solution in this workbook.

You may refer to a similar problem at the following link - "Analysing customer walkin data by date and service taken"

Valuing Closing Stock using FIFO method of Accounting

{24 Comments}

Assume a stock purchase sheet which details the daily stock purchase of various Raw material at various per unit prices.  On another sheet, the closing stock of each Raw material type is entered.  In issuing Raw Material, one may want to follow the First In First Out (FIFO) method accounting which assumes the Raw material issued is from the oldest stock lying in inventory i.e. the inventory left over will be from the recent stock purchased.

The objective is to determine the value of the closing stock of Raw material on FIFO basis.

You may refer to my solution in the attached workbook.

Count word combinations in individual columns of a multi column database

{6 Comments}

Assume a database of five rows and four columns.  Each cell of the database has some text value - let's call this the source grid.  In another part of the same worksheets, the text values of source grid have been arranged as rows and column headings - let's call this the destination grid.

The task is - "To display in the blank cells of the destination grid, total count of row and column heading combinations in each column of the source grid.".  So the counting of row and column heading combinations has to happen in each individual column of the source grid and then all these numbers should be summed up.

The source grid, expected result and formula in individual cells of the destination grid haven been shown in this workbook.

Minimise the total number of inverters to be used for different electricity load factors

{0 Comments}

Assume there are two types of inverters - Inverter1 with a capacity of 15kW and Inverter2 with a capacity of 20kW.  For an electricity load factor of, say 60kW, the objective is to determine the minimum number of inverters to be used.  Therefore the number of inverters which can be used for a load factor of 60kW are:

Option1: All 4 of 15kW; or
Option2: All 3 of 20kW

Since the objective is to mimimise the number of inverts to be used, the result should be all 3 of 20kW.  To take another example, for an electricity load of, say 100kW, the number of inverters which can be used are:

Option1: All 5 of 20kW; or
Option2: 2 of 20kW and 4 of 15kW

The result should be Option1 because the number of total inverters used is minimised there i.e. 5 instead of 6 in Option2.

To acheive this via Excel formulas, try this

1. Enter 15 in cell C2 and 20 in cell C3
2. In range A8:A18, enter different load factors such as 45,50,60,100,75,220,240,90,120,160,275
3. Enter 15 in cell B7 and 20 in cell C7
4. In cell B8, enter the following array formula (Ctrl+Shift+Enter) and copy down till cell B18

=SUMPRODUCT(((INDEX(((({0,1,2,3,4,5,6,7,8,9,10})*$C$2)+(({0;1;2;3;4;5;6;7;8;9;10})*$C$3)=$A8)*({0,1,2,3,4,5,6,7,8,9,10}),,)+INDEX(((({0,1,2,3,4,5,6,7,8,9,10})*$C$2)+(({0;1;2;3;4;5;6;7;8;9;10})*$C$3)=$A8)*({0;1;2;3;4;5;6;7;8;9;10}),,))=MIN(IF((INDEX(((({0,1,2,3,4,5,6,7,8,9,10})*$C$2)+(({0;1;2;3;4;5;6;7;8;9;10})*$C$3)=$A8)*({0,1,2,3,4,5,6,7,8,9,10}),,)+INDEX(((({0,1,2,3,4,5,6,7,8,9,10})*$C$2)+(({0;1;2;3;4;5;6;7;8;9;10})*$C$3)=$A8)*({0;1;2;3;4;5;6;7;8;9;10}),,))>0,(INDEX(((({0,1,2,3,4,5,6,7,8,9,10})*$C$2)+(({0;1;2;3;4;5;6;7;8;9;10})*$C$3)=$A8)*({0,1,2,3,4,5,6,7,8,9,10}),,)+INDEX(((({0,1,2,3,4,5,6,7,8,9,10})*$C$2)+(({0;1;2;3;4;5;6;7;8;9;10})*$C$3)=$A8)*({0;1;2;3;4;5;6;7;8;9;10}),,)))))*({0,1,2,3,4,5,6,7,8,9,10}))

5. In cell C8, enter the following array formula (Ctrl+Shift+Enter) and copy down till cell C18

=SUMPRODUCT(((INDEX(((({0,1,2,3,4,5,6,7,8,9,10})*$C$2)+(({0;1;2;3;4;5;6;7;8;9;10})*$C$3)=$A8)*({0,1,2,3,4,5,6,7,8,9,10}),,)+INDEX(((({0,1,2,3,4,5,6,7,8,9,10})*$C$2)+(({0;1;2;3;4;5;6;7;8;9;10})*$C$3)=$A8)*({0;1;2;3;4;5;6;7;8;9;10}),,))=MIN(IF((INDEX(((({0,1,2,3,4,5,6,7,8,9,10})*$C$2)+(({0;1;2;3;4;5;6;7;8;9;10})*$C$3)=$A8)*({0,1,2,3,4,5,6,7,8,9,10}),,)+INDEX(((({0,1,2,3,4,5,6,7,8,9,10})*$C$2)+(({0;1;2;3;4;5;6;7;8;9;10})*$C$3)=$A8)*({0;1;2;3;4;5;6;7;8;9;10}),,))>0,(INDEX(((({0,1,2,3,4,5,6,7,8,9,10})*$C$2)+(({0;1;2;3;4;5;6;7;8;9;10})*$C$3)=$A8)*({0,1,2,3,4,5,6,7,8,9,10}),,)+INDEX(((({0,1,2,3,4,5,6,7,8,9,10})*$C$2)+(({0;1;2;3;4;5;6;7;8;9;10})*$C$3)=$A8)*({0;1;2;3;4;5;6;7;8;9;10}),,)))))*({0;1;2;3;4;5;6;7;8;9;10}))

For each load factor, the combination of inverters of each capacity will be the minimum number of inverters (of all possible combinations) that can be used.

The assumption in both formulas above is that no more than 10 inverters of each inverter type will ever be used i.e. no more than 10 inverters of 15kW and no more than 10 inverters of 20kW will be used.  This assumption can obviously be relaxed by making minor changes to both formulas.

Determine the maximum number of consecutive 1’s appearing in a range

{19 Comments}

Assume a database where customers are listed from cell A6 down.  From cell B5 to the right months are entered from April to March (B5:M5).  In B6:M6 (Customer 1), a user enters 1's and 0's.  A value of 1 respresents "Cheque bounced" and 0 represents "Cheque honoured".  Similar data is entered for other customers in B7:M500.

One may want to know the maximum number of consecutive "Cheque bounce events" for all customers listed in column A without using spare rows and columns.

In cell N6, enter the following array formula (Ctrl+Shift+Enter)

=IF(MAX(FREQUENCY(IF(B6:M6=1,COLUMN(B6:M6)),IF(B6:M6=0,COLUMN(B6:M6))))=1,0,MAX(FREQUENCY(IF(B6:M6=1,COLUMN(B6:M6)),IF(B6:M6=0,COLUMN(B6:M6)))))

Shade alternate band of rows in a filtered range

{2 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.

Remove duplicates after satisfying additional conditions

{3 Comments}

Assume a two column database of patient ID's and service availed.  One patient may avail the same service multiple times in a year due to which that record may appear as many times as the service is availed.  For e.g., if patient A001 avails the Radiology service twice, then A001 and Radiology will appear in two rows.

Once may want to create the following two reports from this database:

1. A list containing all those records where the patient availed just one service; and
2. A list containing all those records where the patient availed more than one service

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 PowerPivot add-in, then a calculated column formula can resolve this problem.

Solution for all versions of MS Excel

I have shared two solutions here:

1. Array formula and advanced filters; and
2. Only array formulas

For better understanding of the question and to view the final solution, please refer to this workbook.

Dynamically transpose data after ignoring blank cells

{0 Comments}

In range A7:A15, assume the following values A, S , empty cell, empty cell, R, A, W, A, SD.  Starting from cell B17, transpose data to the right (ignoring the blank cells).  Therefore, in range B17:F17, the result should be A, S, R, W SD.

Solution for MS Excel 2010 and higher versions

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

You may watch a short video of my solution here

Solution for all versions of MS Excel

You may refer to my array formula solution.

You may refer to both solutions in this workbook.

Extract number from an alphanumeric string

{72 Comments}

Given an alphanumeric string, one may want to perform the following

Extract phone number from the string

Assume a list of customer addresses with multiple phones numbers mentioned in the address field itself.  These numbers may be mobile numbers and/or mobile numbers.  Furthermore, PIN codes may also be mentioned in the address string.

One may want to extract only the phone numbers to another column.

You may refer to the my solution in this workbook.

Extract one specific 20 digit number from the string

Assume cell descriptions which contains two 20 digit numbers occurring anywhere in the string.  Once may want to extract only that 20 digit number which has the word New before it.

You may refer to my solution in this workbook.