As well, this is such a flexible tool, only really limited by your creativity. I'm sure there are limits somewhere on the number of conditionals within an array but I've never run into a situation where I ran out. Since #DIV/0 is not a number, it will sum 4, 5, 6 for an answer of 15. So, this looks across the array B2:B5 and will only include numbers. You will get a divide by zero error that will replace the value 10. The answer is 4.5 because it will average 4 and 5. (note that "Apple" and "Pear" can be replaced with cell references). So, if (A2:A5 is not missing OR C2:C5 is not missing) AND B2:B5 is greater than 4, the answer is 7 because it will take the average of 5, 6, and 10. You can also filter by values within the target column: for example: =AVERAGE(IF(((A2:A5"")+(C2:C5""))*(B2:B5>4),B2:B5)) The permission value can be anywhere else on your spreadsheet and does not have to be contained in an array. So, if some value gives permission, then run the array, otherwise return "N/A". ![]() So, in this case the answer is 6.25, because all of the values in B have a non-missing value in A or C.ĪVERAGE can be replaced by a number of other functions such as MAX, MIN, SUM, COUNT, and you can put conditionals in front of the array: e.g., =IF(B2>3,AVERAGE(IF((A2:A5"")+(C2:C5""),B2:B5)),"N/A")Įnter as an array formula. 5 and 10 are excluded because values are missing in A3 and C4. In this case, the result is 5 (average of 4 and 6). So, that formula says, If A2:A5 is not missing and C2:C5 is not missing, then return the average of B2:B5. ![]() In that formula "" stands for missing, means not equal to, and the * means AND. Your formula will look like this: īut do not type the curly braces they will appear automatically If you don't know array formulas yet, here's a example: Data I find array formulas in Excel very useful for solving a lot of these kinds of problems.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |