» 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:
- Monte Carlo Methods in Finance
- Rich Dad's Guide to Investing: What the Rich Invest in, That the Poor and the Middle Class Do Not!
- The McGraw-Hill 36-Hour Course in Finance for Nonfinancial Managers
- The Essential 55: An Award-Winning Educator's Rules for Discovering the Successful Student in Every Child
- Who Moved My Cheese? An Amazing Way to Deal with Change in Your Work and in Your Life
- Special Edition Using Microsoft Excel 2002
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