Simple calc problem

Hi--

I have three cells in the P&L template I got from MS. The first is prior
period, then current period, then % change over prior period.

The last period had 0 for the amount, the current 250. The formula for the
calc is:

=IF(D1=0,"-",D1/B1-1)

Meaning, if current is not empty, then divide prior by current -1

So I'm getting a #DIV/0 error. But these are perfectly reasonable figures. I
can't enter more than 0 for the prior period if that's what it is. And this
is MS's own template. Is there some way to fix this? For example, can I add
a nested IF in the second value?

Thanks.

Looking at the P & L template from this site

http://office.microsoft.com/en-us/templates/TC011154841033.aspx?pid=CT...1727610

I would say make this change in F12 to prevent the error unless B12 has a 1

=IF($B12="","-",$D12/(B12-1))

Further..........to prevent the error if B12 is blank or 1

=IF(OR($B12="",$B12=1),"-",$D12/(B12-1))

Copy across to G12

Copy to any other cells in F and G where warranted.


Gord Dibben MS Excel MVP

On Wed, 20 Feb 2008 09:00:40 -0800, Jeffrey Ellis <rlinsurf DeleteThis @nospam.net> wrote:

>Hi--
>
>I have three cells in the P&L template I got from MS. The first is prior
>period, then current period, then % change over prior period.
>
>The last period had 0 for the amount, the current 250. The formula for the
>calc is:
>
>=IF(D1=0,"-",D1/B1-1)
>
>Meaning, if current is not empty, then divide prior by current -1
>
>So I'm getting a #DIV/0 error. But these are perfectly reasonable figures. I
>can't enter more than 0 for the prior period if that's what it is. And this
>is MS's own template. Is there some way to fix this? For example, can I add
>a nested IF in the second value?
>
>Thanks.