How to set excel formula alert on amount in minimum and maximum

[gc7sdu]

Hi,

how to set excel formula alert on figures reached in minimum or maximum? example sales reached near min or reach maximum target.

Thank you

 

[gc7sdu]

If you want the alert to be just text in a cell, try this:

Book6
	A	B	C	D	E
1	Start Poor	End Poor		Start Exc.	End Exc.
2	5000	6000		8000	10000
3
4	Sales	Alert
5	3000
Sales

Cell Formulas
Range	Formula
B5	B5	=IF(AND(A5>=A2,A5<=B2),"Poor",IF(AND(A5>=D2,A5<=E2),"Excellent",""))

Hi Legendary,

Thanks for your advise. if the figures fall in between at 6001 to 7999 will be stated as "Average". May i know how to add in this in between?

 

[Cubist]

It does not make sense to have a 5000-6000. That doesn't cover 0-5000.
Similarly, 8000-12000 doesn't cover any sales that is more than 120000. Here's my suggestion.

Book1
	A	B	C	D	E
1	Start	Status		Sales	Alert
2	0	Fail		6001	Average
3	6000	Average
4	8000	Success
Sheet2

Cell Formulas
Range	Formula
E2	E2	=XLOOKUP(D2,A2:A4,B2:B4,,-1)

 

[gc7sdu]

It does not make sense to have a 5000-6000. That doesn't cover 0-5000.
Similarly, 8000-12000 doesn't cover any sales that is more than 120000. Here's my suggestion.

Book1
	A	B	C	D	E
1	Start	Status		Sales	Alert
2	0	Fail		6001	Average
3	6000	Average
4	8000	Success
Sheet2

Cell Formulas
Range	Formula
E2	E2	=XLOOKUP(D2,A2:A4,B2:B4,,-1)

Hi Cubist,

Understand your concern. i just want to set alert on those sales doing nearest at min and max levels. Appreciate you can guide me to add in "Average" alert in previous formula.

B5	B5	=IF(AND(A5>=A2,A5<=B2),"Poor",IF(AND(A5>=D2,A5<=E2),"Excellent",""))

[th]

Cell Formulas​

[/th]​

[th]

Range​

[/th][th]

Formula​

[/th]​

Thanks

 

[Cubist]

Understand your concern. i just want to set alert on those sales doing nearest at min and max levels. Appreciate you can guide me to add in "Average" alert in previous formula

Did you try my formula?

 

[gc7sdu]

Did you try my formula?

Hi Cubist,

Yes i did tried. By the way, my sales having 2 grades if hit Grade B for example at 13000, then the status will turn up to another row as Min level. which mean status in Grade A will disappearing. Looking your kind advise. Thanks

figures	status	sale	alert
0	Fail	8001	Success
6000	Average
8000	Success
Grade	Min	Average	Max	Actual Sales	Status
A	5000-6000	6001-7999	8000-12000	8300	Success
B	12000-15000	15001-16999	17000-20000