| 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 . . |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Hybrid Mode
