Hi,
=COUNTIF(OFFSET(A1,,,SEQUENCE(ROWS(A1:A12))),A1:A12)
though it would be nice to find a non-volatile set-up.
Regards
Posts by XOR LX
-
-
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 non-array 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: Formula find max value for multiple if thens.. between: ">" or "<" and a ","
Ah, thanks NBVC. Strange request indeed!
Let's wait for the OP to confirm.
Cheers
-
Re: Formula find max value for multiple if thens.. between: ">" or "<" and a ","
I only asked for a few examples with expected results...
I'm afraid I'll have to pass on this one now. Hopefully someone else will pick up on this thread shortly.
Regards
-
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: Formula find max value for multiple if thens.. between: ">" or "<" and a ","
Hi,
I can't see any examples of strings in your post?
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, mocked-up 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, mocked-up 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
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 array-entry! :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/