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

gabriellek

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.

Advertising.xlsx

Uploaded Dec 7, 202310.75 KB

gabriellek

@Eric Law yes it is a Date column.

gabriellek

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

Eric Law

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

gabriellek

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

Eric Law

@gabriellek is your Start Date a date column?

gabriellek

@Eric Law yes it is a Date column.

Eric Law

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

gabriellek

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

Eric Law

@gabriellek Glad you got it!