Results 1 to 6 of 6

Thread: Find MAX in range excluding some intermittent cells

  1. #1

    Find MAX in range excluding some intermittent cells



    Register for a FREE account, and/
    or Log in to avoid these ads!

    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

  2. #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. #3
    Quote Originally Posted by Bob Phillips View Post
    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. #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. #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. #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
  •