Min/Max need help

M

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

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)
 
M

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

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)))
 
M

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

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
 
T

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

I Really need help with formula 1
Formula Help 1
I need help 3
Min Value 4
min ,max,average 2
max and min help. 5
Min in array formula 2
min thats = 0 6

Top