Preparing Data for Stacked Column Chart in Excel

I recently had a situation where I have part measured repeatedly after rework. The number of times the each parts measured and which  measurement belongs to which rework is only can be identified by "time stamp" (the time of the measurement). Please refer to the sample data. Please note the data is sort by time stamp and part name. 

Now I need to a Stacked Column Chart like below. This is to have visual representation of the effect of rework. My original intention to use Qliksense or Tableau but excel can do the job with an additional table. 

Step 1: First sort the table by part and then by time stamp. 

Step 2: Add a column (column D) with the formula

="Measurement No" & COUNTIF(B$2:B2,B2)

Step 3: Drag the formula all the way.

Step 4: Now you can create the table for the Stacked Column Chart.

The table should be like the image below.

Add formula =SUMIFS($C:$C,$B:$B,J$1,$D:$D,$I2) in cell J2.

Fill the table with the formula.

Use insert → chart → stacked column to create the chart.

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.