5 Replies Latest reply: May 15, 2014 10:05 AM by Data Kruncher

# Create Nulls For Avg Calculation

I'm using Monarch V8.01 and looking to summarize avg gpa.  I made the following formula, but haven't figured out how to make it null if the condition is false.  When doing an Average GPA including 0 changes the totals.

The formula editor allowed this:

If(ACCEPTED=1,GPA,0)

However, what I would like is for the formula to be

If(ACCEPTED=1,GPA,null)

I can't really filter because I was hoping to be able to have the three averages (Applied GPA, Accepted GPA, Enrolled GPA) side by side in the summary window as the counts are displayed that way currently.

• ###### Create Nulls For Avg Calculation

You're really close to the solution.

What you need is:

If(ACCEPTED=1,GPA,1/0)

/CODE

The division by zero calculation forces a null value.

HTH,

Kruncher

• ###### Create Nulls For Avg Calculation

Thank you so much Data Kruncher for both the solution and the rapid response.

• ###### Create Nulls For Avg Calculation

You're welcome.

Adding your own custom Null() function to the library of existing custom functions that ship with Monarch is a great way to handle this type of problem.

Then the formula would be a bit more readable as:

If(ACCEPTED=1,GPA,Null())[/code]

• ###### Create Nulls For Avg Calculation

I did a quick search and found a reply you gave regarding custom functions and seems that perhaps this isn't available in version 8.01?

• ###### Create Nulls For Avg Calculation

Hmm. It seems to me that the ability to create your own "user defined functions" wasn't introduced until v9.

Sorry if I've already got you on a wild goose chase.