Printing column for Actual, Budget, Variance and Variance %. Using a simple formula to compute the variance % as Variance column/Budget column. However, when Budget = 0, MR prints the variance as 0% (because the variance $/Budget $ = 0 when Budget = 0), though it should be 100% or (100%) depending on if the variance is positive or negative to income. Tried a formula but unable to get it to work. Is there a standard approach to handling this?
*This post is locked for comments
Glad you got it to work! I appreciate you updating us all with the final solution for the formula. Please mark your final response as the Answer to the question to help others with similar issues in finding a working solution.
My bad, where D is the Variance $ column
This is what I finally got to work: IF C <> 0 THEN D/C ELSE IF D > 0 THEN 1 ELSE IF D < 0 THEN -1 ELSE 0, where C is the $ variance column.
GRG,
Reminds me of a Favorable/(Unfavorable) dodge I tried at my last job. We wanted the comparison of REVENUE amounts between this year and last year to show FAVORABLE when current year revenue was greater than last year; and UNfavorable when current year revenue was less than last year. Also wanted the comparison of EXPENSE amounts between this year and last year to show UNfavorable when current year expenses were greater than last year; and FAVORABLE when current year expenses were less than last year.
This was made a trifle easier because we arranged the Current Year amounts in rows directly above Last Year amounts:
REVENUE Category 1
Current Year 55,000
Last Year 50,000
$ Variance 5,000
% Variance 10%
REVENUE Category 2
Current Year 15,000
Last Year 25,000
$ Variance (10,000)
% Variance (40%)
EXPENSE Category 1
Current Year 25,000
Last Year 20,000
$ Variance (5,000)
% Variance (25%)
EXPENSE Category 2
Current Year 5,000
Last Year 20,000
$ Variance 15,000
% Variance 75%
The SUM rows for $ Variance were computed differently for REVENUE categories vs. EXPENSE categories:
REVENUE current_year_amount - last_year_amount
EXPENSE last_year_amount - current_year_amount
The CAL % Variance rows were all calculated this way:
IF last_year_amount < 0 THEN -(dollar_variance/last_year_amount) ELSE dollar_variance/last_year_amount
Maybe this sort of computation would work for you... though working in Columns vs. Rows could be trickier.
Sincerely,
Steve Erbach
Green Bay, WI
Well, I just entered it again, and got MR to save. Now I just need to add another ELSE to deal with the Variance = 0 scenario with another ELSE path
Tried that exact IF, but when saving the column format, MR shows an error and won't allow me to save. That's the core, issue, can't do nested IFs or ELSE IFs
You can always tweak the formula, that's the least of the issues:
(IF Budget_Col <> 0 THEN Variance_Col / Budget_Col ELSE IF Variance_Col < 0 THEN -1.00 ELSE 1.00)
Yes, works fine, but appears that the IF THEN ELSE statement forces a value of 1 without regard for sign.
Have you set the proper control at the row level for Credits vs Debits?
Had already tried both of those. With or without XCR in the column (which does exist in the Variance $ column and works), the 100% is always positive. The Calculations Priority in Report Settings is already set to perform row calculation first.
#1
André Arnaud de Cal...
291,711
Super User 2024 Season 2
#2
Martin Dráb
230,458
Most Valuable Professional
#3
nmaenpaa
101,156
Share
Report
All responses (
Answers (
