Min or Max Function ignoring values

[Dan Kennedy]

Many thanks to all of you for the help.

I have attached a sample spreadsheet of the data I am working with. I want to find the min and max values of this column of data but only looking at the numbers between 0 & 1. I can't quite figure out the correct code to make it work, I keep getting NA values or Value errors.

Thank you,

dan

 

[Chihiro]

How are you getting the data? Is it copy and paste or brought in by formula?

The reason you are having such a issue, is because of #N/A texts in range.

 

[Chihiro]

You can work around it using...
=MIN(IFERROR(IF((C3:C1442>=0)*(C3:C1442<=1),C3:C1442),""))

Confirmed as Array (CSE).

But I'd recommend getting rid of #N/A errors before you do the calculation.

 

[Dan Kennedy]

The #N/A is entered off the server I am querying for the data, the data comes in as an array and I cannot modify it easily. Each time the user changes the input it will either generate more or remove the #N/A values.

I have not tried your formula yet, but will that work for a named range formula?

Thank you,

 

[John Jairo V]

Hi to all!

You can use:

For min:
=AGGREGATE(15,6,C3:C1442/(C3:C1442>=0)/(C3:C1442<=1),1)
or:
=AGGREGATE(15,6,C3:C1442/(ABS(C3:C1442-0.5)<=0.5),1)

For max:
=AGGREGATE(14,6,C3:C1442/(C3:C1442>=0)/(C3:C1442<=1),1)
or:
=AGGREGATE(14,6,C3:C1442/(ABS(C3:C1442-0.5)<=0.5),1)

Blessings!

 

[Chihiro]

I have not tried your formula yet, but will that work for a named range formula?

Do you mean that you want to have named range return min value?
Sure, but since named range formulas are naturally array formula... you don't need to confirm it as array. And use absolute reference to range.

Or use John's formula (also use absolute reference).

@John Jairo V
I really got to learn aggregate.

 

[Dan Kennedy]

Hi to all!

You can use:

For min:
=AGGREGATE(15,6,C3:C1442/(C3:C1442>=0)/(C3:C1442<=1),1)
or:
=AGGREGATE(15,6,C3:C1442/(ABS(C3:C1442-0.5)<=0.5),1)

For max:
=AGGREGATE(14,6,C3:C1442/(C3:C1442>=0)/(C3:C1442<=1),1)
or:
=AGGREGATE(14,6,C3:C1442/(ABS(C3:C1442-0.5)<=0.5),1)

Blessings!

Works great thank you!!!!

 

[Dan Kennedy]

Do you mean that you want to have named range return min value?
Sure, but since named range formulas are naturally array formula... you don't need to confirm it as array. And use absolute reference to range.

Or use John's formula (also use absolute reference).

@John Jairo V
I really got to learn aggregate.

Well I still need the Named Ranges to ignore the #N/A values but John's formula helped another issue I was trying to figure out.

Thank you Both