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
-
@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"))
Answers
-
@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,
=AVG(COLLECT(CTR:CTR, [Start Date]:[Start Date], YEAR(@cell) = 2023, [Ad Type]:[Ad Type], "Text Ad"))
-
@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.
=AVG(COLLECT(CTR:CTR, [Start Date]:[Start Date], YEAR(@cell) = 2023, [Ad Type]:[Ad Type], "Text Ad"))
I tried moving the parenthesis over for the year too, but got the same error code. Any ideas?
=AVG(COLLECT(CTR:CTR, [Start Date]:[Start Date], YEAR(@cell = 2023), [Ad Type]:[Ad Type], "Text Ad"))
-
@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"))
-
@gabriellek Glad you got it!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.9K Get Help
- 441 Global Discussions
- 139 Industry Talk
- 471 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 68 Community Job Board
- 496 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!