There is problem with average for some specific values in a table. We need to make average of the items that meet several criteria. Following table represents data of some worldwide company. You can see branch offices in the cities and number of their new customers per each month and year.

There is a problem with average for some specific value in a table. Following table represents data of some worldwide company. You can see branch offices in the cities and number of their new customers per each month.

In some cases it is useful to know the address of the first empty cell in a row. This is a way to do this without macros. Write the following formula and press CTRL+SHIFT+ENTER. Excel adds braces { } and saves it as array formula.

In some cases it is useful to know the address of the first empty cell in a column. This is a way to do this without macros. Write the following formula and press CTRL+SHIFT+ENTER. Excel adds braces { } and saves it as array formula.

There are some stuff dealers. In the following table is list of their sales. We need to know, how many sales they made from the beginning of the year. But there is a problem, we need to know sum to the date in each record for the dealer. For example: Jack Smith sold 1 piece on 2011/01/07 and then 3 pieces on 2011/01/19. So total to 2011/01/07 is 1, but total to 2011/01/19 is 4 (1+3).

I prepared two ways to solve this problem. The first uses values in a new column and the second uses array formula. Both have their advantages.

Example

Table in the picture below contains information about the success of students making 3 exams. Value “Yes” means he passed, value “No” means he did not pass. Student should have value “Yes” in each column to pass all exams.