Inspiration needed.... Excel chart visual aid

[nparsons75]

Hi, Im looking for some inspiration. I have a table / spreadsheet that I need to create charts or other forms of vision in order to drive focus to the areas which need it most.

Using the sheet: Excel File

The sheet is basically looking at 7 machines, R1, R2 etc...

Looking at R1 as an example, there are 4 elements, Performance, Quality, Availability and OEE. Each of these have a target (COL AB) and and actual AVG ALL (COL AE) COL AF shows the amount of improvement required to hit the target. Looking at the list I can see a real focus is required on R3 OEE, 30%.

The 3 elements making of the OEE 30% value also show to improve the 30% we need to focus on al 3 elements, 28%, 23% and 24%. If we can work on reducing these values to Zero, the OEE value will improve.

The idea of the sheet is to record the data but also drive focus to where it is needed.

I would like to create some charts or tables from the data to visually drive the focus.

Hope that makes sense..... appreciate any help.

 

[etaf]

the sheet is locked to a user and not public
have a look at XL2BB to post here - link in my signature and on the editing menu

 

[nparsons75]

Ok, XL2BB installed, how do i include the spreadsheet?

 

[nparsons75]

MREXCEL VERSION 1.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH
4		KPI	Jan 20	Feb 20	Mar 20	Apr 20	May 20	Jun 20	Jul 20	Aug 20	Sep 20	Oct 20	Nov 20	Dec 20	JOP	Jan 21	Feb 21	Mar 21	Apr 21	May 21	Jun 21	Jul 21	Aug 21	Sep 21	Oct 21	Nov 21	Dec 21	TARGET	AVG 20	AVG 21	AVG ALL	AIM 0%	KPI	0
5	R1	Performance	94%	78%	68%	73%	84%	91%	72%	65%	81%	87%	76%	80%		75%												82%	79%	75%	79%	5%	Performance	R1
6		Quality	99.4%	100.0%	99.3%	99.5%	100.0%	100.0%	99.0%	99.5%	99.2%	99.0%	99.6%	98.8%		99.4%												100.0%	99.4%	99.4%	99.4%	1%	Quality
7		Availability	78%	76%	79%	80%	83%	75%	82%	80%	86%	70%	80%	74%		69%												79%	79%	69%	78%	1%	Availability
8		OEE	72%	59%	53%	58%	70%	68%	58%	51%	69%	61%	60%	58%		51%												65%	62%	51%	61%	6%	OEE
9	R2	Performance	87%	66%	64%	72%	72%	89%	63%	62%	85%	78%	77%	77%		67%												76%	74%	67%	74%	2%	Performance	R2
10		Quality	100.0%	100.0%	99.8%	99.3%	99.3%	98.8%	99.8%	100.0%	99.5%	99.8%	99.5%	98.4%		100.0%												100%	99.5%	100.0%	99.6%	0%	Quality
11		Availability	72%	79%	79%	79%	76%	75%	78%	83%	86%	76%	78%	74%		76%												82%	78%	76%	78%	5%	Availability
12		OEE	62%	52%	51%	56%	54%	66%	49%	51%	73%	59%	60%	56%		51%												62%	58%	51%	57%	8%	OEE
13	R3	Performance	0%	0%	56%	63%	73%	79%	0%	73%	66%	80%	69%	80%		70%												75%	71%	70%	54%	28%	Performance	R3
14		Quality	0.0%	0.0%	100.0%	100.0%	99.2%	100.0%	0.0%	100.0%	100.0%	99.7%	98.5%	98.3%		99.5%												100%	99.5%	99.5%	76.6%	23%	Quality
15		Availability	0%	0%	91%	89%	74%	84%	0%	80%	93%	84%	65%	78%		72%												82%	82%	72%	62%	24%	Availability
16		OEE	0%	0%	51%	56%	54%	66%	0%	58%	61%	67%	44%	61%		50%												62%	58%	50%	44%	30%	OEE
17	C1	(P) Performance	85%	83%	0%	0%	97%	88%	82%	76%	86%	86%	87%	80%	85%	78%												87%	85%	78%	71%	18%	Performance	C1
18		(Q) Quality	100.0%	100.0%	0.0%	0.0%	100.0%	100.0%	100.0%	100.0%	100.0%	100.0%	100.0%	100.0%	100%	100.0%												100%	100.0%	100.0%	84.6%	15%	Quality
19		(A) Availability	85%	73%	0%	0%	90%	76%	88%	83%	88%	93%	86%	90%	85%	86%												92%	85%	86%	72%	21%	Availability
20		OEE	72%	60%	0%	0%	87%	67%	73%	64%	76%	80%	75%	72%	73%	67%												80%	73%	67%	61%	24%	OEE
21	C2	(P) Performance	91%	79%	76%	95%	88%	87%	78%	76%	85%	83%	86%	80%	84%	0%												89%	84%		77%	14%	Performance	C2
22		(Q) Quality	100.0%	99.1%	100.0%	99.7%	100.0%	100.0%	99.7%	100.0%	99.5%	100.0%	100.0%	100.0%	100%	0.0%												100%	99.8%		92.2%	8%	Quality
23		(A) Availability	82%	66%	94%	85%	86%	77%	87%	83%	93%	90%	89%	42%	81%	0%												87%	81%		75%	14%	Availability
24		OEE	75%	51%	72%	81%	76%	67%	67%	63%	79%	75%	76%	34%	68%	0%												78%	68%		63%	19%	OEE
25	S2	(P) Performance	100%	94%	88%	93%	97%	97%	91%	93%	95%	95%	96%	87%	94%	97%												100%	94%	97%	94%	6%	Performance	S2
26		(Q) Quality	99.7%	98.7%	100.0%	99.5%	100.0%	99.6%	99.5%	99.1%	99.4%	99.6%	98.7%	100.0%	99%	99.6%												100%	99.5%	99.6%	99.5%	1%	Quality
27		(A) Availability	68%	83%	84%	86%	61%	88%	80%	87%	83%	82%	67%	70%	78%	79%												84%	78%	79%	78%	7%	Availability
28		OEE	68%	77%	74%	80%	59%	85%	73%	81%	79%	77%	64%	61%	73%	76%												84%	73%	76%	73%	13%	OEE
29	S2	(P) Performance	100%	90%	94%	96%	97%	100%	87%	94%	96%	93%	97%	93%	95%	97%												100%	95%	97%	95%	5%	Performance	S2
30		(Q) Quality	100.0%	99.1%	98.6%	100.0%	99.5%	99.0%	99.5%	100.0%	99.0%	97.5%	98.1%	100.0%	99%	100.0%												100%	99.2%	100.0%	99.2%	1%	Quality
31		(A) Availability	76%	83%	83%	83%	84%	79%	79%	90%	86%	86%	87%	76%	83%	83%												88%	83%	83%	83%	7%	Availability
32		OEE	76%	75%	78%	80%	81%	78%	69%	85%	82%	79%	83%	71%	78%	80%												88%	78%	80%	78%	12%	OEE
33
34																							Averages					74%	67%	63%	62%
OEE Data Entry

Cell Formulas
Range	Formula
AH4	AH4	=A4:AH8
AB5,AB7,AB9,AB11,AB13,AB15,AB17,AB19,AB21,AB23,AB25,AB27,AB29,AB31	AB5	=MIN(100%,AVERAGE(AC5:AD5)*(1+0.07))
AC5:AC32	AC5	=AVERAGEIF(C5:N5,"<>0")
AD5:AD32	AD5	=IFERROR(AVERAGEIF(P5:AA5,"<>0"),"")
AE5:AE32	AE5	=AVERAGE(C5:N5,P5:AA5)
AF5:AF32	AF5	=MAX(0%,+$AJ$2-AE5/AB5)
AB6,AB10,AB14,AB18,AB22,AB26,AB30	AB6	=MIN(100%,AVERAGE(AC6:AD6)*(1+0.01))
AB8,AB12,AB16,AB20,AB24,AB28,AB32,C32:N32,P32,P28,C28:N28,P24,C24:N24,P20,C20:N20,P16,C16:N16,P12,C12:N12,P8,C8:N8	AB8	=AB5*AB6*AB7
O17:O32	O17	=AVERAGEIF(C17:N17,"<>0")
AB34:AE34	AB34	=AVERAGE(AB32,AB28,AB24,AB20,AB16,AB12,AB8)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B29	Cell Value	=0	text	NO
B31	Cell Value	=0	text	NO
B30	Cell Value	=0	text	NO
B25	Cell Value	=0	text	NO
B27	Cell Value	=0	text	NO
B26	Cell Value	=0	text	NO
B21	Cell Value	=0	text	NO
B23	Cell Value	=0	text	NO
B22	Cell Value	=0	text	NO
B17	Cell Value	=0	text	NO
B19	Cell Value	=0	text	NO
B18	Cell Value	=0	text	NO
AF5:AF32	Cell Value	=0	text	NO
AF5:AF32	Other Type	DataBar		NO
O4	Cell Value	=0	text	NO
O4	Cell Value	=0	text	NO
AE5:AE32	Expression	=AND((AE5-AB5)>=-0.05,(AE5-AB5)<0)	text	YES
AE5:AE32	Expression	=(AE5-AB5)<-0.05	text	YES
AE5:AE32	Expression	=(AE5-AB5)>=0	text	YES
AG29:AG31	Cell Value	=0	text	NO
AG32	Cell Value	=0	text	NO
AG25:AG27	Cell Value	=0	text	NO
AG28	Cell Value	=0	text	NO
AG21:AG23	Cell Value	=0	text	NO
AG24	Cell Value	=0	text	NO
AG17:AG19	Cell Value	=0	text	NO
AG20	Cell Value	=0	text	NO
AG13:AG15	Cell Value	=0	text	NO
AG16	Cell Value	=0	text	NO
AG9:AG11	Cell Value	=0	text	NO
AG12	Cell Value	=0	text	NO
AG4	Cell Value	=0	text	NO
AG5:AG7	Cell Value	=0	text	NO
AG8	Cell Value	=0	text	NO
B32	Cell Value	=0	text	NO
B28	Cell Value	=0	text	NO
B24	Cell Value	=0	text	NO
B20	Cell Value	=0	text	NO
B13:B15	Cell Value	=0	text	NO
B16	Cell Value	=0	text	NO
B9:B11	Cell Value	=0	text	NO
B12	Cell Value	=0	text	NO
B4	Cell Value	=0	text	NO
AE4	Cell Value	=0	text	NO
AE4	Cell Value	=0	text	NO
AD4	Cell Value	=0	text	NO
AD4	Cell Value	=0	text	NO
P4:AC4	Cell Value	=0	text	NO
C4:N4,B5:B7	Cell Value	=0	text	NO
AB4:AC4	Cell Value	=0	text	NO
B8	Cell Value	=0	text	NO

 

[etaf]

i tried copying and transposing the data , then a standard chart for R3
But not really much good

What point are you trying to make , and is it only relevant to the last month , history irrelevant

 

[nparsons75]

Hi Wayne, for your info, we have a project looking into OEE (Overall equipment effectiveness). The idea of the spreadsheet is to basically collate our historical data and use the data to then identify the areas that need to be worked on.

The biggest problem I have at the minute is averages. I cannot find the average (ignoring zeros or blanks). I can do it when covering one range but if I need to add a few ranges in the formula it will not have it, its driving me crazy....

 

[etaf]

The biggest problem I have at the minute is averages. I cannot find the average (ignoring zeros or blanks). I can do it when covering one range but if I need to add a few ranges in the formula it will not have it,

Would you give some examples of the ranges that are giving an issue and what formulas you are using