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.

August 26, 2015
·
Like
0
·
Follow
1

Best Answer chosen by Rung41

Shyama 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

August 26, 2015
·
Like
1
·
Dislike
0

Shyama 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

August 26, 2015
·
Like
0
·
Dislike
0

Rung41
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.

August 26, 2015
·
Like
0
·
Dislike
0

Shyama 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.

August 26, 2015
·
Like
0
·
Dislike
0

Rung41
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)

August 26, 2015
·
Like
0
·
Dislike
0

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

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.

August 26, 2015
·
Like
0
·
Dislike
0

Rung41
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.

August 26, 2015
·
Like
0
·
Dislike
0

Shyama 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.

August 26, 2015
·
Like
0
·
Dislike
0

Rung41
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.