Menu
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!
317
The AVERAGEIFS function is a premade function in Excel, which calculates the average of a range based on one or more true or false condition. It is typed =AVERAGEIFS : =AVERAGEIFS(average_range, criteria_range1, criteria1, ...)
Excel AVERAGEIF function. The AVERAGEIF function in Excel calculates the average (arithmetic mean) of all the cells that meet a specified criteria. The AVERAGEIFS function has the following arguments, the first 2 are required, the last one is optional: Range - the range of cells to be tested against the given criteria.
The Excel AVERAGEIF function calculates the average of numbers in a range that meet supplied criteria. AVERAGEIF criteria can include logical operators (>,<,<>,=) and wildcards (*,?) for partial matching. Get the average of numbers that meet criteria.
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"))
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
