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)
Awesome tips. I have a question though. Is it possible to use the row code or the account number in an “IF THEN ELSE” calculation in a column layout. Or is there any way to achieve this effect. I have a single column that displays percentages.
1. For each of the sales rows, % is calculated as a % of total sales. I can use CBR to accomplish this.
2. For COGS, % in each row is calculated as a fraction of the corresponding sales account.
For eg, widget COGS/Widget Sales, Tool COGS/Tool Sales and so on.
Basically, I am looking to change the column calculation based on the 1. Either the row code, or 2. the account number.
Put another way, can the IF statement be used to compare the value in the column with a string? Eg. IF B=”144401″ THEN B ELSE C.
Thanks
Technically you can combine a column letter with a row code (but not B=”144401″), but that’s not the best design in this instance.
What you can do is change the base row CBR several times in the row format so that you end up with multiple CBRs. So the first CBR references total sales, then when you get to COGS, add a CBR for widget sales right above widget COGS, then a CBR for tool sales right above tool COGS. And so on.
I did a report for a hotel once that had almost as many CBRs as there were rows. :)
BTW I usually style CBR rows as italics so the row is easier to follow. I do the same with other NP rows. (A CBR row doesn’t print by default.) Cheers…Jan
Awesome idea. Thank you very much. You just made my day. :-)
Happy to hear it!!!
Hello
I am trying to use SUMIF formula in FRx, but to no avail. Can we even use it in FRx…
Thanks in advance…
No…I wish! Jan
Great Thread! I was wondering if it was possible to reference the Base Period of a report in the row. I’d like to use the If/then statement to determine whether to pull a year over year growth based on a budget, or based on the actuals for a report that shows 24 months of data through dec 2011
Thanks! No re referencing the base period in the row, but you might try a search re ‘average headcount’ for some information on dividing by the period number.
I have a comparatve statement that was created in FRX. I want to Sbutract this years column from prior year column if the account number is >= 40000 and =”40000″ and b<= "49999" then c-e else e-c. Can this be done? It does not seem to be working.
The bad news is that what you’re trying can’t be done because the account number field is not a numeric field.
But I’m guessing you’re trying to flip signs in the revenue section. If so, the good news is that there’s a much easier way than if/then/else. See the 3 tricks are in the ‘budget variance’ section of my post on top 12 FRx tricks. Do a search (on this site) and you’ll see exactly what to do.
I’m attempting to add a few column’s to my income statement that will look at the actual vs. budget variance column, compare it to a variance threshold, then return a “yes” if the variance is exceeded.
IF ABS P>50000 THEN P ELSE 0
Thought this would be simple, but its not working out.
I also wanted to have a column that looked a % variance column and returned a “yes” if the % variance exceeded 10%.
IF ABS P>50000 THEN P ELSE 0
This doesn’t work either.
Do I need to make adjustments to my rows??
Any help would be much appreciated!
Don’t think you can return a “yes” but you can return the amount which is what you’re doing above. You might try this instead of the ‘abs’:
if p < -50000 or p > 50000 then p else 0
Hi There
I am using unit accounts in a report that has both MTD column and a calculation that adds up each period 1-12 to get a correct YTD number (exchange issues).
The issue I am having is that some units accounts are regular i.e. add up periods 1-12 for a total YTD unit while other unit accounts are non-cumulative (each month stands alone).
Can you think of a way to identify certain rows as non-cumulative and have the MTD figure appear in the YTD Calculation column?
Hope that makes sense.
Grant
It sounds an awful lot like you need to use column placement. There may be an easier way, but I’d have to see what you’re doing to tell for sure. Good luck!
Hi Jan
I am looking for a way to 2 columns MTD and YTD in dollars and beside It I would like to calculate a unit cost….for example
Column B=MTD$, Column C=YTD$, Column D=MTDUnit$, Column E=YTDUnit$.
I have been able to get the desired result by using column references on separate lines….line 100 shows values pulled from the GL in column B and C and line 130 shows calculated values in columns. I would like to combine the 2 rows into one but if I use a regular calculation it just combines the total cost and the unit cost together.
Any ideas?
Thanks
Grant
Can this calculation be used on rows that are linked to the General Ledger?
Or do I need to insert another row referencing the GL balance to use the IF THEN calculation and mark the row linked to the General Ledger not to print?
Thanks,
Lindsay
Yes you need a CAL row and you’ll use the @ symbol to reference the row codes.
I might sound to be out of the topic, is there anywhere on the web one can get free *.pdf guide of Frx Report Designer with formulas and guides on how to do some of the reports. I need to take the certification exam but I need like a strong material that i can use. Thank you
Hi Dandy…I have 2 self-study step by step training manuals that walk you through several reports, but they aren’t free:
https://www.frxbuzz.com/frx-training-manuals/
But given FRx’s future, not sure I’d spend time on it to get certified. In any event, good luck. Jan
Jan,
It’s me again. Love the reference guides but I am having a bit of a problem with using the if then else calculationf for my intercompany accounts. I have several intercompany accounts in each of my companies. I pulled the balances of each in a separate section in my row definitions and now I am trying to use the if then cal to pull the correct balances in the receivable/payable sections on my balance sheet. I get it to work for one company but can’t figure out how to add multiple if then statements on the same row. Is this possible? Or do I need to have separate rows that do not print or show on the balance sheet that sum up to the proper line?
Hi Patty…yes to the latter! Good luck…Jan
Great thread! Here’s another scenario I haven’t seen discussed. What about displaying or suppressing a row based on the date of a column. For example, a rolling P&L that uses a non-financial account value, but that value didn’t exist until a particular month. I didn’t see any discussion where the true/false statement tested on all columns in the Column Layout. Thanks for any feedback!
Hi Rob…unless I’m missing something, it doesn’t sound like you’d need an if/then because FRx picks up non-financial accounts just like it would a regular account: whatever value is there for a particular period is what gets presented. Cheers…Jan
Been reading up on if then else statements and I have a question. I have a column that is a variance against budget and another for last year. Let’s say A is actual, B is budget and C is variance. For D I want the percentage variance so I’ve used if B>0 then C/B else 0. Works great, problem is formatting in the rows is overriding the display zeros as blanks on the print out. Can I fix this or can I point the else statement to display N/A in text?
Hi
Great Frx site .I have a small problem in frx whereby i am trying to restrict zero balances .I have used account filters for departments and one department i am using opening balance as well as current transactions combined .i need to exclude transaction balances .Basically if the closing balance is zero , i dont want it to print .Is there a formula (If,Then ,Else statement) i can use to suppress this?
Thanks
Logan
Hi Logan…in Report Options > Formatting, make sure the box “Display rows with no amounts” is unchecked. This is the default setting, so that FRx doesn’t print an account when the calculated amount is zero.
I need to Calculate a CGR (but for months except of years), it seems that Management reporter 2.0 does not accept the ^ operator.
CGR = ((End Value/StartValue)^(1/BASE-1))-1
CGR = Compound Growth
Right…my recommendation would be export to excel :)
ok – here is what I’m trying to do.
I have a CALC column that totals dollar amounts in three other columns for various GL accounts. Right underneath the last dollar total, I have a row that needs to be a percent calculation instead of the dollars adding togehter (it’s basically Cost of Sales/Income) and the percent is in the same column as the dollars. Is there an IF/THEN/ELSE statement that I can use that says something like IF Columns A,B, and C are dollars format THEN add together ELSE Row#/Row# ??
Not sure if this is possible but wanted to throw it out there. Thanks!!
Hi Teri…you won’t need an if/then because there’s a better way, not that you could do an if/then on a format anyway.
What you have is a calculated column intersecting with a calculated row. FRx (or MR) needs to know which calculation wins.
Here’s what to do: in the Catalog, go to the Report Options tab, then to the Advanced tab. See the Calculation Priority section? Set it to ‘Calculate columns first’. What that really means is ‘calculate the column first, and then calculate the row, so the row wins’.
Bottom line, this will keep your percentages from crossfooting (which is what the column is telling it to do). Make the row win and you’re good to go. Cheers—Jan
Jan,
OK – I got the row formula to calculate first when viewing in FRx. Thank you! Now the issue is that when exporting to Excel and selecting to export formulas, the column formula exports instead of the row. How can I make the Row take precedence over the column when exporting?
Jan,
OK – I got the row formula to calculate first when viewing in FRx. Thank you! Now the issue is that when exporting to Excel and selecting to export formulas, the column formula exports instead of the row. How can I make the Row take precedence over the column when exporting?
Anyone want to tackle my last question? Thanks!!
Hi Jan,
I use a if then cal formula to calculate the deferred tax asset/liability. It worked fine when there’s a balance in total tax asset/liability. Last month we booked a valuation allowance and the tax balance is zero now. On the balance sheet, we still want one line to show the original tax amount and the other line show the valuation allowance. This create a problem that the same information appear both in deferred tax asset and liability section, even though the total is zero. Do you know how can I fix this? Thanks for answering.
Hi Jennifer…hard to tell without seeing it but you should scrutinize the formulas. Sounds like something is out of whack. Jan
Hi there,
This question is directed at those with a lot of experience with Conditional Column Formulas. I am trying to do what I think is a VERY SIMPLE conditional Calculation but I cannot get it to work right. No matter the syntax I use, my IF eval never seems to be correct and I always get my Else(false) Result.
What I am try to do is in the Column Layout, I want to take Column Z-AA if the Row number (column AB) is less than 251 or Greater than 1010 Else AA-Z
IF AB @1010 THEN Z-AA ELSE AA-Z
This is not a matter of flipping a sign for them as there are valid pos and neg numbers. It’s a matter of Actual – Budget and Budget – Actual in certain sections of the report.
I have tried multiple variants of the above formula with no success… I have even tried to eliminate the OR portion and just evaluate the 251, but it cannot seem to get it to recognize the row and evaluate where it is on the report to control the calculation it uses. I have also tried used the Acct or Attributes and control this on Column AB to no avail. The Row number is ideal as it has the most control of how this calculation is done.
Not sure what happened, but my formula got messed up when I pasted…
IF AB @1010 THEN Z-AA ELSE AA-Z
It did it again… There must be an issue with the page as it will not Post correctly as I have typed it. :( But my pseudo code is correct…
Hi Paulette…I seem to recall trying to get a row code recognized in a conditional statement in FRx, but I’m guessing I couldn’t get it to work. My approach would be exactly the same as yours: to try lots of variations of the above. Then recognize when it’s time to punt. Hoping other readers may have a different diagnosis; chime in! J
Is there a way to only display rows that meet a certain criteria? My report has columns for actual, budget and variance. I only want to display the rows whose variance is greater than 5000 and greater than 5%. Thanks for your help.
Hi Courtney…yes, here’s an example of an if/then in a row: IF @1120 < -4 OR @1120 > 4 THEN @1120 ELSE 0. You can use the same type syntax in the column, but in the column you just use the column letters. This syntax should work in either FRx or MR. Cheers…Jan
Jan, is there a way to do nested if/then formulas in columns?
Not exactly. You have to finagle it with multiple columns. I did it the other day with just 2 conditions. It wasn’t easy, but I did get it. Jan