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
OleGoRulesOleGoRules 

Need help with formula

Hey guys, I need some help writing a formula for a combination of checkbox and number fields. Any help would be much appreciated. The logic is to have an engagement scoring model on a scale of (1-5 stars).  The engagement score is a progressive measure of an employer's performance over the fiscal year.
If the contact is a top employer (checkbox) - 1 star
If a top performer (checkbox) - 1 star
No. of in person consultations (number field) -  1/2 star for each consultation, max up to 5 consultations a total of 2.5 stars accumulated
No. of appointments booked over the phone (number field) - 1 star for each booking, max up to 3 stars accumulated
No. of seminars booked  (number field) - 1/2 star for every 3 seminars booked
Thanks.
Best Answer chosen by OleGoRules
Zachery EngmanZachery Engman
Hi Omayer,

I believe this will accomplish what you are seeking, I tried to put the field names similar to the requirement.  Hope it helps:

MIN(5, IF(Is_Top_Employer__c , 1, 0) + IF( Is_Top_Performer__c , 1, 0) + MIN(2.5, 0.5 * In_Person_Consults__c ) + MIN(3, Appts_Over_Phone__c * 1) + ( (Seminars_Booked__c - MOD(Seminars_Booked__c, 3)) /3 * 0.5))

*Be sure that below the formula expression the checkbox for: "Treat blank fields as zeroes" is checked.

All Answers

Zachery EngmanZachery Engman
Hi Omayer,

I believe this will accomplish what you are seeking, I tried to put the field names similar to the requirement.  Hope it helps:

MIN(5, IF(Is_Top_Employer__c , 1, 0) + IF( Is_Top_Performer__c , 1, 0) + MIN(2.5, 0.5 * In_Person_Consults__c ) + MIN(3, Appts_Over_Phone__c * 1) + ( (Seminars_Booked__c - MOD(Seminars_Booked__c, 3)) /3 * 0.5))

*Be sure that below the formula expression the checkbox for: "Treat blank fields as zeroes" is checked.
This was selected as the best answer
OleGoRulesOleGoRules
Thankyou so much Zach - works like a charm!
OleGoRulesOleGoRules
Hey Zach, I had one more question - so the formula works great, and I set up another formula field referencing to the engagement score formula field (0-5) to associate a star image (1star-5stars), but the problem is i have 10 combinations in total, which violated the maximum 5000 character restrictions. Any Idea if I have the images populated within the original formula field?
Zachery EngmanZachery Engman
Hi Omayer,

You should be able to keep it condensed and reference the original formula with a Case Statement, something like this should easily fit within 5k characters:

CASE(Star_Ranking__c, 
1, IMAGE('http://images.clipartpanda.com/clipart-star-RTA9RqzTL.png', '1 Star'), 
1.5, IMAGE('http://images.clipartpanda.com/clipart-star-RTA9RqzTL.png', '1.5 Stars'), 
2, IMAGE('http://images.clipartpanda.com/clipart-star-RTA9RqzTL.png', '2 Stars'), 
2.5, IMAGE('http://images.clipartpanda.com/clipart-star-RTA9RqzTL.png', '2.5 Stars'), 
3, IMAGE('http://images.clipartpanda.com/clipart-star-RTA9RqzTL.png', '3 Stars'), 
3.5, IMAGE('http://images.clipartpanda.com/clipart-star-RTA9RqzTL.png', '3.5 Stars'), 
4, IMAGE('http://images.clipartpanda.com/clipart-star-RTA9RqzTL.png', '4 Stars'), 
4.5, IMAGE('http://images.clipartpanda.com/clipart-star-RTA9RqzTL.png', '4.5 Stars'), 
5, IMAGE('http://images.clipartpanda.com/clipart-star-RTA9RqzTL.png', '5 Stars'), 
IMAGE('http://images.clipartpanda.com/clipart-star-RTA9RqzTL.png', '0 Stars'))

Of course, replace the URLs with your respective images, I just threw a junk one in there that is the same for all.  Hope that will help, unless your URL is extremely long?
OleGoRulesOleGoRules
Hey Zach, 

I atucally uploaded the images to the documents tab - Thank you so much, works wonderfully :).