+ Reply to Thread
Results 1 to 8 of 8

Averaging rents based on two criteria

  1. #1
    Registered User
    Join Date
    08-20-2021
    Location
    Denver, CO
    MS-Off Ver
    Microsoft® Excel® for Microsoft 365 MSO (16.0.14228.20216) 64-bit
    Posts
    5

    Question Averaging rents based on two criteria

    Hi all,

    I have a specific use case I am trying to build a formula for. I am thinking it might include some combination of XLOOKUP and INDEX MATCH MATCH, both of which I've used before, but I this is beyond my pay grade and I am struggling to figure out a solution (it might include neither of those functions).

    Attached is a sample file.

    The formulas are to go in cells Q2:Q6 (highlighted green). My objective is as follows:

    Q3 would be the average of B3:L8 IF P3 matches the corresponding number in B2:L2 AND IF S2 matches the corresponding letter in column M. So if the "Subject" (S2) has a B "impr. rating", I am trying to have it calculate the average rent of 50 sq ft unit sizes (column B represents market rents for 50 sq ft units, and Q3 is adjacent to 50 sq ft in column P.

    You'll notice some of the columns don't have data, so I imagine an IFERROR would need to be incorporated into here somehow.

    As I'm typing it out I realize it probably sounds confusing. But hopefully viewing the file provides clarity.

    Any help would be appreciated!

    Moderator's note: Please take the time to review our rules. There aren't many, and they are all important. Rule #1 requires good titles. "Need Help with Specific Formula, Might Include XLOOKUP and/or INDEX MATCH MATCH" assumes the solution and does not describe your question. I have updated it for you this time because you are a new member. --6StringJazzer
    Attached Files Attached Files
    Last edited by 6StringJazzer; 08-20-2021 at 06:39 PM. Reason: Title

  2. #2
    Forum Moderator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    365
    Posts
    19,242

    Re: Averaging rents based on two criteria

    Use this formula in Q2 and copy down:

    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Attached Files Attached Files
    Jeff
    | | |·| |·| |·| |·| | |:| | |·| |·|
    Read the rules
    Use code tags to [code]enclose your code![/code]

  3. #3
    Forum Expert
    Join Date
    07-20-2011
    Location
    Mysore, India.
    MS-Off Ver
    Excel 2010
    Posts
    6,516

    Re: Averaging rents based on two criteria

    Try this
    In Q2
    Please Login or Register  to view this content.
    Attached Files Attached Files
    Pl note
    Array formula should be confirmed with Ctrl+Shift+Enter keys together.
    If answere is satisfactory press * to add reputation.

  4. #4
    Forum Moderator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    365
    Posts
    19,242

    Re: Averaging rents based on two criteria

    Quote Originally Posted by 6StringJazzer View Post
    Use this formula in Q2 and copy down:

    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Sorry, this is the sum, not an average. Looks like kvsrinivasamurthy has the complete solution.

  5. #5
    Forum Expert wk9128's Avatar
    Join Date
    08-15-2020
    Location
    Malaysia
    MS-Off Ver
    EXCEL 2007/365
    Posts
    1,324

    Re: Averaging rents based on two criteria

    Cell Q2 Array formula , Drag down

    HTML Code: 
    =IFERROR(AVERAGE(IF(($M$3:$M$8=$S$2)*($B$2:$L$2=P2)*($B$3:$L$8<>""),$B$3:$L$8)),"")

  6. #6
    Registered User
    Join Date
    08-20-2021
    Location
    Denver, CO
    MS-Off Ver
    Microsoft® Excel® for Microsoft 365 MSO (16.0.14228.20216) 64-bit
    Posts
    5

    Re: Averaging rents based on two criteria

    kvsrinivasamurthy provided exactly what I was looking for. Amazing, thank you!

  7. #7
    Forum Moderator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    365
    Posts
    19,242

    Re: Averaging rents based on two criteria

    If your question has been answered please mark your thread as "Solved" so that members will know by looking at the thread title that your problem is solved. Go to the menu immediately above your first post to the thread and click on Thread Tools. From the dropdown menu select "Mark this thread as solved..."

    If a member helped you solve your problem, consider adding to their reputation by clicking on the star icon addreputationiconsmall.jpg below their name.

  8. #8
    Valued Forum Contributor
    Join Date
    01-05-2013
    Location
    Singapore/China
    MS-Off Ver
    H&B 2016
    Posts
    1,150

    Re: Averaging rents based on two criteria

    wk9128's formula should also work. Like what wk9128 said, it's an array formula. Ctrl+Shift+Enter to enter the formula.

+ 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. Replies: 14
    Last Post: 09-28-2019, 10:32 PM
  2. [SOLVED] Help to include ISBLANK along with IFERROR in my INDEX MATCH formula...
    By TomP1988 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 05-03-2019, 05:10 PM
  3. [SOLVED] Edit the formula to make index&match include 3 criteria
    By leprince2007 in forum Excel Formulas & Functions
    Replies: 27
    Last Post: 12-12-2016, 02:41 PM
  4. Rank formula to include Index Match formula
    By MakkyD in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 05-24-2016, 11:28 AM
  5. Replies: 2
    Last Post: 01-22-2016, 06:30 AM
  6. Index match formula tweeking to include sumif
    By MZing81 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 06-28-2012, 01:14 PM
  7. Replies: 5
    Last Post: 02-29-2012, 08:51 PM

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