Average if but exclude rows with today's date

[GREGRACH]

Hi All,

I want to average the dollar amounts in a column but I need to exclude those columns that have a zero dollar value and I also need to exclude rows that have today's date. I am lost as to how to do this and any advice would be hugely appreciated!

A B C D
1 Job 222 Joe Bloggs 9/3/17 $587.00
2 Job 223 Mary Simms 10/2/16 $522.00
3 Job 224 Jim Timm 13/3/16 $800.00 etc


I want to average column D but I want to exclude the dates in column C that are today's date. I also need to exclude any blank cells from column D.

Thanks in advance!

 

[JoeMo]

Try this - change the ranges to suit:

=AVERAGEIF(C1:C20,"<>"&TODAY(),D1:D20)

 

[GREGRACH]

Thank you so much for this! Worked perfectly. If you don't mind answering another question......If I were to do the opposite with the date and only want it to select rows with less than todays date, what do I need to change the &TODAY() to?

Try this - change the ranges to suit:

=AVERAGEIF(C1:C20,"<>"&TODAY(),D1:D20)

 

[JoeMo]

Thank you so much for this! Worked perfectly. If you don't mind answering another question......If I were to do the opposite with the date and only want it to select rows with less than todays date, what do I need to change the &TODAY() to?

You are welcome.

For your other question try:

=AVERAGEIF(C1:C20,"<"&TODAY(),D1:D20)