Search results

Hi David, That works better but I'm not 100% sure about it due to decimal approximations. It leads me to one final idea that only compares whole numbers... Replace EXP(n) with the n th prime number and multiply! =PRODUCT(CHOOSE(H1:K1,1,2,3,5,7)) i.e. 1*1*1*2 and the number of matches in the...

Further testing shows all previous EXP formulas should really be adjusted to a wider tolerance, so instead of: =SUM(EXP({3,3,3,1}))=SUM(EXP({1,3,3,3})) which returns FALSE, use something like: =ABS(SUM(EXP({3,3,3,1})-SUM(EXP({1,3,3,3}))<1E-12

Indeed you're right, that MATCH formula only works reliably with unique criteria. But then COUNTIF is not reliable in general either eg 1, 1, 1, 1 could match criteria 1, 1, 2, 2 with that kind of logic. Alternatively, a formula that combines the two approaches could work well for any data type...

Hi David, I think the first alternative you give does better than the second for non-zero integers. eg 1, 2, 2, 37 and 1, 2, 2, 36 will be matched in the formula above. But thanks for the interesting idea. Anyone else interested in the theory behind the formula can follow the proof that e is...

Hi David, It's a nice idea in theory based, i believe, on the principle that exp(n) is not a linear combination of any lower powers {exp(1),..,exp(n-1)} i.e. e is a transcendental number which was first shown in 1882 (https://en.wikipedia.org/wiki/Transcendental_number). For example it would...

Hi @Rushikesh Ambekar . Try this variation: =XIRR(IF({1,0},G5:G8,INDEX(-L5:L8,)),IF({1,0},C5:C8,H5:H8))

For three ranges use CHOOSE({1,2,3} instead. IF is just used in the case of two ranges for brevity.

This might work for you with CTRL+SHIFT+ENTER: =XIRR(IF({1,0},G5:G8,-L5:L8),IF({1,0},C5:C8,H5:H8)) In the original attachment you can also try entering: =XIRR(IF({1;0},B9:F9,B12:F12),IF({1;0},B8:F8,B11:F11))

Playing around with these ideas further, another option is to define 'p' as, =ROW()-MIN(ROW())+1 This returns a reference-free 1-based when array-entered in a range. Then array-enter: =IFERROR(INDEX(...,p),"") where ... is any formula that returns an array (which can be much more efficient...

Nice! I like the unicode naming convention and the technique to reference shapes from formulas. Combining INDEX with IFERROR to remove errors in the return array is new to me and could be useful in lots of situations (by inserting an extra IF function if necessary as mentioned previoisly). From...

Your digressions sound interesting - perhaps you could attach a demo file? In a simplified set up containing first and last names in A1:B5, this multi-cell version: =IFERROR(IF(1,INDEX(A1:B5,{1;3;4},)),"") can be entered into D1:E5 using CSE to return a subset of these names. I'm not quite...

I'd agree that when a function has named formulas as arguments it is often preferable to enter over a range of cells with CSE than to enter individual cell formulas. This can cut down on time consuming recalculations of names like in your definition of pointer. Open document format specifies...

@Peter Bartholomew - Indeed array processing can be applied generally to such value arguments - whether that's a good thing or not is open to debate. I noticed on the EuSprig website an article by an author with the same name deprecating the use of such array formulas in practice. Your...

Surekha - please try the code samples provided. Both NarayanK's and my suggestion use the replace method to work around the formula length limitation.

Sub ArrayFormula() With Sheet1.Range("C3:H10") .FormulaArray = _ "=INT(MOD(MOD(SMALL(ABS(#REF!)+ROW(B2:INDEX(B:B,2^ROWS(B3:B10))),C2:H2),10^7)/2^(ROW(B3:B10)-ROW(B2)-1),2))" .Replace "#REF!", _...

Here are a few further notes relating to the questions raised above... First off, contrary to what I said previously, tests on various formulas in the attachment indicate there is no significant difference with using CSE on formulas and that multi-cell array formulas can be fastest for...

@NARAYANK991, Yeah that wasn't phrased very clearly. What i meant was the time span from first day to last day. Another interpretation could be length of a work contract from first day to last worked and blanks are vacation or sick days. @Naceur, you could also use "*" in place of "~x" (but...

Hi @callao2908, As the formula returns more than one result you can use INDEX to get the other values: =INDEX(FILTERXML("<x><a>"&SUBSTITUTE(A2," ","</a><a>")&"</a></x>","//*[string-length(text()) = 2]"),2) Not sure what that would be in Spanish?

i thought of a better example to illustrate the "trickiness" of the IF function... Suppose you want to find attendance for a course from first day to last in columns A and B (where days attended are marked with an "x" and blank otherwise): 01-Sep 04-Sep x 05-Sep x 06-Sep 07-Sep x 08-Sep...

Another option (for use in Excel 2013+) to return an array containing text elements of a given length: =FILTERXML("<x><a>"&SUBSTITUTE(TRIM(A2)," ","</a><a>")&"</a></x>","//*[string-length(text()) = 2]") eg: A2 = "a ss ddd ffff ggg hh j" -> {"ss";"hh"}

Perhaps worth adding that this kind of operation could also be done in one "single hit": Sheet2.[B2:B8]=Application.Vlookup(Sheet2.[A2:B8],Sheet1.[A2:B8],2,0) To make this dynamic either replace the references with ranges similar to method above or using Names/Table references.

@Naceur: Your example does raise some interesting points. Here is my 2 cents: Ctrl+Shift+Enter. As others have said fixes the issues, but may be a bit slower to recalculate. In my experience even adding a few extra functions to avoid CSE can be faster and is also more efficient when copying...

Ah, thanks for that clarification. i tried to make the list as comprehensive as possible - 600 functions in all (including little known ones such as NUMBERSTRING and DATESTRING used in far east versions.) A slicer was added to show only the more common ones but i had forgotten this is only...

The first sheet of the FuncList workbook attachment contains a formatted list of all functions and arguments and was created by copying the last two columns from the List sheet as text and pasting into the table. Array types are shown in bold; Reference types are shown in italics Value types...

Nice technique. Maybe worth noting one can remove the N() function: =IFERROR(INDEX(Sheet2!$B$1:$I$1,AGGREGATE(15,6,COLUMN(Sheet2!$B$1:$I$1)-1/Sheet2!$B2:$I2,IF(1,ROW(Sheet2!$A$1:$A$8)))),"") That said, with only 8 names it'd be simpler and more efficient to just fill down...