I need some help with a formula please

Westie102

New Member
Joined
Feb 3, 2019
Messages
18
Hi everyone,

I have created this formula:

=IF(F1="All Boroughs",(IFERROR(INDEX(SORT(Matrix,26,-1),SEQUENCE(E1+1),{4,5,6,7,26,32}),"")),(IFERROR(INDEX(SORT(FILTER(MatrixScores!$A$1:$AB$400,MatrixScores!$E$1:$E$400=F1),26,-1),SEQUENCE(E1),{4,5,6,7,26,32}),"")))

Which does what I need it to do, but for one minor floor...
When I select a borough from a list displayed in cell 'F1' the results are brought back as expected under the respective 'headings' {4,5,6,7,26,32} - perfect!
However when I select "All Boroughs" it displays results as expected, but the first row repeats the headings, and the following rows provide a list of the top '10' boroughs from a scoring matrix. How can I stop this from happening? I just can't work it out!

Short background:
I have created a matrix to rate locations within boroughs against certain criteria, add those up to create a total score and then want to display the top n locations with the highest score (All Boroughs), or select a specific borough only and see the top n locations within that specified borough.

I'd be grateful for any advice you can give. I can, of course, provide more info if required, but I didn't want to put anyone to sleep with too much information if you don't need it, I'm guessing if you know what you're doing this is probably quite simple, even though I have spent several hours on it already - hence coming here for help!

Many thanks
Nicky

PS - I'm using Excel on 365 or the desktop version. But I'd really like this to work in 365 as it ideally needs to be a shared file.
 
Last edited:

Some videos you may like

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest

mamady

Board Regular
Joined
Sep 23, 2011
Messages
161
Office Version
  1. 365
Platform
  1. Windows
Hi Nicky,

It would be easier if you shared a sample sheet. Nonetheless, have you tried to change "Matrix" to the specific range without selecting the headings row?

Best Regards
M. Yusuf
 

Westie102

New Member
Joined
Feb 3, 2019
Messages
18
Hi Nicky,

It would be easier if you shared a sample sheet. Nonetheless, have you tried to change "Matrix" to the specific range without selecting the headings row?

Best Regards
M. Yusuf
Noted on the sample sheet for next time, but thank-you - that was just the perfect hint! Resolved :)
Nicky
 

mamady

Board Regular
Joined
Sep 23, 2011
Messages
161
Office Version
  1. 365
Platform
  1. Windows
Most welcome Nicky, I am glad it is resolved 🌷
 

Watch MrExcel Video

Forum statistics

Threads
1,114,520
Messages
5,548,528
Members
410,845
Latest member
OldSwimmer1650
Top