Skip to main content
Level 3
January 27, 2020
Question

Need help deciphering calculated field

  • January 27, 2020
  • 4 replies
  • 800 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

EricaLoAuthor
Level 3
January 30, 2020
This helped! Thank you so much for your response! Erica Lorenzo
New Participant
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 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
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