+ Reply to Thread
Results 1 to 2 of 2

AVERAGEIF Array for multiple variables

  1. #1
    Registered User
    Join Date
    07-29-2020
    Location
    Bristol, England
    MS-Off Ver
    Office 365
    Posts
    1

    AVERAGEIF Array for multiple variables

    I'm in the process of writing a formula to calculate the average cost from a dataset, based on a number of variables; Distance From a Point, Room Capacity, Whether the location is a Hotel, and whether the cost is based on Delegate Rates.

    The variables are all set on a separate page, with manual input of the Distance and Capacity figures, and data validation for a “Yes/No” response for the Hotel and Delegate rate variables.

    So far, I’ve been able to use an AVERAGE(IF array function to narrow down the dataset based on distance from a point, and room capacity.

    I’m struggling to write the code that would also narrow down the data for the last two variables. Essentially, I want the formula to review each variable separately, and do the following:

    If “Include Delegate Rates” is “No”, only choose rates with “No” in the “Delegate Rate” column.
    If “Include Delegate Rates” is “Yes”, include all rates and disregard the “Delegate Rate” column


    If “Include Hotels” is “No”, only choose rates with “No” in the “Is Hotel?” column.
    If “Include Hotels” is “Yes”, include all rates and disregard the “Is Hotel?” column

    If anyone could help me with the formula to do this, that would be greatly appreciated.
    Attached Files Attached Files

  2. #2
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    12,992

    Re: AVERAGEIF Array for multiple variables

    You should be able to use AVERAGEIFS with your version of Excel. Try
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Does that give you the results you expect? If not, please give a few examples of what you are expecting if calculated manually.
    ChemistB
    My 2¢

    substitute commas with semi-colons if your region settings requires
    Don't forget to mark threads as "Solved" (Edit First post>Advanced>Change Prefix)
    If I helped, Don't forget to add to my reputation (click on the little star at bottom of this post)

    Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble

+ 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. [SOLVED] How to equate array variables to other array variables in a formula
    By Geode7 in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 05-13-2019, 04:45 AM
  2. Storing variables as string or as variables in array performance
    By jakopak in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 02-20-2017, 08:35 AM
  3. Multiple variables in array with formula
    By k_paresh in forum Excel General
    Replies: 1
    Last Post: 09-27-2016, 03:51 PM
  4. [SOLVED] AverageIF in an Array
    By Jay_hl in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 06-17-2016, 03:48 PM
  5. how to use worksheetfunction.averageif, with multiple variables
    By dka90 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 09-16-2015, 07:51 AM
  6. [SOLVED] finding an output from 5 variables in an array using exterior input user variables
    By Allsort in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 08-09-2013, 11:16 AM
  7. AVERAGEIF with array parameter?
    By CDirenzi in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 07-18-2013, 10:21 AM

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