| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
small function
I have a range of cells that are a mix of blank cells and cells containing a
time, name and purpose eg "10.30 John S Golf" there should be no more than 10 cells containing text. i want a range of cells say b2:b12 to represent this information in time order I have tried using the small function but this will only work for cells containing numbers only. Some cells will contain text only with no time I want those to appear either at the end or the start of the list. i think this will require a combination of the search if and small functions but not sure quite how!! Any suggestions gratefully received. |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
small function
Extract the time with
=--LEFT(A2,FIND(" ",A2)-1) and use SMALL on that. -- __________________________________ HTH Bob "Atishoo" wrote in message ... I have a range of cells that are a mix of blank cells and cells containing a time, name and purpose eg "10.30 John S Golf" there should be no more than 10 cells containing text. i want a range of cells say b2:b12 to represent this information in time order I have tried using the small function but this will only work for cells containing numbers only. Some cells will contain text only with no time I want those to appear either at the end or the start of the list. i think this will require a combination of the search if and small functions but not sure quite how!! Any suggestions gratefully received. |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
small function
Hi bob
thanks for that! Am still strugling a bit as far as i can tell the left function can only be applied to a single cell and not a range whereas im using the small function to return the contents of the cell with the lowest time etc, It also falls foul where there is no time and it tries to apply small to a text string instead! any inspiration? "Bob Phillips" wrote: Extract the time with =--LEFT(A2,FIND(" ",A2)-1) and use SMALL on that. -- __________________________________ HTH Bob "Atishoo" wrote in message ... I have a range of cells that are a mix of blank cells and cells containing a time, name and purpose eg "10.30 John S Golf" there should be no more than 10 cells containing text. i want a range of cells say b2:b12 to represent this information in time order I have tried using the small function but this will only work for cells containing numbers only. Some cells will contain text only with no time I want those to appear either at the end or the start of the list. i think this will require a combination of the search if and small functions but not sure quite how!! Any suggestions gratefully received. |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
small function
It can be applied to a range of cells in an array formula.
-- __________________________________ HTH Bob "Atishoo" wrote in message ... Hi bob thanks for that! Am still strugling a bit as far as i can tell the left function can only be applied to a single cell and not a range whereas im using the small function to return the contents of the cell with the lowest time etc, It also falls foul where there is no time and it tries to apply small to a text string instead! any inspiration? "Bob Phillips" wrote: Extract the time with =--LEFT(A2,FIND(" ",A2)-1) and use SMALL on that. -- __________________________________ HTH Bob "Atishoo" wrote in message ... I have a range of cells that are a mix of blank cells and cells containing a time, name and purpose eg "10.30 John S Golf" there should be no more than 10 cells containing text. i want a range of cells say b2:b12 to represent this information in time order I have tried using the small function but this will only work for cells containing numbers only. Some cells will contain text only with no time I want those to appear either at the end or the start of the list. i think this will require a combination of the search if and small functions but not sure quite how!! Any suggestions gratefully received. |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
small function
so as an array should it not be:
=SMALL(LEFT(B18*B19*B20*B21*B22*B23*B24*B25*B26*B2 7*B28*B29*B30,4),3) it dont wanna work for me the swine "Bob Phillips" wrote: It can be applied to a range of cells in an array formula. -- __________________________________ HTH Bob "Atishoo" wrote in message ... Hi bob thanks for that! Am still strugling a bit as far as i can tell the left function can only be applied to a single cell and not a range whereas im using the small function to return the contents of the cell with the lowest time etc, It also falls foul where there is no time and it tries to apply small to a text string instead! any inspiration? "Bob Phillips" wrote: Extract the time with =--LEFT(A2,FIND(" ",A2)-1) and use SMALL on that. -- __________________________________ HTH Bob "Atishoo" wrote in message ... I have a range of cells that are a mix of blank cells and cells containing a time, name and purpose eg "10.30 John S Golf" there should be no more than 10 cells containing text. i want a range of cells say b2:b12 to represent this information in time order I have tried using the small function but this will only work for cells containing numbers only. Some cells will contain text only with no time I want those to appear either at the end or the start of the list. i think this will require a combination of the search if and small functions but not sure quite how!! Any suggestions gratefully received. |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
small function
Why do you have *, don't you want the 3rd smallest?
You must mean =SMALL(--(LEFT(B18:B30,4)),3) As I said it is an array formula. -- __________________________________ HTH Bob "Atishoo" wrote in message ... so as an array should it not be: =SMALL(LEFT(B18*B19*B20*B21*B22*B23*B24*B25*B26*B2 7*B28*B29*B30,4),3) it dont wanna work for me the swine "Bob Phillips" wrote: It can be applied to a range of cells in an array formula. -- __________________________________ HTH Bob "Atishoo" wrote in message ... Hi bob thanks for that! Am still strugling a bit as far as i can tell the left function can only be applied to a single cell and not a range whereas im using the small function to return the contents of the cell with the lowest time etc, It also falls foul where there is no time and it tries to apply small to a text string instead! any inspiration? "Bob Phillips" wrote: Extract the time with =--LEFT(A2,FIND(" ",A2)-1) and use SMALL on that. -- __________________________________ HTH Bob "Atishoo" wrote in message ... I have a range of cells that are a mix of blank cells and cells containing a time, name and purpose eg "10.30 John S Golf" there should be no more than 10 cells containing text. i want a range of cells say b2:b12 to represent this information in time order I have tried using the small function but this will only work for cells containing numbers only. Some cells will contain text only with no time I want those to appear either at the end or the start of the list. i think this will require a combination of the search if and small functions but not sure quite how!! Any suggestions gratefully received. |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
small function
I thought an array formula represented the range as "B1*B2* etc!
have tried representing the range as B18:B30 as you suggest but this just returns #value even on a simple range just containing numbers, am scratching head! "Bob Phillips" wrote: Why do you have *, don't you want the 3rd smallest? You must mean =SMALL(--(LEFT(B18:B30,4)),3) As I said it is an array formula. -- __________________________________ HTH Bob "Atishoo" wrote in message ... so as an array should it not be: =SMALL(LEFT(B18*B19*B20*B21*B22*B23*B24*B25*B26*B2 7*B28*B29*B30,4),3) it dont wanna work for me the swine "Bob Phillips" wrote: It can be applied to a range of cells in an array formula. -- __________________________________ HTH Bob "Atishoo" wrote in message ... Hi bob thanks for that! Am still strugling a bit as far as i can tell the left function can only be applied to a single cell and not a range whereas im using the small function to return the contents of the cell with the lowest time etc, It also falls foul where there is no time and it tries to apply small to a text string instead! any inspiration? "Bob Phillips" wrote: Extract the time with =--LEFT(A2,FIND(" ",A2)-1) and use SMALL on that. -- __________________________________ HTH Bob "Atishoo" wrote in message ... I have a range of cells that are a mix of blank cells and cells containing a time, name and purpose eg "10.30 John S Golf" there should be no more than 10 cells containing text. i want a range of cells say b2:b12 to represent this information in time order I have tried using the small function but this will only work for cells containing numbers only. Some cells will contain text only with no time I want those to appear either at the end or the start of the list. i think this will require a combination of the search if and small functions but not sure quite how!! Any suggestions gratefully received. |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
small function
Here is one of my standard replies for array formulae:
* Array formulae need to be committed using CTRL-SHIFT-ENTER (CSE) rather than the normal ENTER. If you do this correctly then Excel will wrap curly braces around the formula when viewed in the formula bar - do not type these yourself. If you edit the formula you will need to use CSE again. Hope this helps. Pete On Jun 26, 10:19*am, Atishoo wrote: I thought an array formula represented the range as "B1*B2* etc! have tried representing the range as B18:B30 as you suggest but this just returns #value even on a simple range just containing numbers, am scratching head! "Bob Phillips" wrote: Why do you have *, don't you want the 3rd smallest? You must mean =SMALL(--(LEFT(B18:B30,4)),3) As I said it is an array formula. -- __________________________________ HTH Bob "Atishoo" wrote in message ... so as an array should it not be: =SMALL(LEFT(B18*B19*B20*B21*B22*B23*B24*B25*B26*B2 7*B28*B29*B30,4),3) it dont wanna work for me the swine "Bob Phillips" wrote: It can be applied to a range of cells in an array formula. -- __________________________________ HTH Bob "Atishoo" wrote in message ... Hi bob thanks for that! Am still strugling a bit as far as i can tell the left function can only be applied to a single cell and not a range whereas im using the small function to return the contents of the cell with the lowest time etc, It also falls foul where there is no time and it tries to apply small to a text string instead! any inspiration? "Bob Phillips" wrote: Extract the time with =--LEFT(A2,FIND(" ",A2)-1) and use SMALL on that. -- __________________________________ HTH Bob "Atishoo" wrote in message ... I have a range of cells that are a mix of blank cells and cells containing a time, name and purpose eg "10.30 John S Golf" there should be no more than 10 cells containing text. i want a range of cells say b2:b12 to represent this information in time order I have tried using the small function but this will only work for cells containing numbers only. Some cells will contain text only with no time I want those to appear either at the end or the start of the list. i think this will require a combination of the search if and small functions but not sure quite how!! Any suggestions gratefully received.- Hide quoted text - - Show quoted text - |
|
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
small function
Many thanks Pete I have been entering using cse still not getting any joy
using the following formula {=SMALL(LEFT(B18:B30,4),3)} "Pete_UK" wrote: Here is one of my standard replies for array formulae: * Array formulae need to be committed using CTRL-SHIFT-ENTER (CSE) rather than the normal ENTER. If you do this correctly then Excel will wrap curly braces around the formula when viewed in the formula bar - do not type these yourself. If you edit the formula you will need to use CSE again. Hope this helps. Pete On Jun 26, 10:19 am, Atishoo wrote: I thought an array formula represented the range as "B1*B2* etc! have tried representing the range as B18:B30 as you suggest but this just returns #value even on a simple range just containing numbers, am scratching head! "Bob Phillips" wrote: Why do you have *, don't you want the 3rd smallest? You must mean =SMALL(--(LEFT(B18:B30,4)),3) As I said it is an array formula. -- __________________________________ HTH Bob "Atishoo" wrote in message ... so as an array should it not be: =SMALL(LEFT(B18*B19*B20*B21*B22*B23*B24*B25*B26*B2 7*B28*B29*B30,4),3) it dont wanna work for me the swine "Bob Phillips" wrote: It can be applied to a range of cells in an array formula. -- __________________________________ HTH Bob "Atishoo" wrote in message ... Hi bob thanks for that! Am still strugling a bit as far as i can tell the left function can only be applied to a single cell and not a range whereas im using the small function to return the contents of the cell with the lowest time etc, It also falls foul where there is no time and it tries to apply small to a text string instead! any inspiration? "Bob Phillips" wrote: Extract the time with =--LEFT(A2,FIND(" ",A2)-1) and use SMALL on that. -- __________________________________ HTH Bob "Atishoo" wrote in message ... I have a range of cells that are a mix of blank cells and cells containing a time, name and purpose eg "10.30 John S Golf" there should be no more than 10 cells containing text. i want a range of cells say b2:b12 to represent this information in time order I have tried using the small function but this will only work for cells containing numbers only. Some cells will contain text only with no time I want those to appear either at the end or the start of the list. i think this will require a combination of the search if and small functions but not sure quite how!! Any suggestions gratefully received.- Hide quoted text - - Show quoted text - |
|
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
small function
Ahhhhhh
it works!!! Sorry the problem was that a couple of cells in the range were returning as #value so that was what the formula was returning. but now I have a new problem !! i want the range to be sorted in order of its time this just returns the time and not the rest of the contents of the cell! i could use find to return the full cell contents based on the time returned but thats messey! any better ideas?? "Atishoo" wrote: Many thanks Pete I have been entering using cse still not getting any joy using the following formula {=SMALL(LEFT(B18:B30,4),3)} "Pete_UK" wrote: Here is one of my standard replies for array formulae: * Array formulae need to be committed using CTRL-SHIFT-ENTER (CSE) rather than the normal ENTER. If you do this correctly then Excel will wrap curly braces around the formula when viewed in the formula bar - do not type these yourself. If you edit the formula you will need to use CSE again. Hope this helps. Pete On Jun 26, 10:19 am, Atishoo wrote: I thought an array formula represented the range as "B1*B2* etc! have tried representing the range as B18:B30 as you suggest but this just returns #value even on a simple range just containing numbers, am scratching head! "Bob Phillips" wrote: Why do you have *, don't you want the 3rd smallest? You must mean =SMALL(--(LEFT(B18:B30,4)),3) As I said it is an array formula. -- __________________________________ HTH Bob "Atishoo" wrote in message ... so as an array should it not be: =SMALL(LEFT(B18*B19*B20*B21*B22*B23*B24*B25*B26*B2 7*B28*B29*B30,4),3) it dont wanna work for me the swine "Bob Phillips" wrote: It can be applied to a range of cells in an array formula. -- __________________________________ HTH Bob "Atishoo" wrote in message ... Hi bob thanks for that! Am still strugling a bit as far as i can tell the left function can only be applied to a single cell and not a range whereas im using the small function to return the contents of the cell with the lowest time etc, It also falls foul where there is no time and it tries to apply small to a text string instead! any inspiration? "Bob Phillips" wrote: Extract the time with =--LEFT(A2,FIND(" ",A2)-1) and use SMALL on that. -- __________________________________ HTH Bob "Atishoo" wrote in message ... I have a range of cells that are a mix of blank cells and cells containing a time, name and purpose eg "10.30 John S Golf" there should be no more than 10 cells containing text. i want a range of cells say b2:b12 to represent this information in time order I have tried using the small function but this will only work for cells containing numbers only. Some cells will contain text only with no time I want those to appear either at the end or the start of the list. i think this will require a combination of the search if and small functions but not sure quite how!! Any suggestions gratefully received.- Hide quoted text - - Show quoted text - |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| SMALL function | Excel Worksheet Functions | |||
| How to use small function? | Excel Discussion (Misc queries) | |||
| Small Function | Excel Worksheet Functions | |||
| SMALL function | Excel Discussion (Misc queries) | |||
| SMALL function if 0 | Excel Worksheet Functions | |||
Linear Mode
