Find MAX in range excluding some intermittent cells

ptkdave

New member
Joined
Mar 19, 2013
Messages
3
Reaction score
0
Points
0
I have a range of numbers from which I wish to total the 6 highest but within the range there are some that I wish to ignore.

Range
E3>AG3

Exclude
H3,L3,Q3,U3,Y3,AD3.

I realise that this is probably a simple formula but I am a golden oldie just trying to make simple spreadsheet for local club.
Thanks in advance for any help received.

Yours
Ptkdave
 

Bob Phillips

Super Moderator
Staff member
Joined
Mar 21, 2011
Messages
1,942
Reaction score
0
Points
36
Excel Version(s)
O365
You could try this array formula

=MAX(IF(ISNUMBER(MATCH(COLUMN(E3:AG3),{8,12,17,21,25,30},0)),0,E3:AG3))
 

ptkdave

New member
Joined
Mar 19, 2013
Messages
3
Reaction score
0
Points
0
You could try this array formula

=MAX(IF(ISNUMBER(MATCH(COLUMN(E3:AG3),{8,12,17,21,25,30},0)),0,E3:AG3))

Bob, thanks but does not do what I require, let me explain exactly my problem is, I have a formula which I used but I tried to change a couple of numbers within it and it has stopped calculating. My formula =SUM(LARGE(INDEX(D3:AG3*(ISERROR(FIND(COLUMN(D3:AG3),"7,11,16,20,24,29"))),0),{1,2,3,4,5,6}))
This totaled 6 largest numbers from cells D3>AG3 inclusive but ignored cells G3,K3,P3,T3,X3,AC3.
I hope that this explains what I am looking for and again thanks for your help.

Ptkdave
 

Bob Phillips

Super Moderator
Staff member
Joined
Mar 21, 2011
Messages
1,942
Reaction score
0
Points
36
Excel Version(s)
O365
Why didn't you mention that at the start?

=SUM(LARGE(INDEX(E3:AG3*(ISERROR(FIND(COLUMN(E3:AG3),"8,12,17,21,25,33"))),0),{1,2,3,4,5,6}))
 

Bob Phillips

Super Moderator
Staff member
Joined
Mar 21, 2011
Messages
1,942
Reaction score
0
Points
36
Excel Version(s)
O365
Actually, there is a bug in your formula, you need to be more precise like this

=SUM(LARGE(INDEX(E3:AG3*(ISERROR(FIND(","&COLUMN(E3:AG3)&",",",8,12,17,21,25,33,"))),0),{1,2,3,4,5,6}))

or use an amended version of wht I originally gave

=SUM(LARGE(IF(ISNUMBER(MATCH(COLUMN(E3:AG3),{8,12,17,21,25,33},0)),0,E3:AG3),{1,2,3,4,5,6}))
 

ptkdave

New member
Joined
Mar 19, 2013
Messages
3
Reaction score
0
Points
0
Bob
Thanks for that, sorry for any misunderstanding.

ptkdave
 
Top