Cell value from a grid

While we can use vlookup or hlookup to return a cell value in a particular row or column. Is there a way we can lookup from both row and column to return a particular value from a cell in a grid?

Consider the case below:

(Yes, I have added more colours to the table this time 🙂 )

The construct is such that every week 5 batches of measurement is carried out. The dates are given in cells A2:A14 and batch numbers are given in cells B1:F1.

Result table for measurement for specific dates and batches is given in the table below:

You could use one of the following formulas to arrive at the result:

Method1: vlookup with match

C17=VLOOKUP(A17,A2:F14,MATCH(B17,A1:F1,0),FALSE)

Of course, you could find the same result with hlookup with match. Only remember to lookup in a row instead of a column

Method2: Index with match

C18=INDEX(B2:F14,MATCH(A18,A2:A14,0),MATCH(B18,B1:F1,0))

Method3: Indirect with address and match

C19=INDIRECT(ADDRESS(MATCH(A19,A2:A14,0)+ROW(A2)-1,MATCH(B19,B1:F1,0)+COLUMN(B1)-1))

And now the bonus formula – using array

Method4: Sum as an array function

C20 {=SUM(($A$2:$A$14=A20)*($B$1:$F$1=B20)*B2:F14)}

Array formula is entered with ctrl+shift+enter

Method5: Use sumproduct as an alternate for array function

C21=SUMPRODUCT(($A$2:$A$14=A21)*($B$1:$F$1=B21)*B2:F14)

Use one of the above formulas or make one for yourself and share with others. Have fun 🙂

Read Full Post »

Sum – Year to Date

The problem here is summing sales figure from the start of the year to a particular month

Sales of 2 products (Item1 and Item2) is given for an entire year – Jan to Dec. The objective is to find cummulative sales till a particular month. For instance the input is Item1 and Jun – where the output should be sum of sales of Item1 from Jan to Jun (highlighted in yellow).

The desired result is given in the table above. I have used offset function to obtain the result. The function is used with multiple width.

The formula used in C6 is =SUM(OFFSET($A$1,MATCH(B6,$A$2:$A$3,0),1,1,MATCH(A6,$B$1:$M$1,0)))

The first match function returns 1, which is number of rows to offset from the reference A1, the second match function returns 6 – position of month Jun in Jan to Dec – which equals the width of column to sum i.e. Jan to Jun.

Note that this is entered as a simple formula and not an array formula.

The formula can be dragged down to obtain result in C7 –  which is summation of sales of Item2 for YTD Mar.

Thanks Bret for highlighting this problem.

Have fun 🙂

 

Read Full Post »

Evaluating step functions

This problem is on step functions. Certain utility company charges me in this manner

For the first 25 units of consumption, the rate is 212 per unit, for next 35 units, the rate is 270 per unit and so on.

I have calculated the total amount payable for certain unit of consumption by using sumproduct and offset functions.

The formula to calculate amount payable is entered in C11 for consumption in B11

C11 =SUMPRODUCT(OFFSET($B$1,1,0,MATCH(B11,Cum_unit,1)),OFFSET($B$1,1,1,MATCH(B11,Cum_unit,1)))+(B11-INDEX(Cum_unit,MATCH(B11,Cum_unit,1)))*INDEX(Rate,MATCH(B11,Cum_unit,1))

The formula can be divided in two parts, the part in blue calculates the amount upto the upto the completed values given in cummulative units (Cum_unit) – 100 in the given example. Cost for remaining units (120-100=20) is calculated in the second part (formula given in green)

Cum_unit is D2:D8

sumproduct – sums up the products of two arrays [sumproduct({1,2},{3,4})=1*3+2*4=11]. This is exactly what we need to calculate the first part – {Units}*{Rate}

To find out how much of the array we need to sumproduct, match function is used. Match with match_type 1 is used for array Cum_unit, so that it returns a value equal to or lesser than the lookup_value. In our case it returns the value 3

Offset is used to generate the arrays for carrying out sumproduct, the reference is taken as B1, for Units array, row offset is 1, column offset is 0, height of array is given by the match function above – which is 3

For Rate array, row offset is 1, column offset is 1, height is same as given by match function.

The cost for remaining part is carried out through the use of index function on the two array Cum_unit and Rate and multiplying them

You would find this very useful when working on large units and rates arrays.

As always let me know if you find this useful 🙂

Read Full Post »

Lookup to the left

For looking up through vlookup, the value to be returned needs to be to the right of lookup_value. Here is a solution for returning a value to the left of lookup_value using match and offset functions

The problem is given in the image sheet below

 

The H1Y1 performance of certain sales people have to be returned. This is achieved through this formula in cell B10

Method 1: Match with Offset

B10 = OFFSET($C$2,MATCH(A10,$C$3:$C$7,0),-2)

This is then dragged to cover all sales people for which figures are required.

Note: match function returns the placement of lookup_value in the column, this is then used to offset. Note that offset reference (C2) is one cell above the lookup column (C3:C7) to ensure that if match returns 1, offset gives a correct value

Method 2: Sum as an array function

B10 {=SUM(($C$3:$C$7=A10)*$A$3:$A$7)}

entered with ctrl+shift+enter

Method 3: Sumproduct

B10=SUMPRODUCT(–($C$3:$C$7=A10),$A$3:$A$7)

May you find this useful!

Read Full Post »

Cows and Bulls

Here is how you can play the game Cows and Bulls in excel

The objective of the game is to guess the secret number (which is hidden from the player). The player can guess the number as many times. If the matching digits are on their right positions, they are “bulls”, if on different positions, they are “cows”. Example given below

The formula for cows:
C3 {=COUNT(MATCH(MID(B3,ROW($1:$4),1),MID($B$1,ROW($1:$4),1),0))}
(entered as an array formula, ctrl+shift+enter)

The formula for bulls
D3 {=SUM(IF(MID($B$1,ROW($1:$4),1)=MID(B3,ROW($1:$4),1),1,0))}
(entered as an array formula, ctrl+shift+enter)

Drag the formula down. You could keep entering your guesses in column B

One could also highlight the cell if the number is guessed correctly, through conditional formatting
Click B3 and through Format>Conditional Formatting…

Copy the format to all cells in column B through Format Painter brush on the toolbar

Tip: If you are playing the game alone and have no one to give you a 4 digit number, you could use rand() function

In the secret number cell enter = ROUND(RAND()*10^4,0), Press F2 and F9 – this would freeze the random number. Make sure that no digits are repeated and the random number is 4 digits long. You can use len function and check duplicate function for that

Have fun playing the game 🙂

Read Full Post »

Multiple extraction through vlookup

The problem here is extraction of multiple results from a two column array through a vlookup

 

The data available is given in columns B and D. The 4 regions along with their major cities are known. The data needs to be arranged as in the table below

Start by sorting on region (column B)

Column A is populated with serial numbers such that each region has a fresh series

Cell A3=1, Cell A4=IF(B4=B3,A3+1,1) – drag till end of column

Column C is concatenation of columns B and A

Cell C3=CONCATENATE(B3,A3) – drag till end of column

This enables us to have a one on one mapping with region and cities

Now, to generate the result table on the right

Cells – G1:J1 is manually populated with numbers 1 through 4

Cell G2=VLOOKUP(CONCATENATE($F2,G$1),$C$3:$D$18,2,FALSE) – drag till end of column and end of row

’tis done!

Alternative solution 1 using offset

To generate the table in F11:G14

Populate F11 to F14 with East, West, North, South

Here again, we would have to sort on region (column B) first

Enter G11 = IF((COLUMN()-COLUMN($G$1))<COUNTIF($B$3:$B$18,$F11),OFFSET($D$3,MATCH($F11,$B$3:$B$18,0)-1+(COLUMN()-COLUMN($G$1)),0,1,1),””)

Here,

column()-column($G$1) generates numbers 0,1 etc

countif – returns number of occurences of east, west, etc

offset – offsets from the reference cell D3, number of rows given by first occurence of east – provided by match function + serial number given by the column function

You could seperate each of the functions to understand how this works. If you need any help in understanding do let me know

 

Drag to the right and below to populate the entire table

’tis done again

Alternative solution 2 using index

To generate the table in F18:G21

Populate F18 to G21 with East, West, North, South

Enter G18 =IF((COLUMN()-COLUMN($G$1))<COUNTIF($B$3:$B$18,$F11),INDEX($D$3:$D$18,MATCH($F11,$B$3:$B$18,0)+(COLUMN()-COLUMN($G$1))))

Drag to the right and down to populate the entire table.

’tis done once again

Read Full Post »