12 Replies Latest reply on Dec 7, 2018 5:15 PM by Toby Erkson

# Help with IF Statement to check balances

I need help to write an IF statement to calculate if the transaction amount equals either the quarterly, monthly, semi annual or annual prem then minus that amount and give a balance.  If it doesn't match either of those columns then put the transaction amount as a difference?

• ###### 1. Re: Help with IF Statement to check balances

Hi Nancy,

Please can you attach a sample workbook with sample data?

Thanks,

Mavis

• ###### 2. Re: Help with IF Statement to check balances

Thank you Mavis for helping.  Below is a sample of the data

Example Policy 12589 they paid \$5 which matches to the Monthly Prem so the final column should give a zero.  125891 it matches to the Qterly prem then final column should give 0 Policy 125892 matches the Annual Prem and final column should give 0 like that.  Hope that makes sense.  Let me know.

Thanks again

Policy #

Transaction Amount

Monthly Prem

Qtrly Prem

Semi Annual Prem

Annual Prem

125890

(\$5.00)

\$5.00

\$15.00

\$20.00

\$200.00

125891

(\$10.00)

\$2.00

\$10.00

\$25.00

\$50.00

125892

(\$1,050.00)

\$55.00

\$75.00

\$275.00

\$1,050.00

125893

(\$89.00)

\$25.00

\$35.00

\$89.00

\$250.00

• ###### 3. Re: Help with IF Statement to check balances

Thank you Mavis

Example Policy 12589 they paid \$5 which matches to the Monthly
Prem so the final column should give a zero.  125891 it matches to the
Qterly prem then final column should give 0 Policy 125892 matches the Annual
Prem and final column should give 0 like that.  Hope that makes
sense.  Let me know.  If the transaction amount doesn't match any column then the final amount will reflect that amount.

 Policy # Transaction  Amount Monthly Prem Qtrly Prem Semi Annual Prem Annual Prem 125890 (\$5.00) \$5.00 \$15.00 \$20.00 \$200.00 125891 (\$10.00) \$2.00 \$10.00 \$25.00 \$50.00 125892 (\$1,050.00) \$55.00 \$75.00 \$275.00 \$1,050.00 125893 (\$89.00) \$25.00 \$35.00 \$89.00 \$250.00
• ###### 4. Re: Help with IF Statement to check balances

Hi Nancy,

Please see the below calculation:

if SUM([Transaction Amount])*-1

= SUM([Monthly Prem])

then 0

elseif

SUM([Transaction Amount])*-1

= SUM([Qtrly Prem])

then 0

elseif

SUM([Transaction Amount])*-1

= SUM([Semi Annual Prem])

then 0

elseif

SUM([Transaction Amount])*-1

= SUM([Annual Prem])

then 0

else SUM([Transaction Amount])

END

I've attached the workbook with this example.

Thanks,

Mavis

• ###### 5. Re: Help with IF Statement to check balances

Thank you so much Mavis.  That really helped.

• ###### 6. Re: Help with IF Statement to check balances

Hi Nancy,

Sure no problem. If my previous answer has answered your question, please go to the main thread of your question and mark my answer as correct. This will update the status of this question and will let other users know which answer was correct (if they also have a similar question).

Thanks,

Mavis

• ###### 7. Re: Help with IF Statement to check balances

I tried but it doesn’t have that option.  I tried even clicking at Actions it doesn’t have that option.

1 of 1 people found this helpful
• ###### 8. Re: Help with IF Statement to check balances

Hi Nancy,

Have you opened up this thread in the main view : Help with IF Statement to check balances

Thanks,

Mavis

• ###### 9. Re: Help with IF Statement to check balances

This is greyed out I am unable to choose Answered.

• ###### 10. Re: Help with IF Statement to check balances

Hi Nancy,

You have to go onto my answer to find the answer option.

Thanks,

Mavis