Excel2003 MAX and MIN with <>

[hamricka]

I need to find the MAX of a column of numbers that contain < in the
with out putting the less than in a separate colum.

Example:

<2
<0.1
<8
<7
<3
<0.5

 

[Bob Phillips]

Try this array formula

="<"&MAX(--SUBSTITUTE(A1:A6,"<",""))

 

[hamricka]

THat made it show "#Value" instead of the values listed.

 

[Domenic]

That's probably because the range contains empty cells. Try...

=MAX(IF(A2:A10<>"",SUBSTITUTE(A2:A10,"<","")+0))

....confirmed with CONTROL+SHIFT+ENTER. Adjust the range accordingly.

 

[T. Valko]

Did you enter the formula as an array?

Array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT
key then hit ENTER.

Are there any empty/blank cells in the range?

Are there any cells that don't contain numbers that are other text entries?

 

[Bob Bridges]

Hamricka, you're saying these values are actually strings (eg "<0.1") rather
than numeric values (eg 0.1)? If so, I think you need a helper column, each
cell of which strips off the '<' and calculates the value; you can take the
MAX of that column.

Say your string values are in col A; in row 2 of your helper column the
formula would be =VALUE(MID(A2,2,9)). The MID takes the string starting with
the second position out to the end, and the VALUE converts it from a string
to a number. After that you can use MAX against that column.

If you're getting this sheet from someone else that's about the best you can
do. But if you're preparing this sheet for someone else, who for some reason
needs to see the '<', you can do without the translation and just have the
cell format display the '<' without it affecting the actual value stored in
the cell. By that method, the value in your second row would be 0.1 (not
"<0.1"), and to get Excel to display it with a '<' just set the format to
include the '<' as part of the output. Do you know how to do that?

 

[hamricka]

THis worked! But it did not show the < with the number. If we can ge
that then the problem will be solved?
aha!

 

[Domenic]

Try...

="<"&MAX(IF(A2:A10<>"",SUBSTITUTE(A2:A10,"<","")+0))

....confirmed with CONTROL+SHIFT+ENTER.

 

[hamricka]

This worked great for the maximum part but when I changed it to min i
puts all 0. I need it to show the lowest value listed not O. what do yo
sugguest. I am not very skilled at writing formulas in excel. I can d
most basic stuff. Here is an example of the results I am working with:

Monday
Tuesday <2.0
Wednesday
Thursday <2.0
Friday <2.0
Saturday <2.0

Etc...

The state will not allow us to enter zero for the days we have n
results and our max and min before the average can not have zeros and i
there is a <> present it must be present in the Min and Max. Can yo
help me?

 

[Domenic]

This worked great for the maximum part but when I changed it to min it
puts all 0. I need it to show the lowest value listed not O. what do you
sugguest. I am not very skilled at writing formulas in excel. I can do
most basic stuff. Here is an example of the results I am working with:

Monday
Tuesday <2.0
Wednesday
Thursday <2.0
Friday <2.0
Saturday <2.0

Etc...

The state will not allow us to enter zero for the days we have no
results and our max and min before the average can not have zeros and if
there is a <> present it must be present in the Min and Max. Can you
help me?

If MAX is replaced with MIN, the formula should return the desired
result. For example, if A2:A6 contains the following data...

<2

<8

<6

....the formula should <2 as the minimum. If, however, the data contains
values such as <0 which need to be ignored, try the following formula
instead...

="<"&MIN(IF(A2:A10<>"",IF(SUBSTITUTE(A2:A10,"<","")+0>0,SUBSTITUTE(A2:A10
,"<","")+0)))

....confirmed with CONTROL+SHIFT+ENTER.

 

[hamricka]

Thank you, than you, thank you, this form is a life saver for those o
us who not very skilled with excel.