Forum Discussion
Excel formula for finding, ranking and returning nth value
Thank you for this.
How can I extrapolate this formula? I get the alert that I cannot edit an array formula.
I need to be able to copy it across for multiple schools
e.g. School: 13, 18, 41 etc
each time searching the same table for the 1st to 4th runners for that respective school number.
rjtomkinson So you are using the formula surrounded by the curly brackets in I2? That's an old style array formula that needs to be confirmed with Ctrl-Shift-Enter (CSE), i.e. not just Enter. As soon as you touch it you get that error message. You need to select all four cells, enter the formula and then CSE. Then you can select those four cells again and drag them across. It's actually quite annoying to work with such CSE type formulas. By the way, you need to make the references to columns A and B absolute for copy by dragging to work correctly. Done that in the attached file.
I've also include a traditional solution that avoids CSE. I would use that one if you really can't get your hands on a modern Excel version.
Have you tried Excel App on Android smart phone?
@Riny_van_Eekelen's file works fine on my side.
Re:
when using your formula I either get a #ref or #name error
Can you share the workbook with error formular?
enter the error formular like '=myErroFumular(range)
Additionly,you need list 4 runners in each sheets rather than put them(all 4 runners) in one sheet?
rjtomkinson Sorry to hear. I know nothing about GS, so I can't help you any further.
Thank you for your efforts.
Without understanding the language of the functions and formulas I think I am destined to fail - when using your formula I either get a #ref or #name error.
Additionally, I want to transfer this to Google Sheets which my school uses, but it doesn't recognise the aggregate function.
Unfortunately I am no closer to a solution.
Thanks again