You can actually use the index of the same array as the points, but just start it at row 1 always. This lets you drag the formula to other participant scores.
Dear Alan, The question can also be resolved, using the following formulas, for those who have MS365 and for those who do not: =INDEX(A2:A26;SMALL(IF(B2:B26=0;SEQUENCE(ROWS(B2:B26)));3)) or =INDEX(A2:A26;SMALL(IF(B2:B26=0;ROW(INDIRETO("1:"&LROWS(B2:B26))));3)) 🤗
@@Computergaga I'd like to buy one of your books on learning Excel formulas. I'm a complete beginner and want to learn how to develop my logical thinking when constructing formulas. Which book do you recommend?
@@qasimawan3569 Definitely my 'Advanced Excel Formulas' book amzn.to/3Rg87Go Do not let the term 'Advanced' mislead you. Yes, there are advanced formula examples but that term is more about the depth that I got to understand how the formulas work. Chapter 1 is Gold as an explanation of writing formulas and sets the foundation for each chapter to then progress from. I really hope that you find it useful.
Hi Allan, array functions are so interesting: how about this one: =MAX(TAKE(UNIQUE(IF(B1:B13="N";ROW(B1:B13);"");;TRUE);3))
They sure are. Very interesting Bart 👍
It works. I had to press CTRL SHIFT ENTER. That's amazing. Its going to save me a lot of work. How can I donate?
You're welcome. Glad it worked.
Feel free to get a copy of my 'Advanced Excel Formulas' book if you want to donate - amzn.to/3Rg87Go
Valulable
You can actually use the index of the same array as the points, but just start it at row 1 always. This lets you drag the formula to other participant scores.
Thank u Alan for this nice video 📹 👍
You're welcome, Nader. Thank you 🙂
Dear Alan,
The question can also be resolved, using the following formulas, for those who have MS365 and for those who do not:
=INDEX(A2:A26;SMALL(IF(B2:B26=0;SEQUENCE(ROWS(B2:B26)));3)) or
=INDEX(A2:A26;SMALL(IF(B2:B26=0;ROW(INDIRETO("1:"&LROWS(B2:B26))));3)) 🤗
Nice work, José 💪
Excellent Alan. Than you!!!
Thank you 😊
Thanks Alan, fantastic 👌
Thank you 😊
Love your style of teaching! Thanks Alan
Thank you 😊
@@Computergaga I'd like to buy one of your books on learning Excel formulas.
I'm a complete beginner and want to learn how to develop my logical thinking when constructing formulas. Which book do you recommend?
@@qasimawan3569 Definitely my 'Advanced Excel Formulas' book amzn.to/3Rg87Go
Do not let the term 'Advanced' mislead you. Yes, there are advanced formula examples but that term is more about the depth that I got to understand how the formulas work.
Chapter 1 is Gold as an explanation of writing formulas and sets the foundation for each chapter to then progress from.
I really hope that you find it useful.
@@Computergaga This is great, thanks for replying Alan. I'll pick it up on my next order at Amazon
Valuable
Thank you
Perfect 👍
Thanks 👍
Excellent breakdown of the problem and steps to a neat and flexible solution
Thank you Paul 😊