# How can I simplify the formula to avoid limit?

I'm getting "Maximum size is 5,000 characters" for formula, when trying to do this:

``````((Account.Cost_per_day__c +

IF(
(AND(Cleaning__c = TRUE, Parking__c = TRUE)),
(VALUE(TEXT(Number_of_cleaning__c)) * VALUE(TEXT(Account.Cleaning_cost__c)))+(VALUE(TEXT(Account.Parking_cost__c))),

(IF(
(AND(Cleaning__c = TRUE, Parking__c = FALSE)),
(VALUE(TEXT(Number_of_cleaning__c)) * VALUE(TEXT(Account.Cleaning_cost__c))),

(IF(
(AND(Cleaning__c = FALSE, Parking__c = TRUE)),
(VALUE(TEXT(Account.Parking_cost__c))),
0)))))) - (
(Account.Cost_per_day__c +

IF(
(AND(Cleaning__c = TRUE, Parking__c = TRUE)),
(VALUE(TEXT(Number_of_cleaning__c)) * VALUE(TEXT(Account.Cleaning_cost__c)))+(VALUE(TEXT(Account.Parking_cost__c))),

(IF(
(AND(Cleaning__c = TRUE, Parking__c = FALSE)),
(VALUE(TEXT(Number_of_cleaning__c)) * VALUE(TEXT(Account.Cleaning_cost__c))),

(IF(
(AND(Cleaning__c = FALSE, Parking__c = TRUE)),
(VALUE(TEXT(Account.Parking_cost__c))),
0)))))) *
Account.Apartment_Owner__r.Owner_percentage__c)) *
(IF(
(AND(MONTH(TODAY()) = MONTH(Start_Date__c), MONTH(TODAY()) = MONTH(CloseDate))),
Days__c,
IF(
(AND(MONTH(TODAY()) > MONTH(Start_Date__c), MONTH(TODAY()) = MONTH(CloseDate))),
(Days__c -((TODAY()-DAY(TODAY())+1)-Start_Date__c)),
IF(
(AND(MONTH(TODAY()) = MONTH(Start_Date__c), MONTH(TODAY()) < MONTH(CloseDate))),
(IF
(MONTH(TODAY()) = 12,
DATE(YEAR(TODAY()), 12, 31),
DATE(YEAR(TODAY()), MONTH(TODAY()) + 1, 2) - 1) - Start_Date__c),
IF(
(AND(MONTH(TODAY()) > MONTH(Start_Date__c), MONTH(TODAY()) < MONTH(CloseDate))),
DAY(ADDMONTHS(DATE(YEAR(TODAY()), MONTH(TODAY()), 01),1) -1),
0)))))
``````

How can I simplify the formula? If it's possible.

• what is the formula outcome?Character is showing 856. How it crossing the limit? – Santanu Boral May 3 '19 at 13:03
• I fixed it. But maybe it has more formulas inside. Do you know how to simplify only the formula you see? – MichaelLev19 May 3 '19 at 13:30
• @SantanuBoral its compiled formula size of 5000 chars – Pranay Jaiswal May 3 '19 at 13:52
• I'm not in a place where I can look at this right now, but review the Salesforce tip sheet for reducing formula size: resources.docs.salesforce.com/218/latest/en-us/sfdc/pdf/… -- you're hitting the formula compile size limit, not the character limit, so simply removing text from the formula is not going to solve your problem. – shannonsans May 3 '19 at 15:47

## General Advice

• Remove all parentheses you don't actually need.
• Unnecessary parentheses add to your character count and make the formula harder to read.
• Given the current state of your formula, it is extremely difficult to even match opening and closing parentheses because of the sheer number of them (as well as the lack of consistent indentation).
• As mentioned already, do not compare checkboxes to true or false.
• You can just merge in the `Boolean` value directly.
• You can also replace `MyCheckbox__c = FALSE` with `NOT(MyCheckbox__c)`.
• Don't convert a number field to text then back to number.

• Instead of:

``````VALUE(TEXT(Number_of_cleaning__c))
``````
• Use:

``````Number_of_cleaning__c
``````
• Same goes for `Cleaning_cost__c` and `Parking_cost__c`

• If these fields are picklist, add workflows to store the numeric values in a separate field of type `Number` (or `Currency` where appropriate) whenever a record is edited.
• Reduce repetitive references to same field.
• More detail below.

## First Overall Calculation (Cost Per Day)

You have this sub-formula which you repeat twice, once to subtract a percentage:

``````IF(
AND(Cleaning__c, Parking__c),
(
Number_of_cleaning__c * Account.Cleaning_cost__c+Account.Parking_cost__c,
IF(
AND(Cleaning__c, NOT(Parking__c)),
Number_of_cleaning__c * Account.Cleaning_cost__c,
IF(
AND(NOT(Cleaning__c), Parking__c),
Account.Parking_cost__c,
0
)
)
)
)
``````

First of all, the sub-formula can be simplified to:

``````IF(Cleaning__c, Number_of_cleaning__c * Account.Cleaning_cost__c, 0) +
IF(Parking__c, Account.Parking_cost__c, 0)
``````

Second of all, you don't need to run these calculations twice. The portion where you calculate cost per day can be simplified to:

``````(
Account.Cost_per_day__c +
IF(Cleaning__c, Number_of_cleaning__c * Account.Cleaning_cost__c, 0) +
IF(Parking__c, Account.Parking_cost__c, 0)
) * (1 - Account.Apartment_Owner__r.Owner_percentage__c)
``````

Now each field in this section is referenced once, whereas before you referenced `Cleaining__c` and `Parking__c` 6 times each. You also referenced `Account.Cleaning_cost__c`, `Number_of_cleaning__c`, and `Account.Parking_cost__c` 4 times each. So this simplification reduces your field reference count from 24 down to 5. Field references are expensive!

## Second Overall Calculation (Day Count)

After calculating the cost per day, you calculate the number of days. I haven't yet spent the time to try to understand this logic. If you are still over the character limit, try moving it into a workflow and persist some `Cleaning_Day_Count__c` value on the object.

# Simplified Formula

Indentation is really important for making legible formulas. I did my best with what you had.

``````(
Account.Cost_per_day__c +
IF(Cleaning__c, Number_of_cleaning__c * Account.Cleaning_cost__c, 0) +
IF(Parking__c, Account.Parking_cost__c, 0)
) * (1 - Account.Apartment_Owner__r.Owner_percentage__c) * (
IF(
AND(
MONTH(TODAY()) = MONTH(Start_Date__c),
MONTH(TODAY()) = MONTH(CloseDate)
),
Days__c,
IF(
AND(
MONTH(TODAY()) > MONTH(Start_Date__c),
MONTH(TODAY()) = MONTH(CloseDate)
),
Days__c - (
(
TODAY() - DAY(TODAY()) + 1
) - Start_Date__c
),
IF(
AND(
MONTH(TODAY()) = MONTH(Start_Date__c),
MONTH(TODAY()) < MONTH(CloseDate)
),
IF(
MONTH(TODAY()) = 12,
DATE(YEAR(TODAY()), 12, 31),
DATE(YEAR(TODAY()), MONTH(TODAY()) + 1, 2) - 1
) - Start_Date__c,
IF(
AND(
MONTH(TODAY()) > MONTH(Start_Date__c),
MONTH(TODAY()) < MONTH(CloseDate)
),
DAY(
ADDMONTHS(
DATE(
YEAR(TODAY()), MONTH(TODAY()), 01
), 1
) - 1
),
0
)
)
)
)
``````
• They are there for a reason: `Field Number_of_cleaning__c is a picklist field. Picklist fields are only supported in certain functions.` – MichaelLev19 May 3 '19 at 14:24
• Wait, you have a picklist field to store numeric values? – Adrian Larson May 3 '19 at 14:25
• Yes. I need it for Field Dependencies. It's a Technical task. – MichaelLev19 May 6 '19 at 7:34
• Thank you for your help so much :) – MichaelLev19 May 6 '19 at 7:48

One quick thing I can see is you are using a lot of `Cleaning__c = TRUE` inside your if block. It can simply be written as `(AND(Cleaning__c , Parking__c))`

``````((Account.Cost_per_day__c +

IF(
(AND(Cleaning__c , Parking__c )),
(VALUE(TEXT(Number_of_cleaning__c)) * VALUE(TEXT(Account.Cleaning_cost__c)))+(VALUE(TEXT(Account.Parking_cost__c))),

(IF(
(AND(Cleaning__c, NOT(Parking__c))),
(VALUE(TEXT(Number_of_cleaning__c)) * VALUE(TEXT(Account.Cleaning_cost__c))),

(IF(
(AND(NOT(Cleaning__c), Parking__c)),
(VALUE(TEXT(Account.Parking_cost__c))),
0)))))) - (
(Account.Cost_per_day__c +

IF(
(AND(Cleaning__c, Parking__c)),
(VALUE(TEXT(Number_of_cleaning__c)) * VALUE(TEXT(Account.Cleaning_cost__c)))+(VALUE(TEXT(Account.Parking_cost__c))),

(IF(
(AND(Cleaning__c , NOT(Parking__c) )),
(VALUE(TEXT(Number_of_cleaning__c)) * VALUE(TEXT(Account.Cleaning_cost__c))),

(IF(
(AND(NOT(Cleaning__c), Parking__c )),
(VALUE(TEXT(Account.Parking_cost__c))),
0)))))) *
Account.Apartment_Owner__r.Owner_percentage__c)) *
(IF(
(AND(MONTH(TODAY()) = MONTH(Start_Date__c), MONTH(TODAY()) = MONTH(CloseDate))),
Days__c,
IF(
(AND(MONTH(TODAY()) > MONTH(Start_Date__c), MONTH(TODAY()) = MONTH(CloseDate))),
(Days__c -((TODAY()-DAY(TODAY())+1)-Start_Date__c)),
IF(
(AND(MONTH(TODAY()) = MONTH(Start_Date__c), MONTH(TODAY()) < MONTH(CloseDate))),
(IF
(MONTH(TODAY()) = 12,
DATE(YEAR(TODAY()), 12, 31),
DATE(YEAR(TODAY()), MONTH(TODAY()) + 1, 2) - 1) - Start_Date__c),
IF(
(AND(MONTH(TODAY()) > MONTH(Start_Date__c), MONTH(TODAY()) < MONTH(CloseDate))),
DAY(ADDMONTHS(DATE(YEAR(TODAY()), MONTH(TODAY()), 01),1) -1),
0)))))
``````
• Thank you! It helps to remove 300 characters :) But it's still to big. – MichaelLev19 May 3 '19 at 14:01