6 Replies Latest reply on Jul 17, 2017 7:34 PM by BlancheMcdade

# I Need a Formula to Evulate Data With Different Results

Hi All,

I need a formula to calculate the following data with specific results.

Field 1 Has Subscription Periods

Field 2 Results from the status of Field 1 GL Codes

Subscription PeriodGL Codes
FIRST_YEAR-ANNUAL Anguilla1300ANG
FIRST_YEAR-ANNUAL TRS4440
FIRST_YEAR-ANNUAL Preco4420
FIRST_YEAR-ANNUAL4005CL1

In the subscription period field, I could have 13 different locations for first FIRST_YEAR-ANNUAL subscriptions, with each having their own G/L code.

Example:

FIRST_YEAR-ANNUAL Anguilla

FIRST_YEAR-ANNUAL Antigua

FIRST_YEAR-ANNUAL Bermuda

But where I am having the problem is making all the other records that don't have a specific GL code equal to 4005CL1.

I have tried using Case and If statements.

Thank you

• ###### 1. Re: I Need a Formula to Evulate Data With Different Results

I am assuming that each row is a different record.

Using Case:

Case (

Field 1 = "FIRST_YEAR-ANNUAL Anguilla" ; "1300ANG" ;

Field 1 = "FIRST_YEAR-ANNUAL TRS" ; "4440" ;

Field 1 = "FIRST_YEAR-ANNUAL Preco" ; "4420" ;

"4005CL1"

)

A better option would be to use the Looked Up Value option to look up these codes from a related table so that you can edit this list without having to redefine a calculation. There's an option to specify a value if there is no exact match where you can enter: 4005CL1.

• ###### 2. Re: I Need a Formula to Evulate Data With Different Results

You're on the right track with Case() statements, however the last part of a case() statement is what it returns if all other conditions are false. Like so:

Case(

If this is true ; Return this ;

If this other thing is true ; Return this other thing ;

Otherwise Return This

)

Note that the last line doesn't have an "IF" condition, it's just returned as the last parameter if any other condition above was not met.

So in your case, something like:

Case(

Subscription_Period = "FIRST_YEAR-ANNUAL Anguilla" ; "1300ANG" ;

Subscription_Period = "FIRST_YEAR-ANNUAL Preco" ; "4420" ;

etc.... for all others follow the same format:  If this ; Then result ;

"4005CL1"

)

When you use Case() that way, if none of your Subscription_Period values match the listed conditions, it will return 4005CL1 as the default result for things that do not match.

1 of 1 people found this helpful
• ###### 3. Re: I Need a Formula to Evulate Data With Different Results

Yes

• ###### 4. Re: I Need a Formula to Evulate Data With Different Results

There are 3 Case(...) so each of them is executed. Since PRECOA is found in the second and third Case, the value is changed twice in the order.

• ###### 5. Re: I Need a Formula to Evulate Data With Different Results

Don't use multiple case functions. Just use one as per my example.

I think that you figured this out, but I'm pointing this out to be sure.

• ###### 6. Re: I Need a Formula to Evulate Data With Different Results

thanks