You need to sign in to do that

Don't have an account?

Rung41

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

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.

Shyama B STry 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 SHi 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

Rung41The 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 SGlad 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.

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)

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

Rung41I 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 SCould 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. Rung41I 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.

Rung41-----

All fields populated - get total Only 3 fields populated - Total Blank

Shyama B STry 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

Rung41Bingo-Bango! That worked. Thanks so much for your help!!