Search results

Nice solution. Fwiw, i think the above formula could be shortened a little: =SUM((COLUMN(A2:K2)>=LARGE(COLUMN(A2:K2)*(N(+A2:K2)>0),5))*N(+A2:K2))

@r1c1 nice use of BASE function! It follows for the particular case of reversing characters one can fill this formula down from J2 for the same results: =BASE((36^LEN(I2)-1)*9/7-DECIMAL(I2,36),36) (since adding the input string to the reversed character string returns 19, 1A9, 1AA9, 1AAA9, ...

@NarayanK, Thanks for posting this as it helps confirm behaviour of certain functions. Strangely CELL("width") is returning a two element array. The second element of this array sometimes returns FALSE and sometimes TRUE. Does anyone have any ideas what this is? I'm not sure what the purpose is...

interesting... and if you wanted to sum visible columns without using a helper row you could try the following array formula: =SUM(IF(CELL("width",IF(1,+OFFSET(A4:F4,,COLUMN(A4:F4)-MIN(COLUMN(A4:F4))))),A4:F4))

@Maxim, i know how you feel. The above description skips over a few details and it took me a good deal of trial and error to arrive at the final formula. In moving from the separate conditions to the single formula with conditions, careful attention needs to be paid over the signs. There are a...

@John Jairo V - Thanks, i thought this was already quite long and was actually expecting yours to be shorter! Two observations may help to simplify this problem: 1. We can consider a single 24 hour time period in which the two times either lie within the interval or outside the interval. 2...

Good challenge - i think this is the hardest one to date! Maybe this filled over the range: =MOD((D$5>D$6)*($B7-$A7)+($A7>$B7)*(D$6-D$5)+SIGN(D$6-D$5)*(MEDIAN($B7,D$5,D$6)-MEDIAN($A7,D$5,D$6)),1) A7 = Shift Start B7 = Shift End D5 = Interval Start D6 = Interval End

On your test file both methods calculated the column in a fraction of a second on my machine so not sure why it's taking longer on your set up. The basis of the last suggestion is that inserting zero values into the range won't change the result (provided the first value is not zero.) So for...

... or similar to HaseebA's approach: (also non-CSE and much simpler!) =IFERROR(VLOOKUP(A2,A$1:H1,COLUMNS(A$1:H1),0),XIRR(CHOOSE(3+MMULT(-(A2:A$1207=A2),{2,1}),E2:E$1207,G2:G$1207,),CHOOSE(3+MMULT(-(A2:A$1207=A2),{2,1}),D2:D$1207,F2:F$1207,)))

Some extra tweaks can be made to the formula as below to make it: - much faster to calculate (by looking up previous results in the same column) - normal entry formula (without CSE)...

A non-volatile alternative which should also allow for unsorted data.... =XIRR(INDEX(P,N(IF(1,MODE.MULT(IF(P[Sec]=P[@Sec],ROW(P[Sec])*{1,1}))-ROW(P[#Headers]))),N(IF(1,MATCH({"OV","IV"},P[#Headers],0))))...

Further to Luke's explanation on the =LOOKUP("ZZZ",... construction, it may be helpful to consider the following analogy (inspired by a conversation with forum regular Jeff Weir on the divide and conquer method) : Suppose we are looking to find a T-Shirt in a given size from a large rack. Let's...

For your first point, using 256-ROW($1:$255) is clearly shorter and quicker to construct (in the original 2009 google groups post i suggested a similar approach) but there's a couple of things to be aware of with that: 1. Inserting or deleting rows causes the range in ROW($1:$255) to change 2...

@Deepak, yes in practise string length is usually limited and in that case we might go a step further and remove references and array entry too, eg (<100 chars): =NPV(-0.9,,INDEX(IFERROR(--MID(A1,10*{9;8;7;6;5;4;3;2;1;0}+{9,8,7,6,5,4,3,2,1,0},1),""),))% This method was originally just posted...

Thanks! I think MODE.MULT hasn't caught on yet in general formula usage because it's a fairly new function (or else people confuse it with MMULT like me!) Sajan described it intuitively on this forum as "filtering out stuff" - where the filtering out can be based on row numbers, duplicate...

@XOR LX, i'd actually use your approach here too. The MMULT suggestion can make sense where the numbers lie within a limited range. I remember using within a name definition in a rather technical...

@XOR LX. Excellent response to the original question and great blog posts too. A couple of thoughts occurred to me after reading this thread: 1. If using a full row or column reference, it could be a good idea to set that reference to intersect with the cell itself - so if the cell was D5 you...

Ronald, with the maximum 20 entries this took about 6 seconds to calculate the closest 10 matches - which is relatively quick considering it's calculating a matrix of 21 million values (this is helped by using a single array formula approach). But as XOR LX says you will run into difficulties...

The attached workbook uses a single array formula to return the closest three matches among all combinations of numbers. To extend the range size you can replace B8 with B10 and then select the new range to fill, click the formula bar and hold down CTRL+SHIFT+ENTER. Also see here...

Another method using a single array formula to return all six combinations matching the total is in the attached workbook. (ref: http://dailydoseofexcel.com/archives/2012/03/02/introducing-tm-match-target/ - comment by lhm)

Didn't Hui already give two formulas for counting unique values above? (I think you might have missed use of "=1" instead of ">0" in those formulas.)

@bines53. Yes, an xll library function like COUNTDIFF will likely be quickest generally - although not so easy to distribute - similar findings can be found on the fastexcel site. But i find the FREQUENCY formula is 5-10 times quicker on my set up than the MATCH CSE alternative for a larger...

@Somendra Misra: agreed - PT or SELECT DISTINCT query would be my preferred option for this. I believe 2013 version now has distinct count option for this in the PT data model. I haven't been frequenting this forum much so don't know who's who, but usually take the view that responses should be...

SUM+MATCH+CSE method took just under a minute (58.3s) for 100,000 values for me. But it took just a fraction of a second (0.06s) to do the same calculation with sorted data and changing the last argument of MATCH from 0 to 1...

I decided to post an update here after stumbling upon this link (admittedly not one for the faint-hearted!): http://excelxor.com/2014/10/09/coercing-array-returns-from-cse-resistant-formulas/ The article appears to resolve some of the issues raised here. Among other things it demonstrates you...