Search code examples
excelif-statementaveragearray-formulas

Multi column if statement - Excel

I am trying to find a formula to check conditions in two different columns and then calculate the average of the corresponding values. The following example reflects what I would like to achieve. Let's say I want to find the average of North-western cities.

I already identified the average of the western cities with an array function like this:

=AVERAGE(IF($C$3:$C$10={"LA","SF","POR","SEA"},$D$3:$D$10))

Then hit Ctrl + Shift + Enter

Because of how my data is organized, this previous formula is the only way I will be able to determine (manually somewhat) whether a city is located on the east or the west coast.

Now I'm missing the part that would say "And also if the previous column says 'north' "

enter image description here

Solution

  • Update with Array Function

    First Attempt:

    So at first I had thought you could just add a second condition to the Array Function like this:

    =AVERAGE(IF(AND($C$2:$C$9={"LA","SF","POR","SEA"},$B$2:$B$9={"North"}),$D$2:$D$9))

    Where the AND would require each item it be true, but this appears not to have worked... and array functions are tricky beasts to debug.

    Second Attempt:

    Instead, what you can do is extend the IF function with a second IF. So in the event that the first one is true, the value should return another IF that checks a second criteria. Only if it meets both criteria, return the score, otherwise it will still return nothing , which won't get picked up by the average function.

    So the final thing should look like this:

    =AVERAGE(IF($C$2:$C$9={"LA","SF","POR","SEA"},IF($B$2:$B$9={"North"},$D$2:$D$9)))

    Named Array

    Since you are unable to create a column to store the data related to which cities live in the west, it would be best if you had some way to easily update this information instead of hardcoding it throughout your functions. One thing you can do to is define a name to reference later.

    Just go to Formulas Ribbon > Defined Names Section > Name Manager > New

    Then create the array of western city values as a named parameter to be used later

    Named Value

    Then you can use it in a formula in place of the array like this:

    =AVERAGE(IF(C2:C9=WestCities,IF(B2:B9="North",D2:D9)))

    Named Usage

    With PivotTable

    Here's my suggestion:

    First, add a column for East/West, so you're not stuffing data into a formula

    Data

    Then add a pivot table and it will do all you calculations for you.

    Set it up like this (make sure to aggregate by Average (not sum):

    Pivot Setup

    And it will look like this:

    Pivot

    If you want to keep your original formatting, you can just run the formulas off of your pivot table like this:

    =GETPIVOTDATA("Score",Sheet4!$A$3,"E/W","West","S/N","North")

    Pivot Formula

    With West Column

    Also, if you had the ability to add in a column for west, you could easily check this with AVERAGEIFS with multiple criteria like this:

    =AVERAGEIFS(D2:D9,A2:A9,"West",B2:B9,"North")

    AverageIfs