Average CTR by Type of Ad by Year with DD/MM/YYYY Format

I'm looking to average the click through rates (CTR) by type of ad for 2023 in the SmartSheet Summary Sheet section.

There is data from 2022 and 2024 on my sheet, so I'm trying to make sure the data is only calculated between 1/1/2023 and 12/31/2023.

I'm using the AVG and COLLECT formulas and keep getting errors. Any help is greatly appreciated.

Current Formula - specific to Text Ads (not working)

=AVG(COLLECT([Start Date]:[Start Date], AND@cell >= DATE(2023, 1, 1), @cell <= DATE(2023, 12, 31), [Ad Type]:[Ad Type], "Text Ad"), CTR:CTR)

Provided Data Table in excel below for reference, but using smartsheet for the calculations.


Best Answers

  • gabriellek
    gabriellek ✭✭
    Answer ✓

    @Eric Law yes it is a Date column.

  • gabriellek
    gabriellek ✭✭
    Answer ✓

    Looks like I was just able to work it out! By changing the date format to not be by just the year, it did end up giving me the correct average. Not too sure why the Year function is not working for me, but thank you @Eric Law for helping me fix my formula and get to the answer!

    =AVG(COLLECT(CTR:CTR, [Start Date]:[Start Date], AND(@cell >= DATE(2023, 1, 1), @cell <= DATE(2023, 12, 31)), [Ad Type]:[Ad Type], "Text Ad"))


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!