+ Start a Discussion
Rung41Rung41 

Help with field formula

I have the current field formula in place  - (Score_of_A+Score_of_B+Score_of_C+ Score_of_D) / 4
What I am looking to do is modify the formula to divide by the number of fields that are actually populated. For example
If 3 out of the 4 fields are populated divide by 3
If 2 out of the 4 fields are populated divide by 2
If 1 our of the 4 fields are poulated divide by 1
I'm not even sure where to start. Any suggestions would be appreciated.
Best Answer chosen by Rung41
Shyama B SShyama B S
Try this too. This works! :)

(IF(ISBLANK((Score_of_A__c)), 0, Score_of_A__c) + IF(ISBLANK((Score_of_B__c)), 0, Score_of_B__c) + IF(ISBLANK((Score_of_C__c)), 0, Score_of_C__c) + IF(ISBLANK((Score_of_D__c)), 0, Score_of_D__c)) /(IF(ISBLANK((Score_of_A__c)), 0, 1) + IF(ISBLANK((Score_of_B__c)), 0, 1) + IF(ISBLANK((Score_of_C__c)), 0, 1) + IF(ISBLANK((Score_of_D__c)), 0, 1))

I missed the blank check of values in the numerater in the previous formula. Score_of_A__c, Score_of_B__c, Score_of_C__c and Score_of_D__c are all numeric fields and we defined the formula to "Treat blank fields as blank".  So in previous formula, when one of the field is empty the numberator add will not work as one of them is BLANK(not a numeric value).

Hope this helps.

thanks
Shyama

All Answers

Shyama B SShyama B S
Hi Rung,

Try this:
(Score_of_A+Score_of_B+Score_of_C+ Score_of_D) /( IF(ISBLANK(Score_of_A), 0, 1) + IF(ISBLANK(Score_of_B), 0, 1)+IF(ISBLANK(Score_of_C), 0, 1)+IF(ISBLANK(Score_of_D), 0, 1))

Thanks,
Shyama
Rung41Rung41
The formla went in with no issues. I'll test it and let you know. Thanks for you assistance.
I would love to understand the logic of this formula and why it does what it does.
Shyama B SShyama B S
Glad to help.
The IF function has the below syntax:
                    IF( expression,value if true, value if false)
So if the field is populated, the function ISBLANK(field) becomes false and returns the value 0 and if not populated, returns 1. The whole expression:
                    IF(ISBLANK(Score_of_C), 0, 1)
becomes 0 if the field is Blank and 1 if it is populated. Adding the IF functions subsequently gives the desired divisor.
Please mark the answer as the Best answer if it solved the problem.

Thanks.
Rung41Rung41

Shyama,
 Tested out the logic and came across the following issue.


If 3 out of the 4 fields are populated, the formula is dividing by 4 (should divide by 3)

If 2 out of the 4 fields are populated, the formula is dividing by 4 (should divide by 2)

If 1 out of the 4 fields are populated, the formula is dividing by 4 (should divide by 1)

Shyama B SShyama B S
Can you please make sure that the formula field has the 'Treat blank fields as blank' selected?User-added image

This comes right after the area where we enter the formula and before save. 
The formula won't work if 'Treat blank fields as zero' is selected.
Thanks.
Rung41Rung41

I made the change you suggested. Now no value is returned if one of the fields isn't populated. However, if all fields are populated, the formula works correctly.

Shyama B SShyama B S
Could you please make sure that the parenthesis is proper as mentioned in my above comment. I tried the same formula and I was able to retrieve the correct result in all the conditions. If all the fields are going to have numeric values, you can try ISNULL instead of ISBLANK.
Rung41Rung41
I double checked and everything looks right. 
( Score_of_A__c+Score_of_B__c+Score_of_C__c+ Score_of_D__c) /( IF(ISBLANK(Score_of_A__c), 0, 1) + IF(ISBLANK(Score_of_B__c), 0, 1)+IF(ISBLANK(Score_of_C__c), 0, 1)+IF(ISBLANK(Score_of_D__c), 0, 1))  - Did I miss something?

I tired ISNULL and got the same results.

Thanks for all your help on this. I really appreciate your time.
Rung41Rung41

All fields populated -----Not all field populated
All fields populated  - get total                            Only 3 fields populated - Total Blank

Shyama B SShyama B S
Try this too. This works! :)

(IF(ISBLANK((Score_of_A__c)), 0, Score_of_A__c) + IF(ISBLANK((Score_of_B__c)), 0, Score_of_B__c) + IF(ISBLANK((Score_of_C__c)), 0, Score_of_C__c) + IF(ISBLANK((Score_of_D__c)), 0, Score_of_D__c)) /(IF(ISBLANK((Score_of_A__c)), 0, 1) + IF(ISBLANK((Score_of_B__c)), 0, 1) + IF(ISBLANK((Score_of_C__c)), 0, 1) + IF(ISBLANK((Score_of_D__c)), 0, 1))

I missed the blank check of values in the numerater in the previous formula. Score_of_A__c, Score_of_B__c, Score_of_C__c and Score_of_D__c are all numeric fields and we defined the formula to "Treat blank fields as blank".  So in previous formula, when one of the field is empty the numberator add will not work as one of them is BLANK(not a numeric value).

Hope this helps.

thanks
Shyama
This was selected as the best answer
Rung41Rung41
Bingo-Bango! That worked. Thanks so much for your help!!