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
zero value
ALSO in CM4 FOR max HOURS for CJ5:CJ65
You are using an out of date browser. It may not display this or other websites correctly.
You should upgrade or use an alternative browser.
You should upgrade or use an alternative browser.
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)
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
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)))
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
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
Are there any names that have the same amount of hours?
Joe...32
Tom...45
Bob...32
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
M
Mike
Yes there could very well be some names with the same hours
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.
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.