AVERAGEIF function help?

Kate Allison

I'm having trouble with the AVERAGEIF function. I have a project intake sheet, and I'd liek to take the average of a number among all projects in the sheet.

I'm attempting to use =AVERAGEIF([Duration of Hold]6:[Duration of Hold]100, >0)), but I can't figure out how to protray the critera correctly to make the formula work.

I want it to only take the average of cells in Duration of Hold, but only the ones that have a number. The column Duration of Hold is calculated based on Start and End date columns. So some rows have a number, but in others the data is still incomplete so some cells say "#INVALID DATA TYPE" because the project hasn't progressed yet. My AVERAGEIF formula is coming back as "UNPARSABLE" currently.

Is there a way to exclude the INVALID DATA TYPE rows in my AVERAGEIF formula? See screenshots below for reference. Thank you!

Mark.poole

@Kate Allison

Ok lets try adding an IFERROR to your formula in the Duration of hold to return a blank instead of an error.

=IFERROR(your formula),"")

THEN you can do

=AVG(COLLECT([Duration of Hold]:[Duration of Hold],[Hold Start Date]:[Hold Start Date], @cell <> "",[Hold End Date]:[Hold End Date],@cell <> ""))

bisaacs

Hey @Kate Allison,

If you wrap your formula in an IFERROR function, that might remove the invalid data errors:

=IFERROR(AVERAGEIF([Duration of Hold]6:[Duration of Hold]100, >0), "")

See if that works!

Mark.poole

what if you change the > 0 to <>””

Kate Allison

@bisaacs It did not work :( It no longer has an error, but now it's just returning a blank cell. It should return a 5 based on the data currently in the sheet.

@Mark.poole changing to <>"" also did not work and still returned #UNPARSABLE.

Maybe there's a way to incorporate ISNUMBER? I haven't figured it out so far this morning.

Mark.poole

@Kate Allison

Ok lets try adding an IFERROR to your formula in the Duration of hold to return a blank instead of an error.

=IFERROR(your formula),"")

THEN you can do

=AVG(COLLECT([Duration of Hold]:[Duration of Hold],[Hold Start Date]:[Hold Start Date], @cell <> "",[Hold End Date]:[Hold End Date],@cell <> ""))

Kate Allison

@Mark.poole adding in IFERROR worked - thank you!!