If then Help

BKlucker

I have columns with construction bids and the rows are the construction activities with cost. I have 2 years of bids and just want to take the average of the last 6 months of bids for each row of construction activity.

=IF([1]4:[101]4 > TODAY(-180), AVG([1]@row:[101]@row), 0)

Columns are labeled 1 -101

#INVALID OPERATION

DKazatsky2

Hi @BKlucker,

Here is what I suggest. Create another column to be used as a "helper" to hold the average per row.

New Column Name = "AVG (helper)" -- Formula: =IFERROR(AVG([1]@row:[101]@row), 0)

Formula for the average within 6 months:

=AVG(COLLECT([Avg (helper)]:[Avg (helper)], [Bid Date]:[Bid Date], >=TODAY(-180)))

[Bid Date] should be changed to whatever column you are using for the date field.

Hope this helps,

Dave

DKazatsky2

Hi @BKlucker,

Here is what I suggest. Create another column to be used as a "helper" to hold the average per row.

New Column Name = "AVG (helper)" -- Formula: =IFERROR(AVG([1]@row:[101]@row), 0)

Formula for the average within 6 months:

=AVG(COLLECT([Avg (helper)]:[Avg (helper)], [Bid Date]:[Bid Date], >=TODAY(-180)))

[Bid Date] should be changed to whatever column you are using for the date field.

Hope this helps,

Dave

BKlucker

This worked. Thank you Dave!