Create signal using conditional formatting

[Lolipop]

Hi!

I have question regarding the conditional formatting. How to show signal using conditional formatting icon set?

I'm planning to make a signal when the 3 or 5 value is showing nearest value among them (example in the picture), then the signal will show red flag in C11. The thing is the value is not fixed. So, how can I set the conditional formatting?

Thank you in advance.

 

[kweaver]

Please don't use a picture to display your data. Use XL2BB so that no one has to retype your information.

Also, what do you mean the "3 or 5 value" ?
It would be helpful if you showed some form of expected result(s) when you use XL2BB.

 

[awoohaw]

Welcome to the Mr. Excel forum.
But, I'm unsure of your question. What do you mean by Signal?

Can you use the xl2bb add in (link below) to share a mini workbook?

Please post an example of what "the 3 or 5 value is showing nearest value among them" means?

 

[Lolipop]

Sorry for the inconvenience caused. I cant use XL2BB as there is some problem occurred.
Here is the scenario. I created a graph showing the trend of the data. As you can see the circle in the graph, there is 3 to 5 value/point that show steady line which is a sign that the natural limit need to recalculate in order to achieve the target. So, how can I tell/notify the user that the natural limit need to be recalculate? My idea is I want to put the sign in the signal column to inform user that at certain point, user need to recalculate their natural limit or need to investigate unusual trend. When user see there is red flag in signal column they know what they should do. The question is how to create conditional formatting using icon in signal column? What formula should I use?

FYI, red flag icon represent sign that the natural limit need to be recalculate or need to investigate unusual trend.

 

[awoohaw]

Okay, so you want to know when Column B exceeds some value.
What column is that value in? Why is 2023Q2 flagged but the 2021Q2, 2022Q1-2023Q1 not flagged?

We need more information.

 

[dalvin647]

I created the following:

*excel formulas are in table format. You said 3 to 5 to make a trend. I chose the past 4. Column H calculates the past 4 move averages. And Column J calculates the average deviation from central line.

With that, column C Signal, checks if (Past 4 Move Average/Sales revenue) is less than or equal to 2%, adjust 2% as needed depending on what you think is a small deviation.

Similarly, also checks if deviation from central line is greater than or equal to 3%, adjust 3% as needed depending on what you think is a big enough deviation to investigate.

Good luck!

Test.xlsx
	A	B	C	D	E	F	G	H	I	J
1	Quarter	Sales revenue	Signal	Central	Natural Process Limit	Natural Process Limit Upper	Moving Range	Past 4 Move Average	Past 4 Average	Average Deviation from Central Line
2	2021Q1	1200		1202.35	1050.7	1354	0	0	0	0
3	2021Q2	1239		1202.35	1050.7	1354	39	0	0	0
4	2021Q3	1130		1202.35	1050.7	1354	109	49.33333333	1189.666667	-12.68333333
5	2021Q4	1144		1202.35	1050.7	1354	14	40.5	1178.25	-24.1
6	2022Q1	1234		1202.35	1050.7	1354	90	63	1186.75	-15.6
7	2022Q2	1267		1202.35	1050.7	1354	33	61.5	1193.75	-8.6
8	2022Q3	1262		1202.35	1050.7	1354	5	35.5	1226.75	24.4
9	2022Q4	1256		1202.35	1050.7	1354	6	33.5	1254.75	52.4
10	2023Q1	1245	Investigate	1202.35	1050.7	1354	11	13.75	1257.5	55.15
11	2023Q2	1246	Investigate	1202.35	1050.7	1354	1	5.75	1252.25	49.9
12	2023Q3			1202.35	1050.7	1354	0	4.5	1249	46.65
13	2023Q4			1202.35	1050.7	1354	0	3	1245.5	43.15
Sheet1

Cell Formulas
Range	Formula
C2:C13	C2	=IF(AND(IFERROR([@[Past 4 Move Average]]/[@[Sales revenue]],1)<=0.02,IFERROR(ABS([@[Average Deviation from Central Line]]/[@[Sales revenue]])>=0.03,1)),"Investigate","")
D2:D13	D2	=AVERAGE(Table1[@[Natural Process Limit]:[Natural Process Limit Upper]])
G2:G13	G2	=IF([@[Sales revenue]]<>"",IFERROR(ABS([@[Sales revenue]]-OFFSET([@[Sales revenue]],-1,)),0),0)
H2:H13	H2	=IFERROR(AVERAGE([@[Moving Range]]:OFFSET([@[Moving Range]],-3,)),0)
I2:I13	I2	=IFERROR(AVERAGE([@[Sales revenue]]:OFFSET([@[Sales revenue]],-3,)),0)
J2:J13	J2	=IF([@[Past 4 Average]]=0,0,[@[Past 4 Average]]-[@Central])