Further to the problem I posted earlier (listed at the end of this post), I have done as you indicated (and have curesed myself for not seeing the obvious) but have a new, more perplexing problem. After I enter and copy the formula down to all the appropriate cells and then either hit F9 to recalculate or the "Collapse Smartsheet" and "expand Smartsheet" icons to ensure my data is up to date, the formula in my variance columns are replaced in all cells (except the total lines) with a different formula. For example

the correct formual is

=IF(G44==0," ",(E44-G44)/G44)

As soon as recalculate the formula is changed to:

=ADGET("GLDATA.BY",$B44)

What the heck am I doing wrong now?

Thanks,

Ken

Member
Member # 405
posted August 24, 2004 03:07 PM
--------------------------------------------------------------------------------
I am using financial reporter (version 1.15.50.945) and am trying to add a simple column showing percentage variance to budget. Basically the formual is:
(Actual - budget)/Budget x 100.
The problem is if I have no budget i receive a Error! in the formula cell. Is there a way to return a blank cell or even a zero if a divide by zero error is received?

Thanks,

ken

--------------------------------------------------------------------------------
Posts: 41 | From: Chase, BC | Registered: Sep 2001 | IP: Logged

Softrak Support
Administrator
Member # 5
posted August 24, 2004 04:47 PM
--------------------------------------------------------------------------------
Hi Ken,
Use the following formula:

=if(Budget=0,0,(Actual-Budget)/Budget x 100)

For example,

Assume the cell reference for Budget=B1 and Actual=A1 then,

=if(B1==0,0,(A1-B1)/B1*100)

For an example of this type of formula, see GL!BudgetPercent.sam in sample data.

--------------------

Regards,
Softrak Tech Support

--------------------------------------------------------------------------------

Try entering the formula in the collapsed mode. Any row that is generated from expanding the smartsheet will copy the formulas from the collapsed row that generated it.

----------------
Don Wise
Westboro

Hi Ken,

To emphasis Don's comment.

When editing a financial statement, make all edits to rows and columns with the sheet in collapsed mode. Collapsing a sheet deletes all the rows with "** generated Row **" in column A and unhides the ".Range" rows. It is the ".Range" rows you should be editing.

Works Great!

Thanks,

Ken