# If then Help

Options

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

• ✭✭✭✭✭
Options

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

• ✭✭✭✭✭
Options

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

• Options

This worked. Thank you Dave!

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!