# Advanced Formula Challenge #12: An Array of Matches 80

The challenge this week is as follows: given a range of arbitrary size in which each entry is either 0 or 1 and in which each row contains at least one occurrence of a 1, a single formula to return an array consisting of the relative column positions of the first occurrence of a 1 within each row.

For example, given the below in A1:E10:

the solution would be the array:

`{2;1;1;2;1;5;1;4;1;3}`

Readers may reference the range A1:E10 in their solution, though of course being aware that this choice is purely arbitrary and hence that any solution must also hold for a range of any size.

Readers should also note that the entries in the returned array are to be the relative column positions within the range (just as if we’d used MATCH on each of the rows within that range). As such, moving the above range to, for example, H1:L10 would have no impact on the output of any solution.

Also note that this is NOT a shortest formula challenge!

Solution next week. Good luck!

1. Alex Groberman says:

Nice challenge!

Taking advantage of your binary specifications, and assuming in this case a 10×10 grid (A1:J10):

`=COLUMNS(A1:J1)-TRUNC(LOG(MMULT(A1:J10*2^(COLUMNS(A1:J1)-COLUMN(A1:J1)),ROW(A1:A10)^0),2))`

-Alex

2. @Alex

Can you clarify what results that formula gives when the number of rows in the range is not equal to that for the number of columns?

Have you got your matrix multiplication correct?

Perhaps you should stick – as I specified – to referencing the range A1:E10, just so that your answer will be consistent with any others that may be posted.

Regards

3. aMareis says:

Hi, XOR LX
Here’s a my first try.

`=COLUMNS(A1:E10)-INT(LOG(MMULT(A1:E10,2^(COLUMNS(A1:E10)-ROW(INDIRECT("1:"&COLUMNS(A1:E10))))),2))`
4. @aMareis

Faultless technique. Well done!

5. Alex Groberman says:

@XOR LX

Fixed (and also shamelessly changing TRUNC to INT after aMareis’ posting):

`=COLUMNS(A1:E1)-INT(LOG(MMULT(A1:E10*2^(COLUMNS(A1:E1)-COLUMN(A1:E1)),TRANSPOSE(A1:E1*0+1)),2))`
6. @Alex

And a great fix too! 🙂

Another faultless – and subtly different – solution. Good stuff!

7. Maxim Zelensky says:

On my mind, aMareis made better solution, because you can refer to named range (for example, binary matrix is in range named “Matrx”). Alex solution is also really cool, but you have to refer to a single row in a range.

Anyway, both solutions a great! As for me there is too much maths to find appropriate way, specially LOG part 🙂

8. Asheesh says:

Hi – I am generally not so good in such challenges, however, below is my immature try…

`=MATCH(1,(OFFSET(\$A\$1,ROW(\$A\$1:\$A\$10)-1,,,COUNTA(\$A\$1:\$E\$1))),0)`
9. @Asheesh

I take it you’ve tested your solution? Can you just confirm what result it gives for you?

Regards

10. John Jairo V says:

Another aproach could be:

`=FIND(1,TEXT(MMULT(A1:E10*10^(COLUMNS(A1:E10)-COLUMN(A1:E10)+MIN(COLUMN(A1:E10))-1),TRANSPOSE(COLUMN(A1:E10)^0)),REPT(0,COLUMNS(A1:E10))))`

I’m looking for another solution. Blessings!

11. @John Jairo

Very original! Like it! Interesting use of FIND and TEXT and certainly not an approach which I’d considered.

Excellent stuff. Cheers!

12. sam says:

My approach is similar John Jairo

`=FIND(1,TEXT(MMULT(A1:E10,TRANSPOSE(10^(COUNT(A1:E1)-COLUMN(A1:E1)))),REPT("0",COUNT(A1:E1))))`
13. @sam

And I’m sure John won’t mind me saying that you’ve improved that set-up considerably.

Excellent work yet again. Very impressed with all contributions so far to this challenge.

Regards

14. Alex Groberman says:

@Maxim

Based on your feedback I’ve revised mine slightly (making it more similar to aMareis in the process):

`=COLUMNS(A1:E10)-INT(LOG(MMULT(A1:E10,2^TRANSPOSE(COLUMNS(A1:E10)-COLUMN(A1:E10))),2))`

-Alex

15. Alex Groberman says:

Oops I was too quick on the gun, needs to be revised to:

`=COLUMNS(A1:E10)-INT(LOG(MMULT(A1:E10,2^TRANSPOSE(COLUMNS(A1:E10)-COLUMN(A1:E10)+MIN(COLUMN(A1:E10))-1)),2))`

Not quite as pretty anymore…

16. Isaac says:

@Sam, @JohnJairo

Fellow, I like your solutions, but I’m confused about the use of the COLUMN(A1:E1) construction in your formula…

I don’t understand how to make the formula work when I move the data to Columns H:L — as indicated in the original post.

Perhaps I’m missing something?

17. Isaac says:

Correction — John’s worked in columns H:L; but I still can’t get Sam’s to work…

18. @Isaac

Good point. Thanks.

Although it would only appear to be Sam’s construction which fails if the range is changed. John Jairo’s seems sound to me. Can you clarify?

Regards

19. Lori says:

@XOR LX – Nice challenge and great ideas presented so far. One issue i have is that in the spec it says “any solution must also hold for a range of any size” so perhaps this could be more strictly adhered to.

For example the solution of aMareis and Alex with a range containing a row of 47 ones returns zero for that line. Also JJV and sam’s suggestions allow only up to 255 columns.

I haven’t given much thought to it but it should be possible to overcome some of these limitations by operating on ranges without converting to numbers or strings. I guess you may have had something like that in mind?

@John Jairo V – your challenge over at Chandoo is a good one – I hope you get some more takers…

20. @Lori

Agreed. I confess that I have been quite unrigorous in my checking of solutions presented so far, which does seem somewhat pointless given that, as you say, I laid out quite specific guidelines.

And you’re also correct when you intimate what I had in mind. The solution I myself derived is not thus restricted. However, I think I was overly-swayed by the ingenuity of some of the posts so far (which in my opinion have showed impressive creativity, if at a cost of rigour), and so “let my guard down” when it came to submitting those solutions to a thorough analysis.

In fact, as I have done several times in these formula challenges, I again shot myself in the foot somewhat. I had originally intended for this challenge to serve as an opener for the general problem of obtaining an “array of MATCHes”, i.e. given any values in the range. The fact that I then stipulated that this range would consist of only either 0s or 1s did not, at the time, seem like it should make a difference, though of course I had not then envisaged the ingenious solutions of Alex and aMareis. Had I done so, I would most certainly have set it up so that the non-unity entries were random integers (or perhaps, even better, all text).

Still, sometimes good things come out of such “accidents”, and from the solutions given thus far I have to say that I’m almost glad that I made a slight slip in the designing of this challenge.

Still, that doesn’t excuse the poor job of checking that I’ve done, so I’ll be sure to improve in that department for the next challenge.

Regards

21. aMareis says:

Here’s an another try.

`=MOD(SMALL(IF(A1:E10=1,ROW(A1:E10)*10^5+COLUMN(A1:E10)-MIN(COLUMN(A1:E10))+1),SUBTOTAL(9,OFFSET(A1,,,ROW(A1:E10)-MIN(ROW(A1:E10))+1,COLUMNS(A1:E10)))-MMULT(A1:E10,TRANSPOSE(COLUMN(A1:E10)^0))+1),10^5)`

Thank you.

22. I also found that aMareis’ will fail for certain combinations of contiguous zeros and contiguous ones. eg 1 zero followed by 46 ones fails, as does 2 zeroes followed by 45 ones. 3 zeros followed by 44 ones works, as does other combinations with increasing leading zeroes.

Very clever, though.

To make Sams and JJ’s formulas truly dynamic – so that they could work on a range called Data wherever that range is located – I added -COLUMNS(Data)-1 to the end.

Well done, all.

23. Asheesh says:

24. Maxim Zelensky says:

Hmmm…. based on solutions by @Sam and @JohnJairo, I got this one:

`=FIND(1,TEXT(MMULT(A1:E10,10^(COLUMNS(A1:E10)-ROW(INDIRECT("1:"&COLUMNS(A1:E10))))),REPT(0,COLUMNS(A1:E10))))`

Tested on named range, which location on sheet is random. At least up to 100 columns in tested range it works fine for me. Hope I correctly translated formula to English version.

25. @Maxim

You say “up to 100 columns“. Are you aware of the precise limitation here? Have you read Lori’s contribution?

Although I’ve been a bit slack in terms of adhering to the specifications that I laid out, recall that I did say that a solution should work for any range.

Regards

26. Maxim Zelensky says:

@XOR LX, got it. Yes, my decision has limitations, at least on string length and max 10^308.

Ok, still thinking on this…

27. aMaris – you need to get rid of that hard-coded 5 in your 10^5 so that the formula handles a range of arbitrary size. Amending your formula accordingly – and using the named range Data – gives this:

`=MOD(SMALL(IF(Data=1,ROW(Data)*10^COLUMNS(Data)+COLUMN(Data)-MIN(COLUMN(Data))+1),SUBTOTAL(9,OFFSET(Data,,,ROW(Data)-MIN(ROW(Data))+1,COLUMNS(Data)))-MMULT(Data,TRANSPOSE(COLUMN(Data)^0))+1),10^COLUMNS(Data))`
28. Okay, I’ve got one:

`=MATCH(TRANSPOSE(ROW(Data)),INDEX(Data*ROW(Data),N(IF(1,1+(INT((COLUMN(INDEX(1:1,1):INDEX(1:1,COLUMNS(Data)*ROWS(Data)))-1)/COLUMNS(Data))))),N(IF(1,1+(MOD((COLUMN(INDEX(1:1,1):INDEX(1:1,COLUMNS(Data)*ROWS(Data)))-1),COLUMNS(Data)))))),0)-TRANSPOSE((ROW(Data)-MIN(ROW(Data)))*COLUMNS(Data))`

Using the Redimensioning from the last post.

I think I can do away with the transpose at the end with some effort

29. @Jeff

Nice! Good application of the material in the previous post!

Cheers

30. Here it is, without the TRANSPOSE and oriented the correct way:

`=MATCH(ROW(Data),INDEX(Data*ROW(Data),N(IF(1,1+(INT((COLUMN(INDEX(1:1,1):INDEX(1:1,COLUMNS(Data)*ROWS(Data)))-1)/COLUMNS(Data))))),N(IF(1,1+(MOD((COLUMN(INDEX(1:1,1):INDEX(1:1,COLUMNS(Data)*ROWS(Data)))-1),COLUMNS(Data)))))),0)-(ROW(Data)-MIN(ROW(Data)))*COLUMNS(Data)`
31. aMareis says:

@Jeff Weir

32. Maxim Zelensky says:

@Jeff wow… Wow!!! Excellent!

PS you can replace COLUMNS(Data)*ROWS(Data) with COUNT(Data) – a little bit easier… 🙂

33. Maxim: good point.

`=MATCH(ROW(Data),INDEX(Data*ROW(Data),N(IF(1,1+(INT((COLUMN(INDEX(1:1,1):INDEX(1:1,COUNT(Data)))-1)/COLUMNS(Data))))),N(IF(1,1+(MOD((COLUMN(INDEX(1:1,1):INDEX(1:1,COUNT(Data)))-1),COLUMNS(Data)))))),0)-(ROW(Data)-MIN(ROW(Data)))*COLUMNS(Data) `
34. Maxim Zelensky says:

Oops… Jeff, test your solution on a range with a number of cells more than 16348. For example, 130×130. It returns an error because columns count exceeds limit on sheet.

35. Ah yes. I can change the formula around to increase that, but I’m still going to be limited by 1048576 cells.

36. Maxim Zelensky says:

@Jeff, would you please? I think it will be best solution for now, regarding array size

37. aMareis says:

@Jeff Weir

You misunderstand my formula.
5 of 10^5 is not mean column’s count.

Thanks.

38. Ron Coderre says:

Couldn’t reists trying this one.

This formula is durable to any size range…(If you’ve got the patience to wait for REALLY large ranges to be calculated):

`=ROUND(COLUMNS(A1:E1)+1-ROUND(MOD(LARGE(ROWS(A1:E10)-ROW(A1:E10)+1+(10^-7*A1:E10*(COLUMNS(A1:E1)-(COLUMN(A1:E1)-COLUMN(A1)))),1+((ROW(A1:E10)-ROW(A1))*COLUMNS(A1:E10))),1),7)*10^7,0)`

I tested it on D5:XES132 and it took several seconds to calculate, using this

`=ROUND(COLUMNS(D5:XES5)+1-ROUND(MOD(LARGE(ROWS(D5:XES132)-ROW(D5:XES132)+1+(10^-7*D5:XES132*(COLUMNS(D5:XES5)-(COLUMN(D5:XES5)-COLUMN(D5)))),1+((ROW(D5:XES132)-ROW(D5))*COLUMNS(D5:XES132))),1),7)*10^7,0)`

Maybe I did something wrong, but I couldn’t get the other posted formulas to work on that range.

39. @aMareis: My apologies…I misunderstood the intent.

40. @Maxim:

Here’s my revised formula, which should handle tables with up to 1048576 cells. Not that I’ve tested it!

`=MATCH(TRANSPOSE(ROW(Data)),INDEX(Data*ROW(Data),N(IF(1,1+(INT((ROW(INDEX(A:A,1):INDEX(A:A,ROWS(Data)*COLUMNS(Data)))-1)/COLUMNS(Data))))),N(IF(1,1+(MOD((ROW(INDEX(A:A,1):INDEX(A:A,ROWS(Data)*COLUMNS(Data)))-1),COLUMNS(Data)))))),0)-TRANSPOSE((ROW(Data)-MIN(ROW(Data)))*COLUMNS(Data))`
41. @Ron: that’s awfully clever. Here’s a generalised version:

`=ROUND(COLUMNS(Data)+1-ROUND(MOD(LARGE(ROWS(Data)-ROW(Data)+1+(10^-7*Data*(COLUMNS(Data)-(COLUMN(Data)-MIN(COLUMN(Data))))),1+((ROW(Data)-MIN(ROW(Data)))*COLUMNS(Data))),1),7)*10^7,0)`

…where Data is a named range pointing at the 2d block.

42. @Ron: give the range the name Data, then try the following:

aMaris:

`=MOD(SMALL(IF(Data=1,ROW(Data)*10^5+COLUMN(Data)-MIN(COLUMN(Data))+1),SUBTOTAL(9,OFFSET(Data,,,ROW(Data)-MIN(ROW(Data))+1,COLUMNS(Data)))-MMULT(Data,TRANSPOSE(COLUMN(Data)^0))+1),10^5)`

Maxim:

`=FIND(1,TEXT(MMULT(Data,10^(COLUMNS(Data)-ROW(INDIRECT("1:"&COLUMNS(Data))))),REPT(0,COLUMNS(Data))))`

Alex:

`=COLUMNS(Data)-INT(LOG(MMULT(Data,2^TRANSPOSE(COLUMNS(Data)-COLUMN(Data)+MIN(COLUMN(Data))-1)),2))`

Sam:

`=FIND(1,TEXT(MMULT(Data,TRANSPOSE(10^(COLUMNS(Data)-COLUMN(Data)))),REPT("0",COLUMNS(Data))))`

John:

`=FIND(1,TEXT(MMULT(Data*10^(COLUMNS(Data)-COLUMN(Data)+MIN(COLUMN(Data))-1),TRANSPOSE(COLUMN(Data)^0)),REPT(0,COLUMNS(Data))))`

Jeff:

`=TRANSPOSE(MATCH(TRANSPOSE(ROW(Data)),INDEX(Data*ROW(Data),N(IF(1,1+(INT((ROW(INDEX(A:A,1):INDEX(A:A,ROWS(Data)*COLUMNS(Data)))-1)/COLUMNS(Data))))),N(IF(1,1+(MOD((ROW(INDEX(A:A,1):INDEX(A:A,ROWS(Data)*COLUMNS(Data)))-1),COLUMNS(Data)))))),0)-TRANSPOSE((ROW(Data)-MIN(ROW(Data)))*COLUMNS(Data)))`

@XOR: Might I suggest that for future challenges you stipulate that a named range called d must be used, and any other ranges must be constructed from that? That way it makes it easy for all to not only test these, but reuse them.

A sample spreadsheet wouldn’t go amiss either…was a bit of a pain to type out all those ones and zeros to match your screenshot.

43. Ron Coderre says:

Restating the formula I posted, utilizing a referenced range named “Data”:

`=ROUND(COLUMNS(Data)+1-ROUND(MOD(LARGE(ROWS(Data)-ROW(Data)+1+(10^-7*Data*(COLUMNS(Data)-(COLUMN(Data)-MIN(COLUMN(Data))))),1+((ROW(Data)-MIN(ROW(Data)))*COLUMNS(Data))),1),7)*10^7,0)`
44. chrisham says:
`=AGGREGATE(15,6,COLUMN(Data)*IF(INDEX(Data,ROW(A1),)=0,"",INDEX(Data,ROW(A1),)),1)=COLUMN(A1)`

Data is the name range

45. Maxim Zelensky says:

@Jeff, thanks!!!

@Ron, I still do not understand how your solution works, but tested it on 3489366 cells (213 rows and 16382 columns)… and as far as I can see, it works on this really big amount of data. Even if 1 is in last column only. Excellent!

46. @Ron

Excellent stuff!

Ingenious use of LARGE means that this set-up is indeed applicable to very large ranges.

Thanks a lot.

47. @chrisham

Thanks, but I couldn’t get your solution to work. In fact, it appears that it will only ever return a single value, not an array as required.

Can you clarify?

Regards

48. @Jeff

Completely agree re your point about Named Ranges – will do so for any future challenges.

I usually add the attachment, though didn’t feel it was necessary here, since in any case the actual values that I gave were of course arbitrary, and so I presumed that readers would set up their own sheets with some random-generating functions for testing purposes.

However, I shall endeavour to post the attachment in future, even if I feel it is not necessarily required.

Regards

49. I timed these on 10 columns * 2000 rows = 20,000 cells. Here’s the recalc times:

Ron Coderre: 24 seconds
aMaris: 17 seconds
Jeff: 0.7 seconds
Sam: 0.014 seconds
John Jairo V: 0.016 seconds
Maxim Zelensky: 0.01 seconds
Alex Groberman: 0.006 seconds

For some reason though, it took nearly 5 minutes of times from when I array-entered my formula to when it showed the result in the spreadsheet. It didn’t show ‘calculating’ in the status bar during this time. I tried several times: Excel appears to go unresponsive, and then finally returns the numbers. But when I time the ‘entered’ formulas, I get the fast time shown above.

Weird.

50. Maxim Zelensky says:

My friend from other forum, where asked for solution of this task, suggested this formula with limit up to 1022 columns:

`=-INT(LOG(MMULT(Data,2^-ROW(OFFSET(A1,,,COLUMNS(Data)))),2))`

Really formula works fine with 16384 columns, but 1022 is a maximum relative position for 1s.

For a range started in 1st column, he suggested shorter solution with the same limits:

`=-INT(LOG(MMULT(Data,TRANSPOSE(2^-COLUMN(Data))),2))`
51. @Maxim

Then tell your friend to visit this site more often! With elegant constructions such as those it would be nice indeed to have their input on a regular basis.

Regards

52. Ron Coderre says:

@Maxim

I really like that approach. It’s moderately easy to understand and it’s very flexible.

Here are examples of how I altered it to return the first position of various values for each referenced row.

First Positive value (if no value is below zero):

`=-INT(LOG(MMULT(SIGN(Data),10^-ROW(OFFSET(A1,,,COLUMNS(Data)))),10))`

First Positive value (ignoring negative values, if they exist)

`-INT(LOG(MMULT(--(Data>0),10^-ROW(OFFSET(A1,,,COLUMNS(Data)))),10))`

First Non-Zero value

`=-INT(LOG(MMULT(--(Data<>0),10^-ROW(OFFSET(A1,,,COLUMNS(Data)))),10))`

First occurrence of a particular word (allowing for the word to be missing)

`=IFERROR(-INT(LOG(MMULT(--(Data="frisbee"),10^-ROW(OFFSET(A1,,,COLUMNS(Data)))),10)),0)`
53. Ron Coderre says:

Hmmm…the website didn’t allow the Not-Equal sign in the First Non-Zero value. It simply removed it and left Data0.

Interesting.

54. @Ron

Yes – sorry about that. Have corrected now.

Regards

55. Maxim Zelensky says:

Hope he will visit your site soon.

BTW, another man suggested this solution:

`=MATCH(1,INDEX(Data,ROW(Data)-MIN(ROW(Data))+1,),)`

which, being entered CSE in range of cells on sheet, returns correct results. But it is impossible (for me) return as a result an array in any form. How it could be enveloped in other function to get an array? Or this is impossible?

56. MichaelCH says:
`=-INT(LOG(MMULT(Matrx,TRANSPOSE(10^-COLUMN(Matrx)))))`
57. Maxim Zelensky says:

and another one, this time mine 🙂

`=--RIGHT(NEXT(MMULT(Data*10^(-COLUMN(Data)+MIN(COLUMN(Data))-1),TRANSPOSE(COLUMN(Data)^0)),"0E+000"),3)`
58. @Maxim

Ah no! Of course that multi-cell array solution is disallowed! We must create an array containing the results, not a range of worksheet cells.

And I do not believe that that set-up can be translated as such.

P.S. Re your latest offering, is NEXT a new 2013 function that I don’t know about? 🙂

Cheers

59. @MichaelCH

Not sure about that one. Have you tested thoroughly?

Regards

60. `=-INT(LOG(MMULT(A1:E10,10^-(ROW(INDIRECT("1:"&COLUMNS(A1:E10)))))))`

a better Version from MichaelCH function (if the range ist moving)

61. @XOR LX – Maxim meant TEXT not NEXT

62. Maxim Zelensky says:

MichaelCH is the author of this fine LOG formula I have posted before. He does not speak English (almost), so he, may be, missing some things.

Formula he posted is, as far as I can see, for data range started in 1st column, and has a limit of 308 columns. It is shortest formula solution (yes, here it is Advanced challenge, but on that our forum we used to shortest formula challenge, that’s why 🙂 )

Ah, and yes, NEXT ought to be TEXT – misspelled while translated formula ))

63. Constructing a general formula from this fruitful discussion with the possible occurrence of errors in the data array.

`=SEARCH(1,TEXT(MMULT(IFERROR(--Check,0),TRANSPOSE(10^(MAX(COLUMN(Data))-COLUMN(Data)))),REPT(0,COLUMNS(Data))))`

Where Data is the array and Check is the comparison.

Some general comparisons:

First empty cell: (Data=””)
First non-empty cell: (Data””)
First number: ISNUMBER(Data)
First text: ISTEXT(Data)
First logical value: ISLOGICAL(Data)
First error: ISERROR(Data)

64. Hi Mike. Nice generalisations. Note that you don’t need that IFERROR in there.

65. Sorry, meant to address that to XLarium.

66. This construction from aMareis seems incredible to me:

`=SUBTOTAL(9,OFFSET(Data,,,ROW(Data)-MIN(ROW(Data))+1,COLUMNS(Data)))`

I’ve not come across this before. It doesn’t evaluate correctly using either the F9 trick or the Evaluate Formula functionality.

If I’m understanding it correctly, it is producing multiple 1D arrays on the fly form Data that SUBTOTAL is then processing.

How the heck does that work? How on earth did you come across it, aMareis?

67. @Jeff Weir

As I stated the data array could contain error values. Thus IFERROR is needed.

68. @Ron: Your formula can be further simplified by ditching a pair of brackets as well as a +1 – i.e. the bits in bold below:

`=ROUND(COLUMNS(Data)+1-ROUND(MOD(LARGE(ROWS(Data)-ROW(Data)+1+(10^-7*Data*(COLUMNS(Data)-(COLUMN(Data)-MIN(COLUMN(Data))))),1+((ROW(Data)-MIN(ROW(Data)))*COLUMNS(Data))),1),7)*10^7,0)`

…which leaves this:

`=ROUND(COLUMNS(Data)+1-ROUND(MOD(LARGE(ROWS(Data)-ROW(Data)+Data*10^-7*(COLUMNS(Data)-(COLUMN(Data)-MIN(COLUMN(Data)))),1+((ROW(Data)-MIN(ROW(Data)))*COLUMNS(Data))),1),7)*10^7,0)`

…although for the sake of explaining it, I find it easier to add this:

`=ROUND(COLUMNS(Data)+1-ROUND(MOD(LARGE(ROWS(Data)-ROW(Data)+MIN(ROW(Data))+Data*10^-7*(COLUMNS(Data)-(COLUMN(Data)-MIN(COLUMN(Data)))),1+((ROW(Data)-MIN(ROW(Data)))*COLUMNS(Data))),1),7)*10^7,0)`
69. @Ron: The outer ROUND seems to be superfluous too.

This seems to work just fine:

`=COLUMNS(Data)+1-ROUND(MOD(LARGE(Data*10^-7*(COLUMNS(Data)-(COLUMN(Data)-MIN(COLUMN(Data))))+ROWS(Data)-ROW(Data),1+((ROW(Data)-MIN(ROW(Data)))*COLUMNS(Data))),1),7)*10^7`
70. Ola.S says:

Just for fun…

Felt simpler with…

`=INT(LOG(1/MMULT(Data;10^-TRANSPOSE(COLUMN(Data)))-1)+1)`

…than where =INT(-1,9) –> -2

`=-INT(LOG(MMULT(Data;10^-TRANSPOSE(COLUMN(Data)))))`
71. MichaelCH says:

-INT(-A1) = ROUNDUP(A1,0)

-INT(-A1) INT(A1)+1

-INT(-1.9) = 2
-INT(-2) = 2

72. Hi Ola.S. Nice formulas. Note that to handle the data being located elsewhere, you’d need to amend them to:

`=INT(LOG(1/MMULT(Data,10^-TRANSPOSE(COLUMN(Data)))-1)+2-MIN(COLUMN(Data)))`

…and

`=-INT(LOG(MMULT(Data,10^-TRANSPOSE(COLUMN(Data)-MIN(COLUMN(Data))+1))))`
73. Siva says:
`=SMALL(IF(A1:E10=1,COLUMN(A1:E10),COLUMNS(A1:E10)+1)+ROW(A1:E10)*10^10,(ROW(A1:E10)-1)*COLUMNS(A1:E10)+1)-ROW(A1:E10)*10^10`

Try this array formula

74. I found this challenge a today. I guess I’m late to the party, but this is what I came up with:

`=FIND(1,MMULT(Data,10^-TRANSPOSE(COLUMN(Data))))-2`
75. Nice Daniel. Needs a slight modification to handle the data being located elsewhere:

```=FIND(1,MMULT(Data,10^-TRANSPOSE(COLUMN(Data)-MIN(COLUMN(Data))+1)))-2/pre>
```
76. Lori says:

Following up on Siva’s suggestion, i think this would work for any size data range:

`=MOD(SMALL(ROW(A1:E10)*10^5+IF(A1:E10,COLUMN(A1:E10),10^5),(ROW(A1:E10)-MIN(ROW(A1:E10)))*COLUMNS(A1:E10)+1),10^5)`

or (rounded to nearest integer):

`=1/MOD(-SMALL(ROW(A1:E10)*2-A1:E10/COLUMN(A1:E10),(ROW(A1:E10)-MIN(ROW(A1:E10)))*COLUMNS(A1:E10)+1),2)`

Excel Hero’s applies to a range with up to 18 columns and the variation of Ola.S and others around 300 columns or so.

77. Peter B says:

Only two years behind!

`=MATCH(1,INDEX(rng,k,0),0)`

where the index k is given as a named formula

`=ROW(rng)-ROW(INDEX(rng,1,1))+1`

Strictly speaking, this result is not an array because of the use of INDEX. It can be array-output to a range but cannot be aggregated further in memory.