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 »

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 »

Text to Numbers

Just like that, each alphabet corresponds to a number, summing up alphabets

I cant really see how this post could be useful to you. One of my friends who believed in numerology brought this problem to me of converting names to numbers. Here is the solution that we worked out. You may treat this as a Just Like That entry until it finds any use.

Lets say the names are stored in column A, have worked out a numerical equivalent in column B using the equivalence (A or a =1) and (Z or z = 26) with all other alphabets in between

Method 1: Using CODE function

For windows CODE(text) returns the ANSI code of the first character in the text string.

• code(“A”)=65 and code(“Z”)=90 with all other upper case alphabets in between
• code(“a”)=97 and code(“z”)=122 with all other lower case alphabets in between

The formula in B2 {=SUM(CODE(MID(LOWER(A2),ROW(INDIRECT(“1:”&LEN(A2))),1))-96)}

entered as an array formula with ctrl+shift+enter

Here lower function is used so that all alphabets are treated as lower case and ansi code is from 97 to 122

Hence, 96 is subtracted so that a has numerical equivalent of 1

Cell A4 (Anup Agarwal) has space between the first name and last name. We can use substitute function to replace the space with no space. The revised formula in cell B4 is

B4 {=SUM(CODE(MID(LOWER(SUBSTITUTE(A4,” “,””)),ROW(INDIRECT(“1:”&LEN(SUBSTITUTE(A4,” “,””)))),1))-96)}

again entered as an array formula with ctrl+shift+enter

Method 2: Using lookup function

You can avoid using CODE function and use the lookup function to achieve the same result. Have defined the following table as equivalent

 

The formula entered in B2  {=SUM(LOOKUP(MID(A2,ROW(INDIRECT(“1:”&LEN(A2))),1),equivalent))}

entered as an array formula with ctrl+shift+enter

The formula entered in B4 is

{=SUM(LOOKUP(MID(SUBSTITUTE(A4,” “,””),ROW(INDIRECT(“1:”&LEN(SUBSTITUTE(A4,” “,””)))),1),equivalent))}

entered as an array formula with ctrl+shift+enter

Have fun and let me know if you ever find use of it 🙂

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 »

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 »

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 »