# Complex IF using the result of another formula field

Topic Labels: Formulas
Solved
374 2
cancel
Showing results for
Did you mean:  4 - Data Explorer

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!

1 Solution

Accepted Solutions  7 - App Architect

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")))))
``````
2 Replies 2  7 - App Architect

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")))))
``````  4 - Data Explorer

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