Which Group does a name belong to?

Consider this problem

The table above gives the groups in which each of the members belong. the problem here is to find out in which group one of the member belongs to e.g. Keith (A12). The formula used in B12 is

=INDEX($A$1:$C$1,1,SUMPRODUCT(- –ISNUMBER(SEARCH(A12,$A$2:$C$8)),{1,2,3}*{1;1;1;1;1;1;1}))

The way this works is

• Search function searches for Keith (A12) in the array A2:C8. It returns #VALUE! for cells where Keith is not present and 1 for the cell in which Keith is present
• Isnumber returns True for the cell in which Keith is present and False for the rest
• Operator – – changes True and False to numbers 1 and 0 respectively
• Thus we have an array of 0s and one 1 with 7 rows and 3 columns
• We now generate an array with {1,2,3}*{1;1;1;1;1;1;1}. This array is of the form

• Sumproduct multiplies the earlier generated array of 0 and 1 with our array such that it returns the column number of cell in which Keith is present – 2 in this case
• Index simply returns the equivalent group from the header array A1:C1 – Lions in this case.

Can you figure out a way of not putting the array manually by using the function row and column functions.

Read Full Post »

Lookup for “contains”

Here is a method for looking up “contains” from the lookup value array.
Consider the problem below. I have sales figures for Asia Pacific countries. The lookup value extracted from the system is in the format “Country City”

The expected results are given in the table D1:E11.

Here is the solution using search and sumproduct. You may use array function instead of sumproduct. Suit yourself.

E2=SUMPRODUCT(––ISNUMBER(SEARCH(D2,CountryCity)),Sales)

where, CountryCity is A2:A14 and Sales is B2:B14

You do not need to change the formula while computing for a country figure. The same formula works perfectly.

Now, how does this formula work,

• Search function looks for the lookup city or country in all cells of CountryCity. (You could use Find function but it is case-sensitive). For cells which contains the lookup city or country it returns a number
• Isnumber converts this to True for cells containing the lookup value. Rest are False
• The operator –– converts True to 1 and False to 0 (Refer earlier post)
• Sumproduct multiplies the array of 1’s and 0’s with the Sales array and returns the summation of Sales figure where the “contains” for lookup value is True

Have a great weekend 🙂

Read Full Post »

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 »

Extracting the nth occurence from an array

In one of the earlier posts, I extracted the first, last, min and max occurence from an array for a particular lookup. Refer this post.

The next step perhaps would be to answer if it is possible to extract the second, third occurences and so on. Consequently to extract all occurences for a particular lookup_value.

Consider the same example, as earlier:

Each of the 4 persons randomly appear for a test and their scores are recorded. I had given methods to extract the scores for 1st and last attempt by each of the 4 perons. Now lets find out their scores for 2nd, 3rd, etc attempts

The table above gives the results. Row 1 gives the number of attempt.

The formula used for getting to the result is

F2=IF(COUNTIF(Person,$E2)>=F$1,INDIRECT(ADDRESS(SUMPRODUCT(LARGE((Person=$E2)*ROW($B$2:$B$15),COUNTIF(Person,$E2)+1-F$1)),3)),”-“)

Person is B2:B15

As you would see, I have used sumproduct to avoid using array formula and a combination of indirect and address to extract results.

Here are the steps to understand this formula –

• Lets begin with the component LARGE((Person=$E2)*ROW($B$2:$B$15),COUNTIF(Person,$E2)+1-F$1
• Remember Large function has the syntax LARGE(array,k) which returns the kth largest value from the array. For example if k=2, then the function returns second largest value in the array.
• Now, (Person=$E2)*ROW($B$2:$B$15) would create an array like {0,2,0,0,5,0,0,0,9,0,0,0,0,0,0} which is essentially the row numbers of cells in the array where Person=Mike is true
• For the k part in large function, I have entered COUNTIF(Person,$E2)+1-F$1. Mike occurs 3 times in array Person, so the k would be 3,2,1, such that 1st attempt score is followed by 2nd attempt score and so on
• Once this is clear, rest is easy
• Address function has syntax Address(row_num, column_num, [abs_num]). row_num is given from the large function, column_num is entered as 3, which is the column number of Score, [abs_num] is omitted which returns address in absolute eg $C$2
• Indirect(“$C$2”) returns the value inC2.
• If condition at the beginning simply evaluates if we have exhausted all occurences of Mike. Once done, it returns a “-“.

§ Note: This also is a better way for multiple extraction because sorting of original table is not required. 

There are 3 other alternative formulas given in the earlier post to achieve the same result.

Else, if one method is sufficient for you, use this and 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 »

DPD analysis using two condition sum

Days past due analysis, two condition sum, bucketing data, frequency function

Here I have carried out a days past due (DPD) analysis. This requires organizing data in various days bucket and makes use of conditional sum function.

14 cases are presented. Each case has a days past due and a corresponding amount. The objective is to put these numbers in various previously identified buckets

The days past due is defined as (0,1] which means 0<DPD<=1 and so on. This is re-written as in column F.

To calculate the amount in column G the following formulas are used, where DPD is the array B2:B15

G2 {=SUM(IF((DPD>0)*(DPD<=$F2),C$2:C$15,0))}

G3 {=SUM(IF((DPD>$F2)*(DPD<=$F3),C$2:C$15,0))} which can then be dragged till end of column

Both entered as array formula with ctrl+shift+enter

To count occurences in each DPD bucket the following formulas are used

H2 {=SUM(IF((DPD>0)*(DPD<=$F2),1,0))}

H3 {=SUM(IF((DPD>$F2)*(DPD<=$F3),1,0))} which can then be dragged to end of column

You could also use the frequency function for counting occurences

Enter formula in I2 =FREQUENCY(DPD,F2:F11)

Select I2 to I11, press F2 followed by ctrl+shift+enter

Note that the two conditions entered for summing is akin to using SUMIF with criteria as AND(cond1, cond2). However, AND criteria does not work with SUMIF.

Also one could use nested if to achieve the same result. The above format simplifies nested if through using the following logic

TRUE*TRUE=TRUE

All other permutations give a FALSE result, which is exactly a two condition AND criteria.

Have fun figuring this out 😉

Read Full Post »

Lookup_value as array

lookup_value as array, why lookup over vlookup, multiple lookup_value in single lookup function, sum(lookup)

In one of my earlier posts, I had illustrated passing an array for col_index_num in vlookup. Refer Extract and sum vlookup results.

We now look at the possibility of passing an array for lookup_value in vlookup. Refer the example below:

If we were required to sum the sale of Prod3 for Angie, Mike and Ross (cells highlighted in yellow), can we do it through a single vlookup function. Lets say an array is defined as {Ross, Mike, Angie} as salesperson.

This vlookup function

{=SUM(VLOOKUP(salesperson,A3:D7,4,FALSE))} entered as an array with ctrl+shift+enter gives the result as 3 – which corresponds to sale of Prod3 by Ross (first element in the array salesperson). So clearly the array function is not working!

Lets now try the lookup function. Revise the above formula as

{=SUM(LOOKUP(salesperson,A3:D7))}

OR

{=SUM(LOOKUP(salesperson,A3:A7,D3:D7))}

which depends on your comfort with the two syntax for lookup

• LOOKUP(lookup_value,array)
• LOOKUP(lookup_value,lookup_vector,result_vector)

entered as an array formula with ctrl+shift+enter

The formula works and it correctly returns 114

The point to notice here is that lookup_array needs to be in ascending order.

Have fun. This is only the second time that I have used lookup function. It also explains why in the previous post – Text to numbers – method 2, I have used lookup and not vlookup.

Have fun 🙂

Read Full Post »

Converting numbers to words

How do we convert numbers to words. Especially useful if you are writing cheques (checks) through printing through a soft file.

The logic given below is one for India – it uses crores and lacs. The logic works for all numbers between 1 and 99,99,99,999 (100 crore -1)

Say the numbers that need to be converted to words are given in column A

Step 1: Start with defining the following arrays

Step2: Extract units, tens, hundreds, thousands, lacs and crores from the numbers in column A

The result is given Array1 below:

The formulas used are:

For crores: D2=ROUNDDOWN($A2,-7)

For lacs: E2=ROUNDDOWN($A2,-5)-$D2

For thousands: F2=ROUNDDOWN($A2,-3)-SUM($D2:E2)

For hundreds: G2=ROUNDDOWN($A2,-2)-SUM($D2:F2)

For tens & units: H2=$A2-SUM($D2:G2)

Drag till end of columns

Modify the Array1 so that resuts appear as this in Array2:

The formulas used are:

For crores: I2=D2/10^7

For lacs: J2=E2/10^5

For thousands: K2=F2/10^3

For hundreds: L2=G2/10^2

For tens+units: M2=H2

Step 3: Convert numbers to words for each column

The formulas used are:

Crores: N2=IF(LEN(I2)=1,VLOOKUP(I2,Unit_array,2,FALSE),IF(VALUE(LEFT(I2,1))=1,VLOOKUP(I2,teens,2,FALSE),CONCATENATE(VLOOKUP(VALUE(LEFT(I2,1)),tens,2,FALSE),” “,VLOOKUP(VALUE(RIGHT(I2,1)),Unit_array,2,FALSE))))

Lacs: O2=IF(LEN(J2)=1,VLOOKUP(J2,Unit_array,2,FALSE),IF(VALUE(LEFT(J2,1))=1,VLOOKUP(J2,teens,2,FALSE),CONCATENATE(VLOOKUP(VALUE(LEFT(J2,1)),tens,2,FALSE),” “,VLOOKUP(VALUE(RIGHT(J2,1)),Unit_array,2,FALSE))))

Thousands: P2=IF(LEN(K2)=1,VLOOKUP(K2,Unit_array,2,FALSE),IF(VALUE(LEFT(K2,1))=1,VLOOKUP(K2,teens,2,FALSE),CONCATENATE(VLOOKUP(VALUE(LEFT(K2,1)),tens,2,FALSE),” “,VLOOKUP(VALUE(RIGHT(K2,1)),Unit_array,2,FALSE))))

Hundreds: Q2=VLOOKUP(L2,Unit_array,2,FALSE)

Tens&Units: R2=IF(LEN(M2)=1,VLOOKUP(M2,Unit_array,2,FALSE),IF(VALUE(LEFT(M2,1))=1,VLOOKUP(M2,teens,2,FALSE),CONCATENATE(VLOOKUP(VALUE(LEFT(M2,1)),tens,2,FALSE),” “,VLOOKUP(VALUE(RIGHT(M2,1)),Unit_array,2,FALSE))))

Formula for crores, lacs, thousands, tens&units are similar in logic and construct

The logic used is

• If number is of 1 digit (0 to 9)- then lookup value from Unit_array
• If number starts with 1 (10 to 19) – then lookup value from teens
• For all else (20 to 99) – lookup left digit from tens and right digit from Unit_array and concatenate

Formula for hundreds is simple – lookup value from Unit_array as hundreds can only be from 0 to 9

Step 4: Now the fun part – putting it all together

The formula is simply

B2=PROPER(CONCATENATE(IF(I2>0,N2&” crores “,””),IF(J2>0,O2&” lacs “,””),IF(K2>0,P2&” thousand “,””),IF(L2>0,Q2&” hundred “,””),IF(R2>0,R2,””)))

Have added proper to capitalize the first letters of all words in the text string.

Let me know if it helps you 🙂

Read Full Post »

Extract and sum vlookup results

multiple vlookup, sum(vlookup), sum vlookup results in a single formula

Consider the problem as below:
The table gives the sales figures for each of the sales person across five products

Say we need to find the total sales for identified salesperson across 3 products – Prod1, Prod2 and Prod5
One way to do this is to carry out vlookup thrice and sum them up. Here is the same solution but with a single vlookup entered as an array formula.

 

 

The formula in cell B10 is {=SUM(VLOOKUP(A10,A3:F7,{2,3,6},FALSE))} entered as an array formula with ctrl+shift+enter

The formula carries out vlookup for each of the col_index_num in the array viz. 2,3 and 6 and then sums the result.

Let me know if it ever speeds up your task 🙂

Read Full Post »