Forum Discussion
IF Function
Here's an alternative way to do it.
I've written only two formulas in this sheet. (no, sorry, three: one to count the number of people in the Big Event). Here's that formula: =COUNTA(Table1[Name])
Then I turned the list of names and times into a table and used a single formula to rank ordered the participants based on time. Here's that formula: =SORTBY(Table1[Name],Table1[Total],1)
And then gave the point value, referring to outside named ranges in this formula:
=SEQUENCE(PeopleCount,,HighestValue,StepValue)
The nice thing about these Array Functions is that they spill into the needed rows. You don't need to copy them down.
Here's what it looks like. I've attached the spreadsheet.
- Good. Glad you got a resolution even if it didn't include the Array Functions. Those are both new and exciting in their applicability to provide really new ways to accomplish things. If you're curious about them at all, I encourage you to watch this video on YouTube. It was my introduction to these amazing functions about 10 days ago.
https://www.youtube.com/watch?v=9I9DtFOVPIg Thanks to both Zack and Mathetes for their suggestions. I ended up using the vlookup function and a table I created with the required values.
- Nice job.
You could also make the step value into a formula, thus only having to input the ceiling/highest value.
=-ROUNDDOWN(HighValue/TotalCount,0)
