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

Read Full Post »

First, Last, Min, Max for a particular value in an array

Consider a set-up where 4 users randomly appear for a test. Their scores are recorded in the order in which they appear for the test. Our objective is to find their scores in the first and last attempts and also their minimum and maximum scores. Here are the users and their scores: The objective is [...]

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 »

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

Read Full Post »

Populate blank rows

Populate blank rows so that they can be filtered or conditionally added For eg. Zone wise sales figure is given but zone is not populated in each row making it difficult to sum sales figures zone-wise Data as given in Table 1 E2 = A2 E3 = IF(ISBLANK(A3),E2,A3) This populates all the blank rows of [...]

Read Full Post »