Skip to main content

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.

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!

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!


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

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!


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

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.

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.


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.

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.


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.

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.



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


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




Note, there will ALWAYS be a number for impressions, but NOT always with clicks. Not sure if that makes a difference or not.

What determines if a record is “past”, “current”, or “future”?


It also looks like you are using your original formula in your screen shot. Is there a reason why you have not tried the suggestion put forth in this thread?

What determines if a record is “past”, “current”, or “future”?


It also looks like you are using your original formula in your screen shot. Is there a reason why you have not tried the suggestion put forth in this thread?


Thank you, kuovonne. I have inserted your formula, and it works well. I guess I was also looking for future results to say, “not available”, but if it’s blank, that works for me too.


In answer to your question about what determines past, present, or future, please see below:


Past Click-Through Rate = 0 (if no clicks) or a decimal percentage if there were clicks i.e.: 1.98%


Present Click-Through Rate = same as above


Future Click Through Rate = a blank cell or “Not Yet Available”

Reply


Terms & Conditions