In the earlier posts we have looked at using SUMIF, SUMIFS, COUTIF AND COUNTIFS. In this post lets see how to use AVERAGEIF and AVERAGEIFS,
Lets look at the following example:
Let say I need to calculate the average quantity produced for each day.
First make a list of dates (unique).
=AVERAGEIF(E:E,A3,G:G)
Extend the formula as needed.
Let say i need the average by machine (1 to 9) and date then i need to use AVERAGEIFS.
The results will be,
The formula is
=AVERAGEIFS($U:$U,$S:$S,$A3,$T:$T,B$2)
Please note the quantity is in column U, dates in column S and machine number in column T.
I have added IFERROR function to get rid of #DIV/0! error
=IFERROR(AVERAGEIFS($U:$U,$S:$S,$A3,$T:$T,B$2),"")
Please leave your comments, thanks.
ExpiresDefault access plus 1 year
Use AVERAGEIF(range, criteria, Site Country to calculate the average of cells that meet a single criterion, and AVERAGEIFS(average_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...) for multiple criteria.
ReplyDelete