Using IF THEN Calculations in FRx
You should know that IF THEN calculations are not only possible in FRx, you can use them in both the row and the column.
In the Row, use a CAL format code, then place your IF THEN statement in column D Related Rows.
In the column, use a CALC column type, then place your IF THEN statement in the Calc Formula cell.
The syntax is as follows:
If (true/false statement) Then (formula) Else (formula)
Don’t use commas. The ELSE segment is optional. In the row, row codes are used in the true/false statement and formula. For example, “If B200>0 Then B200 Else C215″. In the column, column letters are used instead: “If B>0 Then B Else C”.
These are simple examples but they can get much more complex.
I use an IF THEN most often in the row to switch intercompany receivables and payables between asset and liability sections depending on whether there’s a debit or credit balance.
Here’s another not-so-obvious trick with calculations in FRx. This can be used for various things, but I’ll use the example of multiplying by a currency rate.
Use IF @UNIT() calculations in the row format to define the rate for each unit, then just use a CALC column to multiply by your rate row. This is a bit harder to explain, but you basically end up with something like this in the row format:
MYRATE My exchange rate CAL 1.5678 “0.000000″ NP
NO_RATE No conversion CAL 1 NP
11 Company1 CAL IF @UNIT(COMPANY1) THEN @MYRATE ELSE @12 NP
12 Company2 CAL IF @UNIT(COMPANY2) THEN @MYRATE ELSE @13 NP
13 Company3 CAL IF @UNIT(COMPANY3) THEN @MYRATE ELSE @14 NP
14 Company4 CAL IF @UNIT(COMPANY4) THEN @MYRATE ELSE @15 NP
15 Company5 CAL IF @UNIT(COMPANY5) THEN @NO_RATE ELSE @16 NP
16 Company6 CAL IF @UNIT(COMPANY6) THEN @NO_RATE NP
You then have one GL column and one CALC column in the column layout. Your CALC column would use a formula of B*B11. When you generate the report, FRx will start at row 11 and then fall through each CAL row until it hits the right one.
You can combine this with XR and other functionality to do some pretty cool stuff. There are probably some good, creative uses of this without @UNIT(), too.
Folks, Jeff is one of the lead developers of FRx. Doesn’t get much better than this! Jan
I don’t get any of this please help!
I’d recommend you go to FRx Help and search for IF/THEN/ELSE. Review that, then come back here. Good luck.
Is there a way to get my report to only display lines that meet a certain IF criteria. For example I am trying to display only variances over $5000. I’ve used one column to calculate the absolute value for each variance, but I only want to see the rows that exceed 5000. Thank you in advance for any help.
Nm the last post I figured it out. Is there anyway to combine statements though? I need variances >10% of budget or >$5000. I’m able to get one column to display variances >5000 and another column to show variances>10% of budget but I’m wondering if there is a way to get one column to show values where either criteria is met. I am using “IF(ABS(E)>5000) THEN E” & in the next column “IF(abs(E/D)>0.1) THEN E”.
Does anyone know if you can do an: IF/THEN/IF/ THEN/ ELSE?
Simon,
You can’t do that in a single column, but you can accomplish the same type of thing by using multiple columns. For example:
A, DESC
B, GL
C, CALC, IF B>0 THEN D
D, CALC, IF B>1000 THEN 1000 ELSE B, NP
This is the same as having IF B>0 THEN IF B>1000 THEN 1000 ELSE B, you just have to break it out into multiple columns. You could even have an IF/THEN D ELSE E in column C, then have different IF/THEN/ELSE formulas in D and E … those might even refer to other columns with IF/THEN/ELSE formulas.
This also works in rows, but make sure your row and column formulas don’t collide since only one can win (defined by your report’s calculation priority).
Hope this helps.
Jeff
Hey thanks Jeff—I may have a use for that tomorrow!
I also do this in a row to make an ‘out of balance’ row calc to -0- so it doesn’t print due to rounding:
900 Out of balance TOT 830-800 NP
930 Out of balance CAL IF @900 < -4 or @900 >4 THEN @900 ELSE 0
Not exactly the same thing as a nested if/then but it works great in this instance. Jan
To add a couple tidbits to Jan’s post …
* You can reference individual rows in column formulas – for example, a CALC column could contain the formula IF B200 > 0 THEN C ELSE D. This can be useful to accomplish base row-like functionality, something like C/B100
* In row calculations, if you want to reference the value for a row in the current column, you use @ROW#. For example, if you want a row to show one row’s value divided by another and have that value be appropriate to each column, you might use IF @100>0 THEN @100 ELSE @200
* If you want to use a row with a text label in a calculation, you reference it with the column letter, a dot, and the row number. For example: B.ASSETS
I am trying to use the IF/THEN statement to create a p&L that shows the revenue and salaries for one department containg professional staff separately at the top of the report. Below that information is another salary row for departments for non-professional staff. I am using the @unit statement in the row format and if the unit contains is equal to the professional staff department then FRX branches to a row that contains the accounts in the LINK to GL column, including the department segment as DEP-????-ACCT-????-????. The Else statement is equal to “0″. That is not working because the summary p&L does not include the rollup. If I include an else statement that branches to list the natural accounts only (as ACCT????) minus DEP-????-ACCT-????-???? I get duplicate values. How can I fix that so only one department and the summary rollup contains the rows for the professional staff department and the non-professional staff departments show the correct amounts? I played around with the tree and row format and I can’t get that work either because the same natural account relating to non-professional staff also are used for professional staff and we need to report by department on the reporting tree. Please help! Thanks.
Thanks Jeff. I have an additional 5 columns but it works. Now one more thing, I am learning as I go with this system so bear with me. I have never used it. Well, I created a Statement of Cash Flow. Everything works fine except for the YTD column. I can not figure out how to get this to show the 2009 beginning balance for cash. I know how to generate the number but that’s all I can do. I don’t know how to carry it over the report where I want it. My current period cash is right.
I have been trying all day to get the ABS function to work in an IF CALC column. Here are the statements I have tried.
IF ABS(G-H)>2500 THEN G-H This is how it is in the book, but doesn’t work
IF (ABS(G-H))>2500 THEN G-H
IF ABS(G-H) > 2500 THEN G-H
IF ABS (G-H)>2500 THEN G-H
None work. What is the correct syntax, spacing, round or square brackets to get this working? If I can get the ABS to work, then hopefully, I will be able to build upon the variances to include % as well (Jeff’s 3/2009 posting)