Array Formula in Microsoft Excel 2010

To avoid the errors while using the function to combine multiple conditions in Array formulas, we use “AVERAGE”, “IF”, “ISNUMBER”, and “AND” function in Microsoft Excel 2010.

Average:- This function is used for returning the average (arithmetic means) of its arguments that can be numbers or names, arrays or reference that contains numbers.

The syntax of AVERAGE formula:-  =AVERAGE(number1,number2,……)

Let’s take an example to understand how the Average formula performs.

We have a list in the range A2:A11. Now, we want to see the average number in the list.

Follow the below given steps:-

  • Select the cell B2, and write the formula in the cell.
  • =AVERAGE(A2:A11)
  • Press Enter on the keyboard.
  • The function will return the 34, which is the average of the range.

 

 

img1

 

ISNUMBER: This function can be used to check if a cell contains a number.

Syntax of “ISTEXT” function:       =ISTEXT (value)

Example: Cell A2 contains the text 123

                        =ISNUMBER (A2) and then function will return “TRUE”

               Cell A3 contains the number “XYZ”

                        =ISNUMBER (A3) and then function will return “FALSE”

 

img2

 

IF: - Check whether a condition is met and returns one value if True and another value if False.

The syntax of “IF” function =if(logical test,[value_if_true],[value_if_false])

First the formula will do the logical test, what if the output of logical test is true and false.

For example: Cell A2 and A3 contain the number 3 and 5. If the number is 3, the formula should display “Yes”, else “No”.

=IF (A1=3,”Yes”,”No”) 

 

img3

 

AND:- This function is used to check whether all the arguments are true and returns TRUE if all the arguments are TRUE.

The syntax of “AND” function =AND(logical1,[logical2],….)

For example: Cell A1 contains student name, B1 contains 50, and we need to check the criteria.

  • If the cell contains the number more than 40 and less than 60.
  • Write the formula in cell C1
  • =AND(B2>40,B2<60), press Enter.
  • The function will return True.
  • If we will change the number less than 40 and greater than 60, the function will return False. 

 

img4

 

Let’s take an example to understand how we can avoid the errors when using the And function to combine multiple conditions in Array formula.

We have data in range A2:B10. Column A contains the list of number 1 and column B contains number 2.

 

img5

 

To avoid errors when using the AND function to combine multiple conditions in Array formulas, follow the below given steps:-

 

  • Write the formula in cell C2.
  • =AVERAGE(IF(ISNUMBER(A2:A10)*ISNUMBER(B2:B10),A2:A10-B2:B10))
  • Press Ctrl+Shift+Enter on your keyboard.
  • After pressing the Ctrl+Shift+Enter, function will look like this {=AVERAGE(IF(ISNUMBER(A2:A10)*ISNUMBER(B2:B10),A2:A10-B2:B10))}
  • The function will return the average difference between each pair of values in Column A and B.

 

img6

 

This is the way we can avoid the errors while using the AND function to combine multiple conditions in Array formulas in Microsoft Excel.
 
 

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

Terms and Conditions of use

The applications/code on this site are distributed as is and without warranties or liability. In no event shall the owner of the copyrights, or the authors of the applications/code be liable for any loss of profit, any problems or any damage resulting from the use or evaluation of the applications/code.

Visit Us On TwitterVisit Us On FacebookVisit Us On Google PlusVisit Us On Youtube