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 »

Handling errors in excel

I have listed down the types of error in excel and their likely causes in the table below:  

 

 Error type can be found out through the formula =Error.Type(cell_ref)

Count instances of error

One of the ways to count the total occurence of error in an area is this

{=SUM(IF(ISERROR(B2:B8),1,0))} entered as an array formula with ctrl+shift+enter

In the above example it returns 7

Replace error with a dash

If a formula gives you an error for certain input parameters and you do not want these to show up on excel sheet, you could use this function

=IF(ISERROR(formula),”-“,formula)

We have used this in creating spaces within a list using indirect function

Let me know if you need any help with these errors. Have fun handling errors 🙂

Read Full Post »

Create spaces within a list

This is a very interesting problem that we encountered.
To simplify the problem, the objective of the exercise was to insert spaces within a continuous list. For example

 

Column A has the original list and column D has the desired result. Two blank cells needed to be inserted between the entries.
To solve this problem, we made use of Indirect function.

Indirect is a lookup function that returns the reference specified by a text string. In the example above

Indirect(B1) returns “cherry” – cell B1 has A1 which contains cherry

and Indirect(B2) returns “microsoft” – cell B2 has A5 which contains microsoft

Here is an interesting variation:

Indirect(“B1”) returns A1 and Indirect(“B2”) returns A5

To solve the original problem

C1=1

C2=C1+1/3 (on dragging, this will insert two fractional numbers between the whole numbers)

D1=IF(ISERROR(INDIRECT(“A”&C1)),””,INDIRECT(“A”&C1))

The indirect function here takes the references as = indirect(“A1”)

You get the desired result when you drag the formulas

It works!

Read Full Post »