Single Cell Array
When I write {={1;2;3;4;5}} in A1, and then {=SUM(A1)} in B2, B2 displays 1, when it should display 15.
When I write {=SUM({1;2;3;4;5})} it displays 15.
So how do I make this work with single cell arrays, without filling my sheet with my arrays' values, and without manually writing my arrays into the functions which use them?
Hi -
I think you have to have a cell for each element of your array. You can't put them all in one cell. However, you can use the Excel Name Manager, on the Formulas Tab to Define a Name for an array. So you go to Formulas, Define Name, which brings up a window for New Name. Enter any name, for example Array1. Then in the window titled Refers To: enter ={1,2,3,4,5} Then click OK.
Now you can enter your formula =SUM(Array1) and you will get a result of 15. This doesn't fill you sheet with array values and is pretty easy to insert in formulas since Excel will bring your element Name up as you type. Just pick it from the list.
Hope this helps.
____________________________________________
If this has solved your problem, please edit the thread title to add the word [SOLVED] at the beginning. You can do this by
-Go to the top of the first post
-Select Thread Tools
-Select Mark thread as Solved
If I have been particularly helpful, please "bump" my reputation by pressing the small star in the lower left corner of my post.
Oh, yes! that helped! Thanks
Great! Glad I could help. Thank you for the Rep! If you are satisfied with the answer, please don't forget to mark your thread as SOLVED per the directions at the bottom of this post.
Yeah, I'll mark it as solved, but I kind a have 1 more question related:
How do I address a range of elements of this array? If I have {6;2;0;0;3;5;-2;...} how can I use the range of elements - from 2-nd to 6-th (for example)?
And is there any function like "LENGTH" that will return the amount of elements each array contains?
What precisely do you mean? To return a reduced array containing just the elements between the relative positions you specify? So in your example, for:Originally Posted by WereElf
{6;2;0;0;3;5;-2}
you'd want to return
{2;0;0;3;5}
?
COUNT if all elements within the array are numeric. COUNTA otherwise, though note that the latter will equally consider null strings ("") for the count.
Regards
Hi -
You can access individual elements with the INDEX function. So, for example if your array is named Array1, you could use the formula =INDEX(Array1,2) to pull out the second element.
As for your other question, I agree with XOR LX. It depends on what you're going to do with the data. There are several functions in Excel that work with arrays such as SUM, SUMPRODUCT, INDEX, MATCH and many others as well as turning some of the standard functions that don't normally accept arrays into Array Functions using Ctrl-Shift-Enter such as IF statements.
Can you give us some examples of what you're going to do with the data?
That's exactly what I want. How do I do that?
And yeah, count would work for getting the array's length
Assuming your original array is named Arry1 and that you put your desired start and end indices (e.g. 2 and 6) in A1 and B1 respectively:
INDEX(Arry1,N(IF(1,A1+ROW(INDEX(A:A,1):INDEX(A:A,1+B1-A1))-1)))
The above may require committing as an array formula**, depending upon how it is employed within the workbook.
Regards
Works like a charm! Thanks
You're welcome!
Cheers
By the way, after analyzing the formula for a bit, I noticed something odd: doesn't IF(1;..) always return true? what's the point of this if?
Very good question!
To which the answer is: yes, it does always return TRUE.
And I'm still not entirely sure why this artificial, self-evident coercion is necessary, I'm afraid.
Lori makes a decent effort at an explanation here.
Regards
Now there is 1 more thing troubling me:
I want the calculations I make to be private/hidden, and the arrays I use as well.
When I lock the sheet - I can still view the global arrays. Any way to prevent people from seeing the global arrays I use, after locking the sheet?
I know that not many people are aware of the existence of the global arrays (at least not the ones who are going to use my product), but don't wanna take any chances.
You can do this with some VBA:
Regards
Please Login or Register to view this content.
XOR LX
i have tried using your formula (entered as an array formula, pls see the attached PrtSc) but it doesn't seem to work?
would you know why?
thx in advance, david
Attachment1.png
Hi,
Can't see any screenshots, though an actual workbook is always more useful, of course.
Regards
Edit: Apologies, I see it now. Remove the quotation marks from your definition for Array1.
Regards
Last edited by XOR LX; 02-25-2019 at 03:55 PM.
Stupid me, many thx, can i push my luck and ask you if its possible to select non consecutive members as well?
for example if i wanted to select the first, third and fourth?
rgds, david
by the way, i have been looking for this for some weeks now, i knew about the dereferencing technique but it was with the use of constants (e.g.- {1,3} ) and i couldnt find a way to reference it to a cell or a function. many thx for this!
So do you still need some help with this?
Regards
Sure, i would love to learn how to get this done, rgds, david
So why can't you use an array constant corresponding to the desired entries, similar to that you indicated in post #19? If you need a dynamic, non-static array, can you clarify what the conditions are for these "non-consecutive members"?
Regards
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks