7 Replies Latest reply: Nov 27, 2018 9:11 AM by Rene Tan

# Quiestion on formula for IF and And functions

I am a new user of Monarch.  Is there a way create this excel formula "=IF(AND(\$G2>\$AL\$1|\$AJ2="")|\$AL\$1-\$R2|IF(AND(\$G2<\$AL\$1|\$AJ2="")|\$AL\$1-\$G2|IF(\$AJ2=""|\$AL\$1-\$R2|0)))"  in Monarch?   Thank you

• ###### Re: Quiestion on formula for IF and And functions

Here is the literal translation of your excel formula to monarch:

One line version:

IF(\$G2>\$AL\$1 .and. \$AJ2="", \$AL\$1-\$R2, IF(\$G2<\$AL\$1 .and. \$AJ2="", \$AL\$1-\$G2, IF(\$AJ2="", \$AL\$1-\$R2, 0)))

Multi-line version:

IF(\$G2>\$AL\$1 .and. \$AJ2="",

\$AL\$1-\$R2,

IF(\$G2<\$AL\$1 .and. \$AJ2="",

\$AL\$1-\$G2,

IF(\$AJ2="",

\$AL\$1-\$R2,

0)

)

)

Since your excel version appears to reference cells on different rows, I don't think you are going to get the results you expect.  When building a formula field, you need to reference the field names (not cell addresses), and generally speaking you can only perform calculations between fields on the same row (you cannot look 1 row down or up).

• ###### Re: Quiestion on formula for IF and And functions

Thank you very much.  For your help, I appreciate it.

Regards,

Rene Tan

Accountant II

Great Lakes Credit Union

Accounting Department

Ph 847-578-7384

Fax 847-578-7036

NOTICE: Information contained in this transmission to the named addressee is proprietary information and is subject to privilege and work product confidentiality.  If the recipient of this transmission is not the named addressee, the recipient should immediately notify the sender and destroy the information transmitted without making any copy.

• ###### Re: Quiestion on formula for IF and And functions

I just wanted to add to Chris's post where he mentioned you cannot use another row. It looks like in your case you may have a workaround as you have \$AL\$1 so you are always looking up to AL1 no matter the row, if that is true, create a formula field and make it a constant.

For example if AL1 is a date, you can have the formula field as a hard coded date or use the date functions to create that specific date using Today() and DateAdjust() or end_of_current_month() or previous.

If its a number just type in the number.

Just an idea, not sure what is in AL1 but you might be able to have a work around even if you need to change the constant each time.

Hope you can get your formula to work.

Patrick

• ###### Re: Quiestion on formula for IF and And functions

Hi, Patrick.

I use constant value for previous end of month.  Do you a formula on how I can automate this process instead of manually entering the constant date value.

Regards,

Rene Tan

Accountant II

Great Lakes Credit Union

Accounting Department

Ph 847-578-7384

Fax 847-578-7036

NOTICE: Information contained in this transmission to the named addressee is proprietary information and is subject to privilege and work product confidentiality.  If the recipient of this transmission is not the named addressee, the recipient should immediately notify the sender and destroy the information transmitted without making any copy.

• ###### Re: Quiestion on formula for IF and And functions

create a new formula field and make the type Date/Time and format as you wish (you can hide this column if it is not needed to bee shown on the final report).

For the formula itself, I typically like using a date from the input report but not sure what your data has. If you have a current month date or previous month date always in your input I would use that as a basis, most of my reports have a post date or report run date so I have been lucky, this way if you open a report from a month or two ago you don't have issues calculating the prior month. If you have a current month date End_Of_Previous_Month(Date from report)  or if you have a prior month date End_Of_Current_Month(Date from report)

If you do not have anything you can use in the input report and you are always looking for the current day's previous month end date the use End_Of_Previous_Month(today())

Hope this makes sense and helps you,

Patrick

• ###### Re: Quiestion on formula for IF and And functions

Yes, the previous month end date.

Regards,

Rene Tan

Accountant II

Great Lakes Credit Union

Accounting Department

Ph 847-578-7384

Fax 847-578-7036