Skip to main content

Formula referencing the result of IF formula returns ERROR

  • July 5, 2017
  • 2 replies
  • 16 views
D

Hi, whenever I try to reference a set of results I have in a “YIELD” column I get an error. the yield data is the result of a nested IF formula which itself references a single select field. Is there something I’m missing that means this data can’t be referenced by another formula?

The Yield formula is:
IF(TYPE = “Firkin”, “62”,
IF(TYPE = “Pin”, “31”,
IF(TYPE = “20L Keg”,“0”,
IF(TYPE = “30L Keg”,“0”,
IF(TYPE = “24 Case”,“24”,
IF(TYPE = “20 Case”,“20”,
IF(TYPE = “18 Case”,“18”,
IF(TYPE = “12 Case”,“12”,
IF(TYPE = “8 Case”,“8”,
IF(TYPE = “6 Case”,“6”,
“”))))))))))
Where TYPE is a single select field. But even if I try another formula field with YIELD*2 I get an error.

Any help much appreciated!

2 replies

D
  • Dan_Richardson
  • Author
  • New Participant
  • July 5, 2017

Think I’ve sussed this, using VALUE(YIELD) does the job.

W
Forum|alt.badge.img+5
  • W_Vann_Hall
  • Inspiring
  • July 30, 2017

Think I’ve sussed this, using VALUE(YIELD) does the job.


Just a breadcrumb for future searchers, but another way to accomplish this would be to remove the double quotes (") from around the quantities in your IF() statements – for instance, IF(TYPE = "Firkin", 62,… etc. – and change the final empty double quote ("") to BLANK(). This causes the formula to return a numeric value rather than a string, which can then be used in another formula without explicit conversion.

Note the presence of a single string value – for instance, “”, an empty string – as a possible response in the nested IF() statement immediately casts all results to strings. Similarly, ERROR() will also cast the result as a string, but BLANK() will not.[1]

  1. Unless another possible response is a string, that is.
Terms & ConditionsAccessibility statement