+ Reply to Thread
Results 1 to 3 of 3

Multiple Criteria If Condition - Identify highest number based upon preceding criteria

  1. #1
    Registered User
    Join Date
    05-27-2015
    Location
    Sydney, Aus
    MS-Off Ver
    07
    Posts
    1

    Multiple Criteria If Condition - Identify highest number based upon preceding criteria

    Hi All,

    I am trying to build a bit of a tracker within Excel/Google Sheets to identify which 'home loan' has the combination of highest interest rate PLUS loan/mortgage size.

    The formula I've built so far works for the below conditions;
    1. There is only 1 loan with the highest interest rate, or;
    2. If there are multiple loans that have the highest interest rate, the loan that is the largest is included within that condition.

    Where this formula doesn't work is when;
    • When there are multiple loans with the highest interest rate, however these loans are NOT the largest loan size... I believe the issue is due to the fact I'm including a 'max' statement as part of the match condition. Please see cell reference I8:L10 as an example.

    I'm unsure how to achieve this within a formula to identify;
    1. The loan with the highest interest rate
    2. Of the loans with the highest interest rate, which has the largest mortgage/loan size.
    Please note the Interest Rate's vary within the data set

    Formula Used: =if(countif($E3:$H3,max($E3:$H3))>1, if(iserror(index($E$1:$H$1,match(1,(max($E3:$H3)=E3)*(max($A3:$D3)=A3),0))=I$1),"Inactive","Active"), if(index($E$1:$H$1,match(max($E3:$H3),$E3:$H3,0))=I$1,"Active","Inactive"))


    Thanks in advance for any assistance!
    Attached Files Attached Files

  2. #2
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... based in Ireland
    MS-Off Ver
    µSoft Office 365. Learning Excel all over again!!
    Posts
    30,754

    Re: Multiple Criteria If Condition - Identify highest number based upon preceding criteria

    What version of Excel are you currently using? Since you joined 5 years ago, I have assumed that you are now using Excel 2010 or later. If not, I can tweak this for Excel 2007.

    =IF(J$1=INDEX($1:$1,AGGREGATE(15,6,COLUMN($E$1:$H$1)/($A3:$D3=LARGE(($E3:$H3=MAX($E3:$H3))*($A3:$D3),1)),1)),"Active","Inactive")

    in J3, copied across and down. If this is incorrect, please tell us what the expected answers are (a description of your non-working formula is of very limited use).
    Attached Files Attached Files
    Glenn



  3. #3
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... based in Ireland
    MS-Off Ver
    µSoft Office 365. Learning Excel all over again!!
    Posts
    30,754

    Re: Multiple Criteria If Condition - Identify highest number based upon preceding criteria

    Excel 2007-friendly version:

    =IF(J$1=INDEX($1:$1,,SMALL(IF($A3:$D3=LARGE(($E3:$H3=MAX($E3:$H3))*($A3:$D3),1),COLUMN($E$1:$H$1)),1)),"Active","Inactive")

    This is an array formula.

    These are a little different from ordinary formulae in that they MUST be confirmed in the FIRST CELL ONLY by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. After that, the array can be dragged down as normal, to cover the desired range.

    You will know the array is active when you see the curly brackets { } appear around the outside of your formula. If you do not use CTRL+SHIFT+ENTER you will (almost always) get an error message or an incorrect answer. Press F2 on that cell and try again.

    Don't type the curly brackets yourself - it won't work...

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Identify rows based on multiple criteria
    By bilbo85 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 05-07-2020, 07:58 PM
  2. [SOLVED] Highest sum in a column based on multiple criteria
    By alisachsf in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 11-05-2019, 08:44 PM
  3. [SOLVED] Condition Formatting: How to highlight the most highest/lower value based on criteria
    By Shermaine2010 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 07-01-2018, 07:15 PM
  4. [SOLVED] find highest number based on criteria and flag by X
    By Villalobos in forum Excel General
    Replies: 6
    Last Post: 06-14-2017, 02:55 PM
  5. Replies: 3
    Last Post: 07-03-2012, 09:09 PM
  6. VBA code to find highest and 2nd highest number based in criteria
    By Michael007 in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 10-25-2011, 08:38 AM
  7. Identify/highlight maximum value based on multiple criteria
    By JennB in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 12-06-2009, 06:28 PM

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