» 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:
- Quantitative Methods in Derivatives Pricing: An Introduction to Computational Finance
- The Analysis and Use of Financial Statements
- Business Analysis and Valuation: Using Financial Statements, Text and Cases
- Microsoft Excel 2002 Formulas (With CD-ROM)
- Special Edition Using Microsoft Word 2002
- Rich Dad's Guide to Investing: What the Rich Invest in, That the Poor and the Middle Class Do Not!
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