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.
ReplyDeleteVery helpful explanation! The way you broke down photosweek AVERAGEIF and AVERAGEIFS makes it so easy to understand. These formulas really save time when analyzing data in Excel. I appreciate the clear examples and step-by-step guide. Great post for anyone improving their Excel skills
ReplyDelete