Averageif not excluding 0 values in column

[rach2017]

Hi All,

I have a spreadsheet (will make it simple to make this easier). Column D has a date for date received , Column E has a date for date invoiced, Column F has a formula that works out the number of days between the two dates =DATEDIF(D10,E10,"d")

In my spreadsheet I have 100 rows. Some rows have data and some do not. All rows in Column F have the formula to work out the number of days.

I want to work out the average number of days in column F of rows for data that was invoiced prior to today's date. This is the formula I currently have:

=AVERAGEIF(E9:E134,"<>"&TODAY(),F9:F134)

The problem with this formula is, it is taking into account all 100 rows, not just the rows with data. How can I update the formula to ignore the '0' days in column F?

Thank you in advance!

[Phuocam]

Try this ...

=AVERAGEIFS(F9:F134,F9:F134,">0",E9:E134,"<>"&TODAY())

[rach2017]

That's it!!! Thank you so much!

[jaden1611]

Dear Rach
You can create a new column G which it is valued by 0 and 1. After that you can use your avarageif with range of G.
G is defined as =if(f9=0,0,1). I call it as data standardization.
Hope it helps.