Min or Max of a Range

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.

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.

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

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

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?

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?

Re: One more idea

How about this array formula ?

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

Juan Pablo G.

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

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

Re: One more idea

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

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.

Re: Did you hit Control Shift Enter?

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

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?