Skip to main content
Solved

Complex IF using the result of another formula field

S
  • SMP
  • Participating Frequently
  • 7 replies

So when I first built this IF statement, it brought back “Drabble” correctly when the Word Count was 100 words:

IF({Word Count}=100,“Drabble”,IF({Word Count}=200,“Double Drabble”,IF({Word Count}>39999,“Novel”,IF(AND({Word Count}>17499,{Word Count}<40000),“Novella”,IF(AND({Word Count}>7499,{Word Count}<17500),“Novelette”,IF(AND({Word Count}>1000,{Word Count}<7500),“Short Story”,“Ficlet”))))))

Word Count was using the formula provided by airtable help (which is bunk), that is

IF({Text}=BLANK(), 0, LEN({Text}) - LEN(SUBSTITUTE({Text}, ’ ', ‘’))+1)

When I pasted in a long text that I already knew was 200 words and it brought back 185, I knew I was in trouble.

Long story short, it turns out that word count formula in the official help counts the number of spaces and only works if you limit yourself to a single paragraph.

So…

Now I have the Space Within Paragraph Count field:

IF({Text}=BLANK(), 0, LEN({Text}) - LEN(SUBSTITUTE({Text}, ’ ', ‘’))+1)

And the Paragraph Count field:

(LEN(TRIM({Text}))-LEN(SUBSTITUTE(TRIM({Text}),"\n",""))+1)/2

And the Checked for Single-Word Paragraphs? checkbox field.

And the new {Word Count} formula field looks like this:

IF({Checked for Single-Word Paragraphs?}=BLANK(),BLANK(),{Space Within Paragraph Count}+{Paragraph Count})

But when this correctly results in 100, the original formula for story type no longer brings back “Drabble”, even though it is 100, which I double checked by updating the formula to {Word Count}-2, which brought back 98.

I don’t know how to get the original formula working again. Help!

Best answer by Raminder_Singh

Hello @SMP,

The root of the problem is that what you see as 100 is not actually 100 in the Word Count field. You can check this by changing the format from Integer to Decimal:

In my case the actual number was 99.5 but it was shown as 100 when I had Integer selected in the format.

Also instead of matching with exact values ({Word Count} = 100) maybe the compare the ranges ({Word Count} <= 100). E.g.:

IF({Word Count} <= 100, "Drabble",
  IF(AND({Word Count} > 100, {Word Count} <= 200), "Double Drabble",
    IF(AND({Word Count} > 200, {Word Count} <= 1000), "Ficlet",
      IF(AND({Word Count} > 1000, {Word Count} <= 7500), "Short Story",
        IF(AND({Word Count} > 7500, {Word Count} <= 17500), "Novelette"),
          IF(AND({Word Count} > 17500), {Word Count} <= 40000), "Novella",
            IF({Word Count} > 40000, "Novel")))))
View original
Did this topic help you find an answer to your question?

2 replies

R
  • Raminder_Singh
  • Participating Frequently
  • 59 replies
  • Answer
  • July 25, 2021

Hello @SMP,

The root of the problem is that what you see as 100 is not actually 100 in the Word Count field. You can check this by changing the format from Integer to Decimal:

In my case the actual number was 99.5 but it was shown as 100 when I had Integer selected in the format.

Also instead of matching with exact values ({Word Count} = 100) maybe the compare the ranges ({Word Count} <= 100). E.g.:

IF({Word Count} <= 100, "Drabble",
  IF(AND({Word Count} > 100, {Word Count} <= 200), "Double Drabble",
    IF(AND({Word Count} > 200, {Word Count} <= 1000), "Ficlet",
      IF(AND({Word Count} > 1000, {Word Count} <= 7500), "Short Story",
        IF(AND({Word Count} > 7500, {Word Count} <= 17500), "Novelette"),
          IF(AND({Word Count} > 17500), {Word Count} <= 40000), "Novella",
            IF({Word Count} > 40000, "Novel")))))
S
1 person likes this
S
  • SMP
  • Author
  • Participating Frequently
  • 7 replies
  • July 25, 2021
Raminder_Singh wrote:

Hello @SMP,

The root of the problem is that what you see as 100 is not actually 100 in the Word Count field. You can check this by changing the format from Integer to Decimal:

In my case the actual number was 99.5 but it was shown as 100 when I had Integer selected in the format.

Also instead of matching with exact values ({Word Count} = 100) maybe the compare the ranges ({Word Count} <= 100). E.g.:

IF({Word Count} <= 100, "Drabble",
  IF(AND({Word Count} > 100, {Word Count} <= 200), "Double Drabble",
    IF(AND({Word Count} > 200, {Word Count} <= 1000), "Ficlet",
      IF(AND({Word Count} > 1000, {Word Count} <= 7500), "Short Story",
        IF(AND({Word Count} > 7500, {Word Count} <= 17500), "Novelette"),
          IF(AND({Word Count} > 17500), {Word Count} <= 40000), "Novella",
            IF({Word Count} > 40000, "Novel")))))

Ah! Thank you! I forgot the paragraph count had a rounding requirement to work. I’ll work on fixing it.

ETA: Updated Word Count field to IF({Checked for Single-Word Paragraphs?}=BLANK(),BLANK(),ROUND({Space Within Paragraph Count}+{Paragraph Count},0))

Reply


Most helpful members this week

Terms & Conditions