I am using the averageifs function, and have one column where I need to calculate the average if either of the criteria are true.
I have tried using the OR function, and tried the curly brackets, but both give me errors.
=AVERAGEIFS(N1:N612,I1:I612,{"FL","IF"})
There are more ranges, but the coding for those is fine.
To be clear, I want to return an average if column "I" contains (specifically) the letters FL, OR the letters IF. Any other letters should mean that entry is not averaged.
TIA!
AVERAGEIFS extra criteria is a boolean AND operator, it can't perform OR operations. But since your columns do not change we can somewhat implement this ourselves:
Here is a simple way using an array formula (entered with ctrl + shift + enter):
=AVERAGE(IF((I1:I612="IF")+(I1:I612="FL"),N1:N612))
Or if you don't like using array formulas you basically do this manually using SUMPRODUCT and COUNTIF:
=SUMPRODUCT((I1:I612="IF")*(N1:N612)+(I1:I612="FL")*(N1:N612))/(COUNTIF(I1:I612,"IF")+COUNTIF(I1:I612,"FL"))
Chancea has given some good techniques I will list one more that avoids array formulas as many will find it easier to read avoiding the sumproduct.
=(SUMIF(I1:I612,"FL",N1:N612)+SUMIF(I1:I612,"IF",N1:N612))/(COUNTIF(I1:I612,"FL")+COUNTIF(I1:I612,"IF"))
Do not think Excel has this feature, but in Google Sheets, I've been using the below to achieve similar results
=average(filter($A:$A,($C:$C=$E$6)+($C:$C=$E$7)))
giving me an average of the values in A:A where C:C matches the value in either E6 or E7
unlike an ifs / sumifs etc., the filter uses =, not a comma, thus $C:$C=$E$6 is used, not $C:$C,$E$6
Mathematical symbols are just entered ( so <> rather than "<>"& )
Another way to solve this would be to create an additional column (let's call it column J) that returns TRUE if column I contains either "FL" or "IF":
J1 = IF(OR(I1="FL",I1="IF"),TRUE,FALSE)
Then you can reference that column in your AVERAGEIFS formula instead:
=AVERAGEIFS(N1:N612,J1:JI612,TRUE)
A solution to your question is:
=AVERAGE(Maxifs(N1:N612,I1:I612,{"FL";"IF"}))
