Entering arrays in a single cell
Entering arrays in a single cell
(OP)
Is it possible to enter a serie of numbers - comma separated - in one cell and then use this "string" as an array in another formula?
For example:
In cell A1 I enter 1,0,0,1,1,1,0
In cell B1 I enter =sum(A1) and the result should have been 4.
I know this doesn't work, but the question is is there some way of manipulating the syntax or entering array formulas that will make it work?
My aim is not to use the sum function - this was just to illustrate the example - I want to make a user defined function to manipulate the values, but I prefer to have the function in a separate cell in order to be able to protect it.
Regards
Mogens
For example:
In cell A1 I enter 1,0,0,1,1,1,0
In cell B1 I enter =sum(A1) and the result should have been 4.
I know this doesn't work, but the question is is there some way of manipulating the syntax or entering array formulas that will make it work?
My aim is not to use the sum function - this was just to illustrate the example - I want to make a user defined function to manipulate the values, but I prefer to have the function in a separate cell in order to be able to protect it.
Regards
Mogens





RE: Entering arrays in a single cell
TTFN
RE: Entering arrays in a single cell
The formula LEFT(D19,SEARCH("x",D19)-3) will extract the first number from the following text [ 24 5/8" x 18 1/2" ] The result is 24.625.
The formula MID(D19,(SEARCH("x ",D19)+2),LEN(D19)-SEARCH("x ",D19)-2) will extract the second number from the same text.
As long as the text always has the same format including the " x " and the inch units then all I have to do is multiply the 2 formula results to get square inches.
The SEARCH embedded in the formulae will consistently truncate before or after the " x " and the LEN embedded in the MID formula will control how much text to delete to extract the number.
Using the MID rather than the RIGHT formula allows the deletion of the trailing inch unit. For text without trailing units I use the right function.
With some experimentaion you should be able to adapt these to your needs. The MID formula allows you to force the search to start at a predefined character, enter 3 for the command to ingnoer the first 3 letters etc. This may be critical for you since all you numbers are separated with the same character and the SEARCH formula will default to the first target it can find in a line of text.
Good luck, Brad.
RE: Entering arrays in a single cell
m777182
RE: Entering arrays in a single cell
Cell[A1]: input your comma-separated values
Example: 14,2217,2,9,12,1,344,45
Cell[A4]=SEARCH(",",A1)
Cell[A5]=SEARCH(",",A$1,A4+1)
then, copy Cell[A5] down to Cell[A10]
Cell[A11]=LEN(A1)+1
Cell[B4]=1*MID(A$1,1,A4-A3-1)
Cell[B5]=1*MID(A$1,A4+1,A5-A4-1)
then, copy Cell[B5] down to Cell[B11]
Cells[B4] thru [B11] should be your values in a vertical "vector". Since Exxcel automatically formats them to text, the 1* portion of the formulae in the Cells [B5]-[B11] converts the values back to numerical.
RE: Entering arrays in a single cell
CODE
Dim total As Single
a = Split(ActiveSheet.Cells(1, 1).Value, " ")
For b = LBound(a) To UBound(a)
total = total + Val(a(b))
Next b
ActiveSheet.Cells(1, 2) = total
End Sub
Good Luck
johnwm
________________________________________________________
To get the best from these forums read FAQ731-376 before posting
UK steam enthusiasts: www.essexsteam.co.uk
RE: Entering arrays in a single cell
I figured I might have to use a "text separating" formula or function to make it work.
As IRStuff questioned - I don't HAVE to have it all in one cell but I just think it makes a neater spreadsheet in this case as I already have a lot of columns in there. My spreadsheet is for pressure loss calculations and I want to enter the number of bends, tee’s etc in one cell and use the values to calculate the single losses.
I changed the code from johnwm into a worksheet function and it works perfect.
Function SumValues(REF As Range)
Dim total As Single
a = Split(REF.Cells(1, 1).Value, ",")
For b = LBound(a) To UBound(a)
total = total + Val(a(b))
Next b
SumValues = total
End Function
Thanks again for all suggestions
Mogens
RE: Entering arrays in a single cell
TTFN