Top 5 values if they meet a criteria
Hello
I have data in row 7 that are numbers ranging between 0 and 50. I am trying to add the top 5 numbers that fall between 0 and 20 and the top 8 numbers that fall between 20 and 50.
I would rather excel list the top 5 numbers that are between 0 and 20 and the top 8 numbers that are between 20 and 50. But I'm ok if it just adds them as well. Please let me know what formula to use.
Thanks,
Diana
Hi,
I'm not sure if I understood what you want exactly.
An attached file would have been more useful.
for top 5 use that (array formula, press Ctrl+Shift+Enter when inserted) and drag down 5 cells
Formula:
Please Login or Register to view this content.
for top 8, and drag down 8 cells
Formula:Please Login or Register to view this content.
see attached
Hi and welcome to the forum.
Assuming the numbers are in A7:T7 then in say A10 enter the array formula
Formula:Please Login or Register to view this content.
Note this is an array formula and should be entered with Ctrl-Shift-Enter. When it's entered you'll see the { } parentheses surrounding it.
Now copy A10 to A11:A14
Do something similar in say B10 but change the <=20 to >20
If you put the formula on a row other than row 10 adjust the ROW()-9 bit so that the row number less a constant is 1. e.g if on row 13 use =ROW()-12
Richard Buttrey
RIP - d. 06/10/2022
If any of the responses have helped then please consider rating them by clicking the small star iconbelow the post.
wow you guys are the greatest. Thanks so much!
My pleasure and thanks for the rep.
Note however that Tudy's use of the ROWS($1:1) is to be preferred to my ROW()-9 since there's then no need to consider which row contains the formula and adjust with a constant.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks