AverageIF Issue

[canthony24]

I have this averageif formula, =AVERAGE(AVERAGEIF(F4:F9,{"TRS","TRP","TRX"},G4:G9))

This works well when I have all of the criteria in the dataset, but if the dataset only has "TRS", the averageif returns and error because I think it's trying to locate all of the words inside the formula.

How do I not get the error if the dataset only has, let's say "TRS"?

 

[NiMip]

Try putting an IFERROR in there, like this: =AVERAGE(IFERROR(AVERAGEIF(F4:F9,{"TRS","TRP","TRX"},G4:G9),FALSE))
Will have to use CONTROL+SHIFT+ENTER rather than just enter, as this is an array formula.

 

[barry houdini]

=AVERAGE(AVERAGEIF(F4:F9,{"TRS","TRP","TRX"},G4:G9))

Is it possible that TRS can appear twice but TRX and TRP only once for example? In that case you wouldn't get a true average of all 4 of those rows because you would be averaging an average.

This array formula will fix your problem and also that issue

=AVERAGE(IF(F4:F9={"TRS","TRP","TRX"},G4:G9))

confirm with CTRL+SHIFT+ENTER

For a non array version try this

=SUM(SUMIF(F4:F9,{"TRS","TRP","TRX"},G4:G9))/SUM(COUNTIF(F4:F9,{"TRS","TRP","TRX"}))