Results 1 to 4 of 4

Index Formula using max and excluding specifc value

Threaded View

  1. #1
    Registered User
    Join Date
    02-06-2013
    Location
    High Point, NC
    MS-Off Ver
    Excel 2010
    Posts
    3

    Index Formula using max and excluding specifc value

    I need to find the MAX value in a range that excludes a specific value (100% in this case) and return the value of the row above it.


    1st row has the values of: Y, A, D, DV, I, E
    2nd row will have different % such as: 100%, 25%, 100%, 10%, no value, 50%

    What is need is to find the MAX value excluding any that say 100% and return the value to the row above it. So in this example the result should be "E" as 50% is the MAX number of all the values excluding the 100% (which there are 2 of them in this example) and the "E" was the value above the 50%.

    Below is the formula I started with but cannot figure out why is keeps returning the wrong value that I would like. I also tried it by using the CTRL+Shift+Enter, but it does the same thing. I might be making more difficult that it really needs to be.

    =INDEX(G7:L7,MATCH(MAX(IF(OR(G9<100,H9<100,I9<100,J9<100,K9<100,L9<100),G9:L9)),G9:L9,0))

    Any help would be great! Thanks...B
    Attached Images Attached Images

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1