SOLVED

# Nesting IFERROR inside AVERAGEIFS

Brass Contributor

# Nesting IFERROR inside AVERAGEIFS

I made this formula:

=AVERAGEIFS(AM:AM,B:B,AH1)

The problem is that there are cells within column AM that have #VALUE because at this time, the data is incomplete.  This is a living document, so that will consistently be the case somewhere.  What I need is for this formula to work, but disregard any instances of #VALUE.  I think I need to put IFERROR inside of it; however, everything I've found online is about putting AVERAGEIFS inside IFERROR to give a binary result of correct or not correct data.  I'm after an average of those that don't have #VALUE but also fulfill the requirements in the formula of the date column matching the date cell.

2 Replies
best response confirmed by BlueMoose (Brass Contributor)
Solution

# Re: Nesting IFERROR inside AVERAGEIFS

``=SUM(IF((NOT(ISERROR(AM:AM)))*(B:B=AH1),AM:AM))/SUM((B:B=AH1)*(NOT(ISERROR(AM:AM))))``

You can try this formula. Enter the formula with ctrl+shift+enter if you don't work with Office 365 or Excel 2021.

# Re: Nesting IFERROR inside AVERAGEIFS

Perfect! Thanks so much.