Summary Field that Averages Field Entries that are Less than Zero

megbarclayllx

I have a calculated field for the time over or under a deadline date. I want to create a summary field that summarizes the number of days overdue for projects completed after their deadlines.

Right now I'm trying =averageif([field name]:[field name],<0,[field name]:[field name])

I get an "Invalid Operation" error when I do this. Can someone give me some advice on a better approach?

Thanks!

MichaelTCA

Hello @megbarclayllx

What are the column properties for "Field Name"?

Are you using another function/helper column for the number of days overdue?

megbarclayllx

It's a text/number field. It's a formula that's calculating the number of days overdue.

megbarclayllx

Also hello and thank you!!

MichaelTCA

@megbarclayllx

What is that function you're using for the difference?

Can you provide screenshots?

megbarclayllx

Sure thing. Here it is:

Paul Newcome

Do you have that error present in any of the cells within the column being referenced? If so, you will need to wrap your existing formula in an IFERROR statement.

=IFERROR(original_formula, "")

megbarclayllx

Hi there! thanks for that tip!

I'm getting the error when I try to program the sheet summary filed. Thank you!

Paul Newcome

Right. But of that error is present in even a single cell that is being referenced, it will pull through.

It is possible your AVERAGEIF is just fine, but the formula you are using to populate the referenced colum is throwing an error somewhere which in turn is causing the issue in your sheet summary field.

megbarclayllx

I see. thanks. I'll try it!