If then Help

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

Best Answer

  • DKazatsky2
    DKazatsky2 ✭✭✭✭✭✭
    Answer ✓

    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

Answers

  • DKazatsky2
    DKazatsky2 ✭✭✭✭✭✭
    Answer ✓

    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

  • This worked. Thank you Dave!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!