Need help deciphering calculated field | Community
Skip to main content
Level 3
January 27, 2020
Question

Need help deciphering calculated field

  • January 27, 2020
  • 4 replies
  • 801 views
Hi all, Our WF consultant created the calculated field below on our request form, and I'm trying to re-create it but can't figure out what the "LEN ... -2" does/means. The below calculation displays all the Digital Project Types and Design-Print Project Types a requestor selects (these are two separate custom forms, and requestors can select multiple deliverables under each): LEFT(CONCAT(IF(!ISBLANK(Digital Project Types),CONCAT(Digital Project Types,", "),""), IF(!ISBLANK({Design-Print Project Types}),CONCAT({Design-Print Project Types},", "),"")), LEN (CONCAT(IF(!ISBLANK(Digital Project Types),CONCAT(Digital Project Types,", "),""), IF(!ISBLANK({Design-Print Project Types}),CONCAT({Design-Print Project Types},", "),""))) -2 ) If I delete the "LEN ... -2)" calculation, nothing displays in the field. Conversely, if I delete the calculation preceding the "LEN" calculation, "0" displays. So I understand that both must be present to successfully show the list of deliverables the requestor chose, but I don't understand the reasoning behind it. I want to understand this calculation before I start re-creating it on other forms. Hope someone can break this down for me! Erica Lorenzo Writer-Editor BKD, LLP
This post is no longer active and is closed to new replies. Need help? Start a new post to ask your question.

4 replies

Level 3
January 28, 2020
Erica, The -2 pairs with LEFT command. I believe, the negative number should provide the entire string minus 2 from the right. Patricia Greene JLL
EricaLoAuthor
Level 3
January 28, 2020
OH wow, I didn't even think of that. That makes more sense. Do you know how the LEN is functioning in this calculation? Erica Lorenzo
January 29, 2020
It's difficult without seeing the result but LEN is a function that calculates string length. In Excel, you use the above to remove characters from a result. For example; Value = 45 Hotdogs Formula = VALUE(LEFT("# Hotdogs",LEN("# Hotdogs")-8)) --- "# Hotdogs" would be a cell value, for example B2 Result = 45 (see quick example below) Hope this helps Best, Christian Christian Barnes Dentsu Aegis Network
EricaLoAuthor
Level 3
January 30, 2020
This helped! Thank you so much for your response! Erica Lorenzo