Complex IF using the result of another formula field

[SMP]

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!

[Raminder_Singh · Accepted solution]

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

[SMP]

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