MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Min or Max of a Range


Posted by Aaron on December 28, 2001 6:22 AM

Can anyone help me with a Minimum or Maximum of a range that contains #N/A? Ay help would be appreciated.


Posted by Scott on December 28, 2001 6:34 AM

Can you replace the #N/A? If it's a formula, you could use =if(isna(. By using this, you could replace them with either 0 or Blanks.

Posted by Aaron on December 28, 2001 6:45 AM

Unfortunately I need the #n/a for graph data. Is there possibly code that would do the trick. I am trying to ultimately get the min and max of a range for my Y Axes.

Posted by Scott on December 28, 2001 6:50 AM

Ok, then how about using if(isna to replace the na with the actual Text "N/A"? The Min calculation will ignore text.

Posted by Aaron on December 28, 2001 6:56 AM

If you use text in a graph it will graph the text as 0 unfortunately. Any other suggestions?

Posted by Scott on December 28, 2001 7:09 AM

One more idea

What if you used another column to just pull over the numbers (somthing like "=IF(ISNA(E15),"",E15)"), and then ran a min max off of that? then you could still use your original column in the graf?

Posted by Aaron on December 28, 2001 7:29 AM

Re: One more idea

That is a good idea, and I will probably use that. I just thought there might be a function that would do it. Thanks for all of your help What if you used another column to just pull over the numbers (somthing like "=IF(ISNA(E15),"",E15)"), and then ran a min max off of that? then you could still use your original column in the graf? : If you use text in a graph it will graph the text as 0 unfortunately. Any other suggestions?

Posted by Juan Pablo G. on December 28, 2001 7:32 AM

Re: One more idea

How about this array formula ?

{=MIN(IF(ISNUMBER(E1:E100),E1:E100,""))}

Juan Pablo G.

Posted by Mike on December 28, 2001 7:37 AM

Aaron, this should work for your overtime sheet.

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
mymin = Worksheets("Overtime").Range("B5").Value
mymax = Worksheets("Overtime").Range("B5").Value

For x = 2 To 17
For y = 5 To 9
temp = Worksheets("Overtime").Cells(y, x).Text
If LTrim(temp) = "" Then temp = "0"
If (temp <> "#N/A") And (temp <> "0") Then
If Worksheets("Overtime").Cells(y, x).Value > mymax Then mymax = Worksheets("Overtime").Cells(y, x).Value
If Worksheets("Overtime").Cells(y, x).Value < mymin Then mymin = Worksheets("Overtime").Cells(y, x).Value
End If
Next y
Next x

With Worksheets("Overtime").ChartObjects(1).Chart.Axes(xlValue)
.MaximumScale = mymax
.MinimumScale = mymin
End With
End Sub

With this code you won't need those 2 cells anymore to hold the max and min.

-Mike

Posted by Mike on December 28, 2001 7:47 AM

Oops, I forgot about your wanting round numbers at top and bottom of Y scale so add this code just before the with statement:

mymin = 10 * Int(mymin / 10)
mymax = 10 * (Int(maxmax / 10) + 1)

-Mike Aaron, this should work for your overtime sheet. Private Sub Worksheet_Change(ByVal Target As Excel.Range) For x = 2 To 17 For y = 5 To 9 temp = Worksheets("Overtime").Cells(y, x).Text If LTrim(temp) = "" Then temp = "0" If (temp <> "#N/A") And (temp <> "0") Then If Worksheets("Overtime").Cells(y, x).Value > mymax Then mymax = Worksheets("Overtime").Cells(y, x).Value If Worksheets("Overtime").Cells(y, x).Value < mymin Then mymin = Worksheets("Overtime").Cells(y, x).Value End If Next y Next x With Worksheets("Overtime").ChartObjects(1).Chart.Axes(xlValue) .MaximumScale = mymax .MinimumScale = mymin End With

Posted by Aaron on December 28, 2001 8:40 AM

Re: One more idea

I get a #value error How about this array formula ? {=MIN(IF(ISNUMBER(E1:E100),E1:E100,""))} Juan Pablo G.

Posted by IML on December 28, 2001 8:55 AM

Did you hit Control Shift Enter?

For this array formula to work, go to the cell.
Hit F2 to activitate hit. If you typed in the braces delete them. Hold down the control and shift key and finally hit enter.
When done correctly the braces now appear in the formula bar. This should work. I get a #value error : How about this array formula ? : {=MIN(IF(ISNUMBER(E1:E100),E1:E100,""))} : Juan Pablo G.

Posted by Aaron on December 28, 2001 10:06 AM

Re: Did you hit Control Shift Enter?

That solved the Problem. Please forgive my ingnorance. You guys are good.

Posted by Aaron on December 28, 2001 11:30 AM

How can I incorporate that into Code

Is there a way I can write that into code, instead of having my code look at specific cells?