| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Min/Max need help
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 |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Min/Max need help
For the MIN:
Array entered** : =MIN(IF(CJ5:CJ650,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) -- Biff Microsoft Excel MVP "Mike" wrote in message ... 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 |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Min/Max need help
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" wrote: For the MIN: Array entered** : =MIN(IF(CJ5:CJ650,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) -- Biff Microsoft Excel MVP "Mike" wrote in message ... 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 . |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Min/Max need help
If there might be errors in the range then try this (still array entered)...
For the MIN: =MIN(IF(ISNUMBER(CJ5:CJ65),IF(CJ5:CJ650,CJ5:CJ65) )) For the MAX (array entered): =MAX(IF(ISNUMBER(CJ5:CJ65),IF(CJ5:CJ650,CJ5:CJ65) )) -- Biff Microsoft Excel MVP "Mike" wrote in message ... 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" wrote: For the MIN: Array entered** : =MIN(IF(CJ5:CJ650,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) -- Biff Microsoft Excel MVP "Mike" wrote in message ... 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 . |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Min/Max need help
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" wrote: If there might be errors in the range then try this (still array entered)... For the MIN: =MIN(IF(ISNUMBER(CJ5:CJ65),IF(CJ5:CJ650,CJ5:CJ65) )) For the MAX (array entered): =MAX(IF(ISNUMBER(CJ5:CJ65),IF(CJ5:CJ650,CJ5:CJ65) )) -- Biff Microsoft Excel MVP "Mike" wrote in message ... 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" wrote: For the MIN: Array entered** : =MIN(IF(CJ5:CJ650,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) -- Biff Microsoft Excel MVP "Mike" wrote in message ... 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 . . |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Min/Max need help
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 -- Biff Microsoft Excel MVP "Mike" wrote in message ... 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" wrote: If there might be errors in the range then try this (still array entered)... For the MIN: =MIN(IF(ISNUMBER(CJ5:CJ65),IF(CJ5:CJ650,CJ5:CJ65) )) For the MAX (array entered): =MAX(IF(ISNUMBER(CJ5:CJ65),IF(CJ5:CJ650,CJ5:CJ65) )) -- Biff Microsoft Excel MVP "Mike" wrote in message ... 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" wrote: For the MIN: Array entered** : =MIN(IF(CJ5:CJ650,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) -- Biff Microsoft Excel MVP "Mike" wrote in message ... 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 . . |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Min/Max need help
Yes there could very well be some names with the same hours
"T. Valko" wrote: 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 -- Biff Microsoft Excel MVP "Mike" wrote in message ... 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" wrote: If there might be errors in the range then try this (still array entered)... For the MIN: =MIN(IF(ISNUMBER(CJ5:CJ65),IF(CJ5:CJ650,CJ5:CJ65) )) For the MAX (array entered): =MAX(IF(ISNUMBER(CJ5:CJ65),IF(CJ5:CJ650,CJ5:CJ65) )) -- Biff Microsoft Excel MVP "Mike" wrote in message ... 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" wrote: For the MIN: Array entered** : =MIN(IF(CJ5:CJ650,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) -- Biff Microsoft Excel MVP "Mike" wrote in message ... 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 . . . |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Min/Max need help
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$6528,CJ$5:CJ$65+ROW(CJ$5:CJ$65)/10^10),ROWS(CK$5:CK5)),IF(CJ$5:CJ$6528,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. -- Biff Microsoft Excel MVP "Mike" wrote in message ... Yes there could very well be some names with the same hours "T. Valko" wrote: 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 -- Biff Microsoft Excel MVP "Mike" wrote in message ... 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" wrote: If there might be errors in the range then try this (still array entered)... For the MIN: =MIN(IF(ISNUMBER(CJ5:CJ65),IF(CJ5:CJ650,CJ5:CJ65) )) For the MAX (array entered): =MAX(IF(ISNUMBER(CJ5:CJ65),IF(CJ5:CJ650,CJ5:CJ65) )) -- Biff Microsoft Excel MVP "Mike" wrote in message ... 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" wrote: For the MIN: Array entered** : =MIN(IF(CJ5:CJ650,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) -- Biff Microsoft Excel MVP "Mike" wrote in message ... 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 . . . |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Linear Mode
