Forum Discussion
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.
Peut-être:
Dim s As String Dim reto As Range Set reto = Selection Do s = InputBox("Scannez le document, puis cliquez sur 'OK'; en fin de série, cliquez sur OK! ", " Enregistrement du retour ") If s = "" Then Exit Do Application.Goto Reference:="codbarretour" Do While ActiveCell > 0 ActiveCell.Offset(1, 0).Activate Loop reto.Select reto.Formula = s ActiveCell.Offset(0, 10).Activate Range(ActiveCell, Range("chrono")).Select Selection.Copy Selection.PasteSpecial Paste:=xlPasteValues ActiveCell.Offset(0, -12).Activate Loop
2 Replies
- OliverScheurichGold Contributor
=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.
- BlueMooseBrass ContributorPerfect! Thanks so much.