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 »

The trick of ––

You read it right. Its going to be a quick post but at the end of it, you would be glad that you read it.

Trick1: Text to Number

 Consider this
=ISNUMBER(LEFT(12345,1)) – this returns FALSE as this simplifies to =ISNUMBER(“1”)

However, =ISNUMBER(––LEFT(12345,1)) returns TRUE. The two minus signs in conjunction “––” converts text “1” to number 1

So, also =––”1″ equals numeric 1

Trick2: Boolean {TRUE, FALSE} to numeric equivalent {1,0}

Consider,

{=SUM({1,2,3,0,5}<>0)} This is an array formula which reduces to

=SUM({TRUE, TRUE, TRUE, FALSE, TRUE}) and returns a 0

However,

{=SUM(––({1,2,3,0,5}<>0))} correctly puts a 1 for every TRUE and 0 for every FALSE and returns a 4

Check this,

Key in =(1=1) – this returns a TRUE

Now =––(1=1) returns a 1

Before you ask, you will not obtain the same result if you replace –– with +

You may keep this in mind and find a use of it someday 🙂

Read Full Post »

Adding errors, texts, numbers, blanks in a list

A quick post.

Here is how you can you can count errors, blanks, numbers, texts in a list.

List A2:A11 is defined as Count_list

To count

• Errors       {=SUM(IF(ISERROR(Count_list),1,0))}
• Blanks       {=SUM(IF(ISBLANK(Count_list),1,0))}
• Numbers {=SUM(IF(ISNUMBER(Count_list),1,0))}
• Text           {=SUM(IF(ISTEXT(Count_list),1,0))}

The formulas are entered as array formuals with ctrl+shift+enter

Read Full Post »

Sum array with errors

How do you sum up an array which is a combination of numbers and errors? I faced this problem when carrying out a vlookup. The results_array is a combination of #N/A error and numbers as below:

On summing up Results_array through the formula

=sum(A2:A16) gives #N/A error.

To overcome this problem I made use of the following formula

A17  {=SUM(IF(ISNUMBER(A2:A16),(A2:A16),0))}

This is entered as an array formula with ctrl+shift+enter

The formula works in the same way as the results given in explanation array, where

B2=IF(ISNUMBER(A2),A2,0) and then summing up in B17

If you wish to count the occurences of numbers in the array A2:16 you could use this formula

A18 {=SUM(IF(ISNUMBER(A2:A16),1,0))}

Entered as an array formula with ctrl+shift+enter

Now some not so useful information 😉

724 incidentally is a sum of squares of 18 and 20. Try this =18^2+20^2
Or here is a function that you may use = SUMX2PY2(18,20)

Have fun!

 

 

Read Full Post »