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:-
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”
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”.
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.
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.
To avoid errors when using the AND function to combine multiple conditions in Array formulas, follow the below given steps:-
This is the way we can avoid the errors while using the AND function to combine multiple conditions in Array formulas in Microsoft Excel.
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.