AVERAGEIF

Hello!
I'm trying to use the AVERAGEIF function in my app; my idea is to use that for calculate the average of a column of data in my table, but using conditions and functions. I wanna make it calculate the average of values on today's date, ignoring past values in the column. For that, I created this formula:

=AVERAGEIF(Table[Days],">DATE(YEAR(NOW()),MONTH(NOW()),DAY(NOW())",Table[Values])

For example, in column "Days", I have the serial date of yesterday (44402.5), and the now serial date (44403.2). My average should only calculate for today, because DATE function returns 44403, but it isn't work. I tried to use the number itself, and It works; but to make it useful, this value must change every day. There's a way to use this function or a cell reference in AVERAGEIF?
Thanks!

Hi @Plan-c2ab :honeybee :honey_pot:

Yes, you can use AVERAGEIF to calculate an average of dates :slight_smile: Happy to show you how:

I created an example with yesterday's and tomorrow's dates. To calculate the average, I used =AVERAGEIF(A2:A3, ">0"), which is the range of the dates in the table and will give me the serial value of that date:

To convert it to a date, simply format the column with the calculated result to a Date format:

For this example, it gives the expected result of today (7/27/21) as the average.

Hope this helps! :honey_pot:

Hello!
Thanks a lot for replying!
I used =AVERAGEIF(Table[Days],">"&Table!A1,Table[Values]). In Table!A1 is the date formula that I want.

Thank you so much!

1 Like

Great, I'm happy you got it working, @Plan-c2ab! And of course, we're always happy to help :slightly_smiling_face: :honeybee:

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.