Min/Max need help

[Mike]

Im trying to put a formula in CL4 for min hours for CJ5:CJ65 & if <=0 have
zero value
ALSO in CM4 FOR max HOURS for CJ5:CJ65

 

[T. Valko]

For the MIN:

Array entered** :

=MIN(IF(CJ5:CJ65>0,CJ5:CJ65))

** 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.

For the MAX:

=MAX(CJ5:CJ65)

 

[Mike]

This does not work i tried, In cel CJ5:CJ65 i have a formula in there & it
works but i get #NA just until other data,s are entred =INDIRECT("e" &
VLOOKUP(ROW()-4,CP$5:CR$65,3,FALSE)) COULD THIS BE WHY because i get same for
the formulas that you gave me but does not work when other data,s are entered

 

[T. Valko]

If there might be errors in the range then try this (still array entered)...

For the MIN:

=MIN(IF(ISNUMBER(CJ5:CJ65),IF(CJ5:CJ65>0,CJ5:CJ65)))

For the MAX (array entered):

=MAX(IF(ISNUMBER(CJ5:CJ65),IF(CJ5:CJ65>0,CJ5:CJ65)))

 

[Mike]

Thanks this did work, Could you please help with one more formula, I have
CK4 Min. hours & CL4 Max hours & CM4 is going to = Differece between CK4 &
CL4,
CG5:CG65 =names & CJ5:CJ65 =hours remember hours & names belong together,
Now what i want is to have Names transfered over to CK5:CK65 if greater than
28 from Min to Max

 

[T. Valko]

Now what i want is to have Names transfered

over to CK5:CK65 if greater than 28 from Min to Max

Are there any names that have the same amount of hours?

Joe...32
Tom...45
Bob...32

 

[Mike]

Yes there could very well be some names with the same hours

 

[T. Valko]

Ok, this is fairly complicated!

Enter this formula in CK4. This will return the count where the hours are
greater than 28.

=COUNTIF(CJ5:CJ65,">28")

Enter this array formula** in CK5:

=IF(ROWS(CK$5:CK5)>CK$4,"",INDEX(CG$5:CG$65,MATCH(SMALL(IF(CJ$5:CJ$65>28,CJ$5:CJ$65+ROW(CJ$5:CJ$65)/10^10),ROWS(CK$5:CK5)),IF(CJ$5:CJ$65>28,CJ$5:CJ$65+ROW(CJ$5:CJ$65)/10^10),0)))

** 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.

Copy down until you get blanks.

We could use a less complicated method but it would require the use of a
column of helper formulas.