Posts by XOR LX
We will be implementing some important changes during 25th and 26th May 2024 which may result in an outage period of the website. We anticipate this process may take a couple of hours and so we apologise in advance for any inconvenience.


XOR LX  Formula works great, if only there was a way not to have to use Array
If by 'array' you mean with CSE (CTRL+SHIFT+ENTER) then a nonarray version is:
=IFERROR(CELL("address",INDEX(I5:I1000,MATCH(1,INDEX(N(I5:I1000<0),0),0))),"NONE")
Regards

Hi,
Array formula**:
=IFERROR(CELL("address",INDEX(I5:I1000,MATCH(1,N(I5:I1000<0),0))),"NONE")
Regards
**Array formulas are not entered in the same way as 'standard' formulas. Instead of pressing just ENTER, you first hold down CTRL and SHIFT, and only then press ENTER. If you've done it correctly, you'll notice Excel puts curly brackets {} around the formula (though do not attempt to manually insert these yourself).



Re: How to Create Function for Matrix Multiplication
Quote from KjBox;792913Happy to be corrected by the formula maestro!
Certainly not the spelling maestro in any case! :shock:
Quote from XOR LX;792902No! They can differ. However, what is true is that the number of columns in the fist must be equal to the number of rows in the second.

Re: How to Create Function for Matrix Multiplication
As requested by Rory, please update your original post to include the link(s).
Regards

Re: How to Create Function for Matrix Multiplication
Quote from KjBox;792762the number of columns in each range must be the same
No! They can differ. However, what is true is that the number of columns in the fist must be equal to the number of rows in the second.
Regards

Re: Formula find max value for multiple if thens.. between: ">" or "<" and a ","
Forgive me, but I have absolutely no idea what you are asking for.
You appear to have given almost exclusively a series of excerpts from your own formulas. Yet I still cannot see a single example with expected result.
Regards


Re: Return Nth Result on a Multiple Criteria Index Match
You're welcome!
Cheers

Re: Return Nth Result on a Multiple Criteria Index Match
Ah, thanks for the clear explanation. I see what you mean now.
=INDEX(Array,SMALL(IF(IF(Criteria1="",1,Range1=Criteria1),IF(IF(Criteria2="",1,Range2=Criteria2),IF(IF(Criteria3="",1,Range3=Criteria3),ROW(Array)ROW(INDEX(Array,1))+1))),nth))
Regards

Re: Return Nth Result on a Multiple Criteria Index Match
Still not sure what you mean precisely.
What do you mean by "If Range1 is blank"? That all the entries within that range are blank?
And what do you mean by "if any of the criteria is missing"? You mean that one or more of the criteria is blank? Or simply not there (if so, what does that even mean?)?
This is why I suggested posting a small, mockedup dataset with expected result...
Regards

Re: Return Nth Result on a Multiple Criteria Index Match
Not sure what you mean there, sorry.
Can you clarify with a small, mockedup example plus expected result?
Regards

Re: Extract text after multiple occurances of special character
You're welcome!
Cheers

Re: Extract a group of seven numbers from a string in a cell
Quote from pike;791079XOR LX did you investigate {1;2;4;8;16;32;64;128} for combinations
You're perfectly correct. Powers of 2 would be a much more rigorous choice for that array, as ikkeman pointed out in the comments to that post.
As I said, I was just feeling a touch 'artistic' the day I created that construction! :idea:
Cheers

Re: Extract a group of seven numbers from a string in a cell
pike :cheers:

Re: Extract a group of seven numbers from a string in a cell
Quote from pike;791016XOR LX would say
If the string is in a1 use Arrayformula
=0+MID("ζ"&A1&"ζ",1+MATCH(26,MMULT(N(ISERR(0+MID(MID("ζ"&A1&"ζ",ROW(INDEX(A:A,1):INDEX(A:A,LEN(A1)6)),8),{1,2,3,4,5,6,7,8,9},1))),{13;1;1;1;1;1;1;1;13}),0),7)Indeed, apart from the fact that doesn't actually require arrayentry! :smile:

Re: Extract a group of seven numbers from a string in a cell
Hi,
Could there also be one or more other numbers of more than 7 digits in length within the string?
Regards

Re: Extract text after multiple occurances of special character
Hi,
I'd personally prefer to have the returns as numeric, rather than text, as returned by your formula.
=0+MID($A1,FIND("ζ",SUBSTITUTE($A1,"#","ζ",COLUMNS($A:A)))+1,5)
and copy to the right.
Of course, this makes the same assumption as your current formula, i.e. that no number is ever greater than 5 digits in length.
Regards
Advanced Excel Techniques: http://excelxor.com/