function readOnly(count){ }
Sign Up ›
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
Learn More

Browse by Topic
ShowAll Questionssorted byDate Posted
+ Start a Discussion
Rung41Rung41 

Help with multiplier formula

I have the following formula feild. Based on the average rent it receives an applicable score.
IF(Average_Rent__c > 2999, 14.00, 
IF(Average_Rent__c> 1099, 10.50, 
IF(Average_Rent__c > 699,7.00, 
IF(Average_Rent__c < 700, 1.75, NULL)))).

I need update it to include a multipler based on a picklist field "Region" and its values New York, Chicago, Los Angeles.

So if the picklist "Region", value is New York,  multiple the Average rent by 3, Chicago by 1.5 and Los Angeles by 2.

 


 

Best Answer chosen by Rung41
ClintLeeClintLee
Yes, you could do that. 

It depends on where in the formula you want to multiply the average rent.

For example:

IF( (Average_Rent__c * Region_Multiplier__c) > 2999, 14.00, 
IF( (Average_Rent__c * Region_Multiplier__c) > 1099, 10.50, 
IF( (Average_Rent__c * Region_Multiplier__c) > 699, 7.00, 
IF( (Average_Rent__c * Region_Multiplier__c) < 700, 1.75,
NULL)))).
 
Or:

IF( Average_Rent__c > (2999 * Region_Multiplier__c), 14.00, 
IF( Average_Rent__c > (1099 * Region_Multiplier__c), 10.50, 
IF( Average_Rent__c > (699 * Region_Multiplier__c), 7.00, 
IF( Average_Rent__c < (700 * Region_Multiplier__c), 1.75, 
NULL)))).
 

All Answers

ClintLeeClintLee
You could create another formula field to store your multiplier named Region_Multiplier__c.  

The formula would be something like:
Case(
    Region__c,
    "New York",
    3,
    "Chicago",
    1.5,
    "Los Angeles",
    2,
    1
).

The formula in your original field would be like this:

IF(Average_Rent__c > 2999, 14.00 * Region_Multiplier__c, 
IF(Average_Rent__c> 1099, 10.50 * Region_Multiplier__c, 
IF(Average_Rent__c > 699,7.00 * Region_Multiplier__c, 
IF(Average_Rent__c < 700, 1.75 * Region_Multiplier__c, NULL)))).
 
Rung41Rung41

So based on the update formula, is the multipler updating the score or the average rent?


IF(Average_Rent__c > 2999, 14.00 * Region_Multiplier__c, 
IF(Average_Rent__c> 1099, 10.50 * Region_Multiplier__c, 
IF(Average_Rent__c > 699,7.00 * Region_Multiplier__c, 
IF(Average_Rent__c < 700, 1.75 * Region_Multiplier__c, NULL)))).
 
The score should remain the same but the average should be multiplied
For example, if the region is Chicago multiple the average rent by 1.5 but keep the score of 14.

Can I do something like - IF(Average_Rent__c > (2999*Region_Multiplier__c), 14.00 .....
ClintLeeClintLee
Yes, you could do that. 

It depends on where in the formula you want to multiply the average rent.

For example:

IF( (Average_Rent__c * Region_Multiplier__c) > 2999, 14.00, 
IF( (Average_Rent__c * Region_Multiplier__c) > 1099, 10.50, 
IF( (Average_Rent__c * Region_Multiplier__c) > 699, 7.00, 
IF( (Average_Rent__c * Region_Multiplier__c) < 700, 1.75,
NULL)))).
 
Or:

IF( Average_Rent__c > (2999 * Region_Multiplier__c), 14.00, 
IF( Average_Rent__c > (1099 * Region_Multiplier__c), 10.50, 
IF( Average_Rent__c > (699 * Region_Multiplier__c), 7.00, 
IF( Average_Rent__c < (700 * Region_Multiplier__c), 1.75, 
NULL)))).
 
This was selected as the best answer
Jayson Faderanga 14Jayson Faderanga 14
try this man :D

IF(ISPICKVAL(Region__c, "New York") && (Average_Rent__c * 3) > 2999), 14.00,
IF(ISPICKVAL(Region__c, "New York") && (Average_Rent__c * 3) > 1099), 10.50,
IF(ISPICKVAL(Region__c, "New York") && (Average_Rent__c * 3) > 699), 7.00,
IF(ISPICKVAL(Region__c, "New York") && (Average_Rent__c * 3) < 700), 1.75, 

IF(ISPICKVAL(Region__c, "Chicago") && (Average_Rent__c * 1.5) > 2999), 14.00,
IF(ISPICKVAL(Region__c, "Chicago") && (Average_Rent__c * 1.5) > 1099), 10.50,
IF(ISPICKVAL(Region__c, "Chicago") && (Average_Rent__c * 1.5) > 699), 7.00,
IF(ISPICKVAL(Region__c, "Chicago") && (Average_Rent__c * 1.5) < 700), 1.75, 

IF(ISPICKVAL(Region__c, "Los Angeles") && (Average_Rent__c * 2) > 2999), 14.00,
IF(ISPICKVAL(Region__c, "Los Angeles") && (Average_Rent__c * 2) > 1099), 10.50,
IF(ISPICKVAL(Region__c, "Los Angeles") && (Average_Rent__c * 2) > 699), 7.00,
IF(ISPICKVAL(Region__c, "Los Angeles") && (Average_Rent__c * 2) < 700), 1.75, 
    
null
))))))))))))

Hope it helps :D
Rung41Rung41

Thanks guys! Both option work but unfortunately I am running into formula compile size issue. I have another formual field referencing this field and that formula is somewhat long. Wish they would raise the limit on the compile size.