In desperate need of help with my formula

[Lena_Osorio-Gem]

I am creating a table where our team enters LinkedIn social media analytics. I have only dabbled with formulas, so I went to my friend for help, and he gave me a good formula, but I think it needs tweaking.

Here is my dilemma:

First, I want the “Click-Through Rate” to display “Not Available” for future posts. For current posts (as well as past posts); I would like to display a number, even if it’s zero.

If you notice in my screenshot, there were certain past posts with data entered, but it doesn’t display anything in the Click-Through Rate cells. :woman_shrugging:t5:

Second, I would like to display the Click-Through Rate as a decimal that moves the decimal point. For example, instead of displaying 0.0198, I would like for it to display, 1.98%

Here is the formula:

IF({clicks} > 0, ROUND({Clicks}/{Impressions},2), if({Impressions} >0, ROUND({Clicks}/{Impressions},2), “Not Available”))

I appreciate any and all help.

[augmented]

Hi Lena. Just a few things should fix up your formula field.

1. Make your first “IF” test {Clicks} >=0
2. Take out the ROUND functions - will take care of that next
3. Select the “Formatting” tab (beside the “Formula” tab in the field editing window) and change the format to Percentage and select the precision that you want.

Good luck!

[Lena_Osorio-Gem]

Thanks for replying. I will give that a try and let you know. :slightly_smiling_face:

[Lena_Osorio-Gem]

Thanks for helping. I made the changes but it wouldn’t save the formula. It kept saying, “Invalid formula” :frowning:

[kuovonne · Accepted solution]

Welcome to the Airtable community!

By “current and past posts” do you mean posts with impressions?
You can try a simpler formula. Use the formatting options to format the result as a percent. Note that the formatting options are not available with your old formula because your old formula returned a text string, and not a number.

IF(
  {Impressions},
  {Clicks}/{Impressions}
)

This formula will not show “Not Available” because if you do that, you loose the ability to format the result as a number. Instead, this will leave the field blank if there are no impressions.

[augmented]

Great point, Kuovonne! I’m such a doofus. When I don’t replicate something myself, I tend to miss obvious things.

If it were me, I would have a filtered view to remove the campaigns without impressions, if necessary for UX.

[Lena_Osorio-Gem]

For current and past posts that have “0” clicks, I need for it to display a number result in the “Click Through Rate” column. If I can’t have a percentage, that’s fine, but I would like for it to show a number result, even if it’s zero.

Hopefully, that makes sense. Right now the line items that have “0” clicks are not showing anything in the Click Through Rate column. It’s just empty.

[kuovonne]

Lena_Osorio-Gemisic:

Right now the line items that have “0” clicks are not showing anything in the Click Through Rate column. It’s just empty.

Can you share a screen shots of your current formula configuration and of your data?

[Lena_Osorio-Gem]

[Lena_Osorio-Gem]