» Indicators for the maximal and minimal values
CATEGORY - Excel Formula and Function
VERSION - All Microsoft Excel Versions
Creating a formula that will indicate the maximum and minimum values in List1
by returning ""Max Value"" or ""Min Value"" in the cell next to them.
Solution:
Enter the following IF statement, using the MAX and MIN functions, in column B:
=IF(A2=MAX($A$2:$A$8),""Max Value"",IF(A2=MIN($A$2:$A$8),""Min Value"",""""))
List1____Formula
2
40______Max Value
12
4
7
0_______Min Value
30
Book Store:
Recommended Books:
- Microsoft PowerPoint Version 2002 Step by Step
- The 22 Immutable Laws of Marketing : Exposed and Explained by the World's Two
- Infectious Greed: How Deceit and Risk Corrupted the Financial Markets
- Business Plans For Dummies®
- Adventure Capitalist: The Ultimate Road Trip
- The 22 Immutable Laws of Branding
Double Quotes
whisperer
Hi Rubin,I felt that I had to pass comment about the use of double quotes in your last tip as they will throw up an error. The web site version above, although different, still throws up an error.
The minimal/maximal formula should be expressed thus:
=IF(A2=MAX($A$2:$A$8),"Max Value",IF(A2=MIN($A$2:$A$8),"Min Value",""))
Keep up the good work!
GT
using conditional formatting
dhouse
A variation using conditional formatting to indicate maxima and minima is also possible:
=A2=MAX($A$2:$A$8) [condition 1]
=A2=MIN($A$2:$A$8) [condition 2]
DH
Making its simpler
Hani Kashalo
I suggest using names for the list of nombers, this will be more easier. the formula will be as follows (I use "range" word as a name to the list):
=IF(C4=MAX(range),"Max Value",IF(C4=MIN(range),"Min Value",""))
BR
I felt that I had to pass comment about the use of double quotes in your last tip as they will throw up an error. The minimal/maximal formula should be expressed thus:
=IF(A2=MAX($A$2:$A$8),"Max Value",IF(A2=MIN($A$2:$A$8),"Min Value",""))
Keep up the good work!
GT