» 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:
- Real Estate Loopholes: Secrets of Successful Real Estate Investing
- Microsoft Excel 2002 Visual Basic for Applications Step by Step
- Windows XP Pocket Reference
- Not-for-Profit Accounting Made Easy
- The Basics of Finance: Financial Tools for Non Financial Managers
- Microsoft Excel 2002 Formulas (With CD-ROM)
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