Formula Help

[Papps]

Hi, 

I was wondering if there is a formula that I can use to help me with the following.. 

I have a group of data on left which has been sorted high to low in the next column, I am then wanting to create a third column which excludes the 4 highlighted numbers.

Any suggestions?

James 

[#2]

you could add a filter and then manually deselect those 4 values

[#3]

@PascalKTeam I am trying to make it automated as manual changes will be come time consuming as this is part of a wider more complex sheet, but thank you for you comment. 

James 

[#4]

If you want it automated you need to define the logic. Is it always 65, 64, 63 and 58 that should be removed? or always the top 4 values?
Would a macro solutions be ok for you?

[#5]

Is there any way (without violating confidentiality) to describe that "larger task" or the context in which this is a part, as well as defining the logic behind your primary request. Like (for example) I presume you're deleting whole rows of information that contain the numbers in question; is there a way to accomplish that earlier in the process? Or does the condition come into existence during the immediately prior step? Could the "offending rows" simply be excluded later on during the creation of a final report (via Pivot Table or Power Query)? Those are the kinds of questions to address, if possible.

You've presumably done the sorting "manually"--i.e., using Data.....Sort...; if so, why isn't the suggestion to "Sift" or "Slice" and delete equally acceptable?

[#6]

@mathetes & @PascalKTeam  Below is the full sheet, it is only a grade calculator to see what I will achieve at the end of my final year at uni. I want to make it automated so I can change the figures in the blue highlighted cells to see how it would affect my grade. It isn't very complex but all previous formulas are correct and working well. The part I am struggling with is the column 'Y2&3 Ranked, excluding top from Y3', my grade is made up of my top 4 marks from Y3+ the best 7 marks from Y2 and the remainders from Y3.

Is this more clear? 

Thank you, 

James 

[#7]

@Papps 

More clear, yes. But the actual spreadsheet would be clearer still. It would enable us to see what the existing formulas are.

My suspicion is that you'll be wanting to use Power Query to complete your "What-if?" analysis, and I'm not competent with that set of tools yet. They (those Power Querytools) came along after I retired. ;-(

An interesting challenge, though. I just created a "grading spreadsheet" for a course my wife was co-teaching, one into which she and her colleague could enter letter grades (what the institution used) and my sheet (behind the scenes) would calculate a final grade based on weightings for different segments, converting letters into numbers, doing the weighted calculations, and then converting the result back into a final letter grade. It was fun....not quite what you're doing, but related.

So I'll be interested in seeing what others recommend.  But I'm pretty sure you're going to be asked to post the actual sheet. Are you acquainted with Excel's Power Query tool?

[#8]

@Papps 

A good opportunity to use FILTER (If you have it).

https://support.office.com/en-us/article/FILTER-function-f4f7cb66-82eb-4767-8f7c-4877ad80c759

[#9]

@mathetes 

I have attached the file, 

Only need help with top data set and I am not acquainted with Excel's Power Query tool.

Regards, 

James

[#10 · Accepted solution]

@Papps 

Straightforward formula

in O3

=IFERROR(LARGE(($L$3:$L$10,$M$7:$M$10),ROW()-ROW($O$3)),"")

and drag it down.

[#11]

@Papps 

If you have O365:

=SORT(K3:K10,1,-1)

[#12]

@Papps 

I went out to lunch, fully expecting one of the true experts here would give you a straight forward solution. I'm happy to see that @Sergei Baklan  did not let either of us down.

[#13]

Thank you, much appreciated :)

[#14]

@Papps , you are welcome