# 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.

Tags:

@Eric Law yes it is a Date column.

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"))

• ✭✭✭✭✭✭

@gabriellek I think your collect function is wrong, it should be =COLLECT(Range, Crit Range, Crit), also you can just use the year formula instead of jan 1 to dec 31. Try this,

• @Eric Law thank you! I think it's getting closer, but it doesn't appear that the date function is working. I'm getting the #INVALID DATA TYPE error when I use that formula.

I tried moving the parenthesis over for the year too, but got the same error code. Any ideas?

• ✭✭✭✭✭✭

@gabriellek is your Start Date a date column?

@Eric Law yes it is a Date column.

• ✭✭✭✭✭✭

@gabriellek Could you try a these two formulas and see what shows up as invalid. You can do this in the summary field or in a cell.

=YEAR([Start Date]1)

=AVG(CTR:CTR)

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!