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″ [...]

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 [...]

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 [...]

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 [...]

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 [...]

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 [...]

Read Full Post »