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

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

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

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 »

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

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 »