Small/Large () without repeating the same value in each list

ExcelBasicBro

New Member
Joined
Feb 28, 2021
Messages
2
Office Version
  1. 365
  2. 2013
Platform
  1. Windows
I am creating two lists. List one is the Top 5 and the second list is the bottom 5. I am pulling the data from the same data set. I get can return both the Large() and the Small() but if the data has less than 10 items to start, it ends up repeating of the values on the both lists. I'd like to avoid that if possible. Any ideas on how to say if a value shows up on the Top 5, do not put it on the bottom 5?
 

Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
14,559
Office Version
  1. 365
Platform
  1. Windows
Using the dynamic array functions in office 365,
Book1 (version 1).xlsb
ABCDE
1Original Top 5Bottom 5
2191
3282
4373
5464
655
76
87
98
109
11
Sheet3
Cell Formulas
RangeFormula
C2:C6C2=IFERROR(LARGE(A2:A21,SEQUENCE(5)),"")
E2:E5E2=IFERROR(SMALL(A2:A21,SEQUENCE(MIN(5,COUNT(A2:A21)-5),,1)),"")
Dynamic array formulas.
 
Solution

Forum statistics

Threads
1,148,159
Messages
5,745,116
Members
423,925
Latest member
globaltlg

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top