# Thread: Find MAX in range excluding some intermittent cells

1. ## Find MAX in range excluding some intermittent cells

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

I realise that this is probably a simple formula but I am a golden oldie just trying to make simple spreadsheet for local club.

Yours
Ptkdave

2. You could try this array formula

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

3. Originally Posted by Bob Phillips
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

4. 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}))

5. 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}))

6. Bob
Thanks for that, sorry for any misunderstanding.

ptkdave

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•