Max number if not equal to the cell references in two other cells

[tamsh]

Hi, I would like to return the max number from a list, if that number has already not been selected in two other cells. Thus far I have used max to find the highest number in one array, putting result into a cell. Then a second max to find the highest number in a second array, putting result into a cell. Then used CELL to display the cell reference of each maximum number in two other cells.

What I now need is to find the highest number in the entire array (both arrays) that has not already been identified in the other two max formulas.

Hope that makes sense...

Can this be done?

 

[Fluff]

What version of Excel are you using>
I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Also can you post some sample data along with expected results?
MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

 

[tamsh]

Office365, windows.

Cell AR6.

MAX.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH	AI	AJ	AK	AL	AM	AN	AO	AP	AQ	AR	AS	AT	AU	AV	AW	AX	AY	AZ	BA
1	3: NEA - Practical performance in physical activity and sport
2	Team Sports																								Individual Sports															Practical Summary						Analysis & Evaluation			3: Scaled Average NEA Marks / 100	3: Scaled Average NEA Percentage	3: Scaled NEA Marks / 104	3: Scaled NEA Marks Percentage	3: Current Unit Grade
3	Association football			Basketball			Cricket			Handball			Netball			Rugby Union			Athletics			Badminton			Rock-climbing			Badminton			Boxing			Table Tennis			Trampolining			Best Team Sport / 25		Best individual Sport / 25		Next Best Sport / 25	Best Sports Sub-total / 75	Analysis / 15	Evaluation / 10	Analysis & Evaluation Total / 25
4	Skills / 10	Full Context / 15	Association football sub-total / 25	Skills / 10	Full Context / 15	Basketball sub-total / 25	Skills / 10	Full Context / 15	Cricket sub-total / 25	Skills / 10	Full Context / 15	Handball sub-total / 25	Skills / 10	Full Context / 15	Netball sub-total / 25	Skills / 10	Full Context / 15	Rugby Union sub-total / 25	Skills / 10	Full Context / 15	Athletics sub-total / 25	Skills / 10	Full Context / 15	Badminton sub-total / 25	Skills / 10	Full Context / 15	Rock-climbing sub-total / 25	Skills / 10	Full Context / 15	Badminton sub-total / 25	Skills / 10	Full Context / 15	Boxing sub-total / 25	Skills / 10	Full Context / 15	Table Tennis sub-total / 25	Skills / 10	Full Context / 15	Trampolining sub-total / 25		Best Team Sport Cell Ref		Best individual Sport Cell Ref
5
6			0			0			0			0			0			0			0			0			0			0			0			0			0	0	$C$6	0	$AA$6	#VALUE!	#VALUE!			0	#REF!	#REF!	#REF!	#REF!	#REF!
Sheet1

Cell Formulas
Range	Formula
I4,AM4,AJ4,AG4,AD4,AA4,X4,U4,R4,O4,L4	I4	=G3&" sub-total / 25"
C6,AV6,AM6,AJ6,AG6,AD6,AA6,X6,U6,R6,O6,L6,I6,F6	C6	=A6+B6
AN6	AN6	=MAX(A6:X6)
AO6	AO6	=CELL("address",INDEX(A6:X6,MATCH(MAX(A6:X6),A6:X6,0)))
AP6	AP6	=MAX(Y6:AM6)
AQ6	AQ6	=CELL("address",INDEX(Y6:AM6,MATCH(MAX(Y6:AM6),Y6:AM6,0)))
AR6	AR6	=MAX(IFS(A6:AM6<>CELL("address",INDEX(A6:X6,MATCH(MAX(A6:X6),A6:X6,0))),A6:AM6,A6:AM6<>CELL("address",INDEX(Y6:AM6,MATCH(MAX(Y6:AM6),Y6:AM6,0))),A6:X6))
AS6	AS6	=SUM(AN6:AR6)
AW6	AW6	=#REF!+AV6
AX6	AX6	=AW6/100
AY6	AY6	=VLOOKUP(AW6,[GCSE_PE_Tracker.xlsx]Grade_Boundaries!$J$16:$K$115,2)
AZ6	AZ6	=AY6/104
BA6	BA6	=VLOOKUP(AY6,[GCSE_PE_Tracker.xlsx]Grade_Boundaries!$M$3:$N$13,2)

 

[Fluff]

Thanks for that.
How about

=MAX(HSTACK(DROP(SORT(A6:X6,,-1,1),,1),DROP(SORT(Y6:AM6,,-1,1),,1)))

 

[tamsh]

Sorry being dim, can you explain? I am getting function isnt valid

 

[Fluff]

Do you have the HSTACK or SORT functions?

 

[tamsh]

Do you have the HSTACK or SORT functions?

No, not showing

 

[Fluff]

If you don't have either of them, then either you are not using 365 or your version is about 3 years out of date & needs updating.

 

[tamsh]

If you don't have either of them, then either you are not using 365 or your version is about 3 years out of date & needs updating.

It is office365 but a work acocunt which I am unable to update. Never mind and thank you for your time.

 

[Fluff]

In that case I would suggest changing your profile to show 2019 as that is what you are effectively using.
Give me a moment to come up with another solution.