Hi folks,
Would appreciate some help with this. Is there a way possible to sum each dimension of an array using only Excel Formulas?
So let's say I have a formula that will generate a 4 x 4. Is there any worksheet function that can sum each row or each column of the array so that I can then pull the smallest / largest value of the array?
See the formula
Hello cmore,
You will need to use functions that can handle arrays, not all do. SUM and INDEX can be used to together to return a row or column total.
Example to Sum Column 1:
=SUM(INDEX(MyArray, 0, 1))
Example to Sum Row 1:
= SUM(INDEX((MyArray, 1, 0))
Sincerely,
Leith Ross
Remember To Do the Following....
1. Use code tags. Place [CODE] before the first line of code and [/CODE] after the last line of code.2. Thank those who have helped you by clicking the Starbelow the post.
3. Please mark your post [SOLVED] if it has been answered satisfactorily.
Old Scottish Proverb...
Luathaid gu deanamh maille! (Rushing causes delays!)
Wud up. Hope you've been good boss. Yeah this is a really jacked up problem. So I'm trying to auto sort text when when the data is an array. So I won't ever know before hand the dimensions of the array. Typically any sensible person would use VBA, but I'm trying to come up with something for basic users without using a helper column. So no Countif
and trying to for instance look at the data in A1:A4 (housed in an array) and return an array of J2:J5 or F6:I6?
See formula in J10, returns 4 x 4. Cannot believe there's no way to sum each dimension. It's going to drive me crazy
Hello cmore,
Are you trying to create a new array from the sums of the existing array's dimensions?
Yes sir you got it. What you think? Not possible in excel UI?
Hello cmore,
It is possible to return an array as the result of a formula in Excel. However, you can not convert the array into a named range unless you use VBA.
Do tell? I'm all ears. I haven't been able to figure this out. doesn't have to be a named formula
Hello cmore,
For example, if you add the formula =INDEX(A2:A5, 0, 1) to a cell, it will return the #VALUE error. This happens because the returned value is an array of values and not a single value.
To check this, you can use a simple debug technique.
1. Click the cell with the formula.
2. Got to the formula bar and place the Cursor before the I in INDEX.
3. Left click and drag to the end of the formula and release the mouse.
4. Press the F9 key.
You will see the data values enclosed in braces {} separated either by a comma (columns) or a semi-colon (rows).
To return to the original formula, press Ctrl+Z.
Agreed. But the problem is more I'm trying to take an array and either evaluate each element against all of its elements or another way of thinking about it is taking a 4 x 1 converting to a 4 x 4 and then resulting the results back to a 4 x 1.
So if I take the formulathen functionally I'd like the equivalent of taking the sum of
Please Login or Register to view this content.
INDEX(--({"A";"B";"C";"D"}<{"A","B","C","D"}),1,) = (i1)
INDEX(--({"A";"B";"C";"D"}<{"A","B","C","D"}),2,) = (i2)
INDEX(--({"A";"B";"C";"D"}<{"A","B","C","D"}),3,) = (i3)
INDEX(--({"A";"B";"C";"D"}<{"A","B","C","D"}),4,) = (i4)
to a final result of {(i1), (i2), (i3), (i4)}
I cannot find a non-VBA solution to do this, or am I missing a piece of what you're saying?
Hello cmore,
It is getting late in California. I will think about this more in morning. Perhaps I can provide you with some code tomorrow.
Hey Leith. Thanks for thinking this through with me. Don't worry about generating code for this. It's such a simple task in VBA I'm sure you can think it up in your sleep. I'm not sure why Excel cannot do this natively with an array in memory given CountIf can do this. Going to keep thinking and do a work around in the meantime.
Yeah still no luck to this, open to all thoughts on non-VBA solution
Hello cmore,
I am going to refer this problem to one of the other moderators who is an expert with formulas. If it can be done, he will know.
Thanks homey truly appreciate it sir.
Hello cmore,
Don't thank me just yet. I contacted Richard Buttery by PM. Hopefully, he will be able to provide some answers using just formulas.
@cmore
I don't understand what result you want to see.
Would you manually add the ACTUAL result and explain the calculations that you made.
Will the original data always be formulae that result in 1 or zero as your example or might there be other values?
Richard Buttrey
RIP - d. 06/10/2022
If any of the responses have helped then please consider rating them by clicking the small star iconbelow the post.
Is this what you're trying to do?
Row\Col A J2 and down: =SUMPRODUCT(--(E2 < F$1:I$1)) B A C F6 and across: =SUMPRODUCT(--(F1 > $E2:$E5))
Entia non sunt multiplicanda sine necessitate
For the data in the OP file, I created a Names
Name: DataRange RefersTo: = Sheet1!$A$1:$A$4
Name: DataCompare RefersTo: =--(DataRange<TRANSPOSE(DataRange))
And then formulas =SUM(INDEX(DataCompare,1,0))
or =SUM(INDEX(DataCompare, ROWS($1:1), 0))
DataRange could be made dynamic
_
...How to Cross-post politely...
..Wrap code by selecting the code and clicking the # or read this. Thank you.
Or this?
Row\Col A B1:E4: {=--(A1:A4<TRANSPOSE(A1:A4))} B A C
Hi Guys,
Thanks again for your help, not sure why my reply didn't post earlier. Sorry for the late response:
1. Richard: the results should be the ordinal position of the elements in the array 0 - x number of elements (final formula will be 0 - x number of unique elements). The problem itself isn't very complicated but the restrictions I'm given call for no helper column and no VBA. I've added an updated spreadsheet for reference.
2. shg: yes that's essentially what I'm trying to do, but return a single 2-D array as the result, if that makes sense
3. mikerickson: yep that's it also, but want to replicate the result in cell J10, assuming the info. in B1:B4 was housed in an array or a named range formula rather than in a range object
4. shg: not exactly sure if I 100% follow the 2nd post. Attached workbook for reference
Thanks again for your help on this, truly appreciated.
How about ...
{=CHAR(MIN(CODE(B1:B4)))}
How bout, you are a genius! I have never even seen this code formula before. That is the key. Only problem is Code gives me first letter of string, I have to figure out a way to get it to still place things in order (i.e. Aa vs Ab, etc.). But I have some hope. This is brilliant
For instance, if I change B2 to "Cb" and B4 to "Ca" and put the formula below in H17:H20 I will get a repeat of the "Cb" text instead of getting "Ca" and then "Cb".
Please Login or Register to view this content.
Maybe it's time to back up and explain what you're trying to do.
Then set the named range DataRange to your array. The point was that INDEX works on arrays, not just ranges. Making the array a named value is one way to make that happen.Originally Posted by cmore
Agreed sorry, I misunderstood what you were pointing out
Sorry so, ultimately I am trying to extract a unique list from a larger list in order, without helper column or VBA
See attached cells H17:H20. If I can get unique codes for each character in the entire string then problem solved it seems.
I wonder though, do you think it's impossible to sum by dimensions without using VBA. I just cannot believe there isn't some formula to handle that.
If you are given an unsorted list in a range, name the range Data.
Then create a named array DataCount RefersTo: =COUNTIF(Data, "<="&Data)
Then the formula
=INDEX(Data,MATCH(ROW(1:1),DataCount,0),1) dragged down, will give you that list sorted.
Thanks Mikerickson, won't this fail when I try to get the unique items within this list? Or are you saying, get the data in order first then try and get the unique info?
Also, I just realized this won't work because, the data source I'm starting with is already reduced through formula
For instance, in the workbook, the steps would be to pull out all the names that are "A", and sort them while only showing unique values. From my understanding Countif won't work because it needs to refer to a Range object? Is there something I'm missing? Trying to see if I can work things out with what you've provided
A George
A Jim
A George
A Suzy
A Rick
C George
Last edited by cmore; 05-30-2015 at 07:14 PM.
That will sort a list, but not eliminate duplicates.
To do that (and sort), create a named range DataColumn that is the entire column of where Data is.
Then
Name: nData RefersTo: =IF(MATCH(Data,DataColumn,0)=ROW(Data), Data)
Name: nDataCount RefersTo: =COUNTIF(nData, "<="&nData)
And the formula =INDEX(Data, MATCH(ROW(1:1), nData, 0), 1)
I'm getting value errors on nDataCount? No?
Also, your way of getting uniques is frigging awesome, no more need for frequency formula.
Last edited by cmore; 05-30-2015 at 08:02 PM.
How about is there a way to "evaluate" text in excel?
can I evaluate this somehow?Please Login or Register to view this content.
To answer that question, yes there is an evaluate feature in excel, http://stackoverflow.com/questions/4...a-real-formula. Trying to figure out if this will solve my problem. Not highly likely.
hey shg, you know if it's possible to convert a string into 'code' entirely or is that too far fetched? So for instance some how excel does this conversion to compare on cell to another, any way to generate this comparison? similar to what we have for the first character and the Code function
In the attached, I've put some data in column A and defined the names
Name: Data RefersTo: =Sheet1!$A$2:INDEX(DataColumn,MATCH("zzzz",DataColumn),1)
Name: DataColumn RefersTo: =Sheet1!$A:$A
Name: DataCount RefersTo: =COUNTIF(Data,"<="&Data)
Name: DataMix RefersTo: =IF(MATCH(Data,DataColumn,0)=ROW(Data),DataCount+ROW(Data)/1000)
Column C shows the intermediate DataCount, Column D shows DataMix.
Neither of these columns is necessary, but are there to show what how these formulas work.
The formula for the result is in G2, and dragged down. (It could also be wrapped in an IFERROR)
=INDEX(DataColumn,1000*MOD(SMALL(DataMix,ROWS($1:1)),1),1)
Since Data is dynamic, you can add to the bottom of column A to test. No intermediate blank rows please.
So first and second of all this is again pretty awesome!! The issue is that the dynamic range you Data provides the rest of the system with a Range object. My issue is that the data will have to meet some criteria which puts it in the form of an Array and then I get Value Errors when I try to use countif
So if you replace Data's formula with the conditional:Everything goes to N/A errors because datacount is now giving me Value errors, because the countif can't take the array formula as a parameterPlease Login or Register to view this content.
Another formulation for DataCount, that doesn't use COUNTIF would be
Name: DataChart RefersTo: =--(data<=TRANSPOSE(data))
Name: oneToN RefersTo: =ROW(Sheet1!$A$1:INDEX(Sheet1!$A:$A,COUNTA(data),1))
Name: DataCount RefersTo: =SUM(INDEX(DataChart, oneToN,0))
The ranges in oneToN are just dummies to create the array {1; 2; 3; ... ;COUNTA(data))
This doesn't require data to be a range, an array will do.
Testing this one out. Shoot unfortunately, the oneToN, the index is just taking the first element in the array rather than indexing each row and then summing by row.
Last edited by cmore; 05-31-2015 at 12:00 AM.
Yeah, I cannot find a way to sum each dimension so you return the single 2D array
Updated example spreadsheet for reference
Hi,
In your PM message you said:There is nothing in J12. I need to know what you expect to SEE in J12, and importantly I need to understand what thought processs you have used to calculate that result. I don't know whether you're wanting a value or some text. I sense that you want some text which involves the items in B1:B4 but I just don't understand exactly what and how the result is arrived at.So the formula I'm trying to replicate is in J12 the original data is B1:B4
the result I expect to see would be the result in J12.
So the table G2:J5 is a visual for the attempted workaround comparing B1:B4 <= Transpose(B1:B4). This is just a visual. and the results G6:J6 or K2:K5
Would you mind please referring to the last file I added to the forum?
Are you wanting a result that is independent of the G2:J5 table (i.e. assuming it's not there or do you see this as a permanent helper set of data?
Sorry about that, I meant J10 is the formula I'm trying to replicate, given an Array as parameter rather than named range. So the formula works with J10, but trying to find a formula that mimics this behavior, see the failed attempt in J11.
We want the values as a single 2D array. Ultimately, I'd take that array and manipulate it further with an aim towards arriving at the unique list of items in b1:B4 that meet some criteria in alpha numeric order.
Sorry the table in G2:J5 is a representation of the method I attempted in order to solve the problem. It isn't necessary to solve the problem.
So what I attempted was to compare the array by doing: (array <= transpose(array)). But this yields a multi-dimensional array. As such, I wonder if we can sum each dimension of that result. The table in G2:J5 is a representation of that. But the final result desired would be g6:j6 or k2:k5 in a single 2D array
Thanks again for your help
Still sacrificing to the many-faced god for a solution to this thing
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks