Skip to main content
Solved

In desperate need of help with my formula

L

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.

Best answer by kuovonne

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.

View original
Did this topic help you find an answer to your question?

12 replies

A
  • augmented
  • Inspiring
  • 326 replies
  • August 19, 2022

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!

Hannah_Wiginton
1 person likes this
  • Hannah_Wiginton
    Hannah_Wiginton
L
  • Lena_Osorio-Gem
  • Author
  • Participating Frequently
  • 9 replies
  • August 19, 2022
augmented wrote:

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. :slightly_smiling_face:

L
  • Lena_Osorio-Gem
  • Author
  • Participating Frequently
  • 9 replies
  • August 19, 2022
augmented wrote:

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” :frowning:

kuovonne
Forum|alt.badge.img+17
  • kuovonne
  • Brainy
  • 5993 replies
  • Answer
  • August 19, 2022

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.

A
Hannah_Wiginton
2 people like this
  • A
    augmented
  • Hannah_Wiginton
    Hannah_Wiginton
A
  • augmented
  • Inspiring
  • 326 replies
  • August 19, 2022
kuovonne wrote:

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.

L
  • Lena_Osorio-Gem
  • Author
  • Participating Frequently
  • 9 replies
  • August 19, 2022
kuovonne wrote:

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.

kuovonne
Forum|alt.badge.img+17
  • kuovonne
  • Brainy
  • 5993 replies
  • August 19, 2022
Lena_Osorio-Gem wrote:

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?

L
  • Lena_Osorio-Gem
  • Author
  • Participating Frequently
  • 9 replies
  • August 19, 2022
kuovonne wrote:

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


L
  • Lena_Osorio-Gem
  • Author
  • Participating Frequently
  • 9 replies
  • August 19, 2022
Lena_Osorio-Gem wrote:


L
  • Lena_Osorio-Gem
  • Author
  • Participating Frequently
  • 9 replies
  • August 19, 2022
Lena_Osorio-Gem wrote:


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

kuovonne
Forum|alt.badge.img+17
  • kuovonne
  • Brainy
  • 5993 replies
  • August 19, 2022

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?

L
  • Lena_Osorio-Gem
  • Author
  • Participating Frequently
  • 9 replies
  • August 22, 2022
kuovonne wrote:

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


Most helpful members this week

Terms & Conditions