Search results

To be precise this type of problem cannot be "solved" using any standard program because the state space is continuous, not discrete. (It is however quite possible in the not too distant future from what I understand that AI may have advanced to be able to tackle such problems.) If the problem...

Given a solution has already been provided, is this a challenge to find other solutions? If so, a possible formula for unique alphanumeric delimited values (with Ctrl+Shift+Enter): =TEXTJOIN(",",,FILTERXML("<x><a>"&SUBSTITUTE(A1,",","</a><a>")&"</a></x>","//a[not(. = preceding::a/.)]"))

@Ufoo: did you see Mike Girvin's follow up in magic trick 1314: I believe the second formula in that video can be adapted to your needs (suggested by a certain lori_m by coincidence perhaps :)) Also see the following video in magic trick 1315 to see how the process can be achieved very simply...

Hi David, Yes you're right about inefficiency of rank functions on large data sets but they do provide one sure way of maintaining full accuracy. Otherwise you may need to worry about loss of precision when adding and subtracting large numbers for example with data like =PI() near the end of...

@narunfca: The formula adjustment looks good except perhaps for an additional +1 adjustment as in Shriva's formula in case the maximum value coincides with the minimum of the row (eg changing A9 to 25 and B9 to 25 in the example) One more non-CSE option for fun...

Hi David, That formula works for the particular data set and for data containing whole numbers up to about 8 digits long if data can be located anywhere on the sheet. So for things like dates, scores and record numbers it may make sense to use such a formula particularly on large record sets...

@Ufoo - In the recent follow up challenge to this one, a simplified formula for the particular data setup was suggested. You can see if you can follow that one by stepping through the Evaluate Formula tool and then come back to this formula which is a generalisation of that approach. But I'm...

Hi David, Can you make additional assumptions on the type of numbers in the range? For example assuming integers between 0 and 99 and data starting in row 1 you could try with CSE: =SUM(MOD(LARGE(100*ROW(A1:C10)+A1:C10,ROW(A1:C10)*COLUMNS(A1:C10)),100)) The formula quoted by Shrivallabha was...

Hi Narayan, A scalable and non-volatile version would be quite a lot longer, i think. eg with CSE: =SUM(LARGE(A1:B10,MOD(LARGE(ROW(A1:B10)*10^6+RANK(A1:B10,A1:B10),(ROW(A1:B10)-MIN(ROW(A1:B10)))*COLUMNS(A1:B10)+1),10^6)))

Earlier responses are probably preferable but another option that maybe could be useful if the input ranges were derived from a longer formula: =-SUM(MMULT(A1:B10,{-1,-1;-1,1}/2)^{1,2}^{1,0.5}) based on MIN(A1:B1)=(A1+B1-ABS(A1-B1))/2.

Oops, yes i chose the wrong icon - was meant to be o_O (confused!)

@xlstime admittedly that last response could have been clearer :awesome: I was merely wanting to point out that the second condition was not needed as SLOPE only takes into account the records where x and y values both contain numbers. (Note that this is not the case with the equivalent LINEST...

@xlstime i think this might be sufficient since SLOPE (along with INTERCEPT, RSQ and FORECAST) ignores entries that are not numeric in one or other argument. =SLOPE(IF($B$3:$B$21=I3,$D$3:$D$21),$E$3:$E$21) Entered using Ctrl+Shift+Enter. (As others stated above insert either MMULT(...,1) or...

@Narayan - nice idea. It works ok in this case but what if there is more than one entry equal to the maximum value? (eg if 15 is changed to 20 in the fourth line then that proposal will not include Smith ) Perhaps an alternative that only counts any maximum once is...

David, as you say you need to use TRANSPOSE for that formula to work as it relies on the calculation going from left to right. But in this case, i don't think you need that complicated PROB construction as the column index is available in row 2, so something like this should be ok (with CSE)...

or using LOOKUP, maybe try with CSE: =FORECAST(,IF((LOOKUP(,A5:F5,A2:F2)-A2:F2+0.5)^2<1,A2:F2),A5:F5)

David, maybe you had something like this in mind? =PERCENTRANK(A5:F5,,20)*MAX(A2:F2)

Also see the latest blog post of @Colin Legg: https://colinlegg.wordpress.com/2016/03/06/unwanted-worksheet-events-maybe-specialcells-is-to-blame/

Haven't much experience with the morefunc add-in so can't help with that issue. For the first argument another option is MODE.MULT(ROW(A1:A8)*{1,1},0,0). And one other possibility that avoids needing to join the arrays is: =PROB(ROW(A1:A8)*{1,0,0,0},A1:A8*{1,0,0,0}+{0,9,-9,1}^99,1,ROW(A1:A8))

I believe there is a rounding issue because PROB checks that all the values sum to one. Try with CSE =SUM(A1:A623/SUM(A1:A623))=1 next to your formula, I think PROB will return an error whenever this SUM formula returns FALSE.

@John Jairo V - just noticed the reference to this thread which i'd almost forgotten about: (http://www.pcreview.co.uk/threads/array-formula-returning-the-cumulative-sum.3121728/) That formula works fine in this case, however it can fail on larger data sizes or if the data sums to zero. For...

Great idea to generate {min,max} via aggregate function! Not really any clearer but for interest's sake perhaps it's possible to save a couple of characters via the little used AVEDEV function, since: sum({min,max}*{-1,1}) / 2 = avedev({min,max}) (I think one could also use stdev.p in place...

For a formula that returns all results {3;4;6;8;12;16;24;48} you can try: =MODE.MULT(IF(MOD(50,ROW(2:50))=2,ROW(2:50),{"",""})) If A1=50 and A2=2 you can change the 2:50 to INDEX(A:A,2):INDEX(A:A,A1) and the 2 to A2 as in John Jairo V's link. With this replacement the formula will recalculate...

For anyone interested, it turns out the second element of CELL("width",A1) determines whether the column is set to use the standard column width. So =INDEX(CELL("width",A1),2) is equivalent to Range("A1").UseStandardWidth in VBA and to GET.CELL(16,A1) (second element) in the old XLM macro...

@Asheesh: indeed, there seems to be a common myth that unary plus doesn't do anything, and i have yet to see it used elsewhere in forums. The function of the plus sign is essentially to convert a range to an array but it does have a number of uses where the effect is not necessarily immediately...