Microsoft Excel - Defects Trend Analysis in excel

Asked By Satish Kumar on 04-Apr-13 12:07 AM
Hi All,
How to do a Trend Analysis of monthly defects in excel for below data by Supervisor wise



Jan Defects

Feb Defects

Supervisor 1:





Worker 01

2

0


Worker 02

1

1


Worker 03

0

2


Worker 04

5

1

Supervisor 2:





Worker 01

2

2


Worker 02

5

0


Worker 03

9

1

Supervisor 3:





Worker 01

1

4


Worker 02

0

3


Worker 03

2

1


Worker 04

4

2

Supervisor 4:





Worker 01

2

5


Worker 02

1

2


Thank you.
Harry Boughen replied to Satish Kumar on 04-Apr-13 01:36 AM
Hello Satish,
An idea of what sort of output you are looking for would help.
Harry
Satish Kumar replied to Harry Boughen on 04-Apr-13 11:26 PM
Hi Harry,
just looking for forecast the defect rate (for next month) and represent through a chart
Harry Boughen replied to Satish Kumar on 05-Apr-13 12:15 AM
Hello Satish
    Jan Defects Feb Defects
                          1   2    3
Trend        0
Supervisor 1:   8   4
Worker 01   2   0

Worker 02   1   1

Worker 03   0   2

Worker 04   5   1


You could sum the data for each supervisor <=SUM(C5:C8) etc> and number the months and then use the TREND function <=TREND($C4:D4,,E$2)> to make a projection to the next month.  Then simply chart the results.
Not particularly scientific but easy.
Regards
Harry
Note:I can't get the formatting of the example above right.  The TREND formula should be in cell E3.
H