+ Start a Discussion
Tracy Oden 10Tracy Oden 10 

Multiple percentage fields shall total a single amount in another field

Can someone please provide a formula?

I have a single grand total amout field. I have various percentage fields that should equal a compoent of the grand total amount (but the various percentage fields must not cap more than 100% combined).

For example;

Grand Total Amount =  $200,000

Product 1 - 70%
Prouct 2 - 20%
Product 3 - 10%

If a percentage is attempted in product 4 or 5 based on the previous percentage amounts; a validation rule wll require a change. For example;

Product 4 - (must be reestricted unless a percentage in Product 1, 2, or 3 is lowered. Cannot be more than 100% of grand total).
Product 5 - (must be reestricted unless a percentage in Product 1, 2, or 3 is lowered. Cannot be more than 100% of grand total).
 
Best Answer chosen by Tracy Oden 10
kaustav goswamikaustav goswami
How many such product fields are there?

You can check the total of these fields in a validation rule
IF(Product 1 + Product 2 + Product 3 + Product 4 + Product 5 > 100, True, False)

You will have to then enter a message for the user explaning the reason for error.

Thanks,
Kaustav

All Answers

kaustav goswamikaustav goswami
How many such product fields are there?

You can check the total of these fields in a validation rule
IF(Product 1 + Product 2 + Product 3 + Product 4 + Product 5 > 100, True, False)

You will have to then enter a message for the user explaning the reason for error.

Thanks,
Kaustav
This was selected as the best answer
Tracy Oden 10Tracy Oden 10
Your answer is truly the best annswer, but it is not seeming to work.  Please see my acytual validation formula:
 IF(Daily_Consultation_Percentage__c + Daily_Deliverables_Percentage__c + Daily_Project_Out_Percentage__c + Daily_Staff_Aug_Percentage__c + Daily_Meeting_Contribution_Percentage__c > 100, True, False)

Am I missing anything? When I test it it still let's more than 100 be added. (percent).
kaustav goswamikaustav goswami
I am sorry for this over sight. While working with percent fields in formulas we should refer to the values in decimal format.

The actual validation rule should be something like this - 
 IF(Daily_Consultation_Percentage__c + Daily_Deliverables_Percentage__c + Daily_Project_Out_Percentage__c + Daily_Staff_Aug_Percentage__c + Daily_Meeting_Contribution_Percentage__c > 1, true, false)

Please let me know if there are further issues with this.

Thanks,
Kaustav
 
Tracy Oden 10Tracy Oden 10
Thank you! It worked like a charm.