How to use "INDIRECT" function

Lets say we have production log sheet for technicians to put a "x" in the relevant cell, as shown below. Technicians assigned working on the particular date enters a cross "x" in the corresponding row and corresponding column (his/her name).

Then we need to get all the names in column C. This will be very help full in many ways. 

Use formula in cell C2 and populate as needed. 

=INDIRECT(ADDRESS(2,MATCH("x",D3:K3,0)+COLUMN()))

Lets analyse the formula, 

1. INDIRECT needs the cell reference.

2. To get the reference cell I am using ADDRESS function. Address function requires row number and column number.

3. Row number is 2, as that is the contains name of the technicians.

4. Column number is bit complicated. Use MATCH function to find the column number within the selected range (in this example D3:K3). Use COLUMN function to find the column number where the result to be displayed (column C in this example).  

MATCH("x",D3:K3,0)+COLUMN()

The result will be like image below,

Comments are welcome.

How to use AVERAGEIF and AVERAGEIFS

In the earlier posts we have looked at using SUMIF, SUMIFS, COUTIF AND COUNTIFS. In this post lets see how to use AVERAGEIF and AVERAGEIFS, 

Lets look at the following example:

Let say I need to calculate the average quantity produced for each day.

First make a list of dates (unique).

=AVERAGEIF(E:E,A3,G:G)

Extend the formula as needed.

Let say i need the average by machine (1 to 9) and date then i need to use AVERAGEIFS. 

The results will be,

The formula is 

=AVERAGEIFS($U:$U,$S:$S,$A3,$T:$T,B$2)

Please note the quantity is in column U, dates in column S and machine number in column T.

I have added IFERROR function to get rid of #DIV/0! error

=IFERROR(AVERAGEIFS($U:$U,$S:$S,$A3,$T:$T,B$2),"")

Please leave your comments, thanks.