Div/0 error

[Vic Abrahamian]

Hi,
When I use the following formula
=(a1-a2)/ABS(a1)
Any time A1=0, I receive a devide by 0 error.
How can I change the formula to show 100% instead?
Thank you

 

[Rob Bovey]

Hi Vic,

Here's one way:

=IF(A1=0,1,(A1-A2)/ABS(A1))

--
Rob Bovey, Excel MVP
Application Professionals
http://www.appspro.com/

* Take your Excel development skills to the next level.
* Professional Excel Development
http://www.appspro.com/Books/Books.htm

 

[Vic Abrahamian]

HI,
Thank you, but when I change the 0 in cell a1 to another number the result
doesn't change.
I di

 

[Vic Abrahamian]

Than you when I change the 1 to 100% it shows a 100% as the result,
but if I change cell A1 to $5 instead of $0
The result still stays 100%
It doesn't change to the real result

 

[Rob Bovey]

Hi Vic,

It sounds like you may not have your workbook set to calculate
automatically. If you're using Excel 2003 or earlier, choose Tools > Options

Calculation from the Excel menu. Make sure the Automatic option in the

upper left corner is selected, then click OK and save your workbook.

--
Rob Bovey, Excel MVP
Application Professionals
http://www.appspro.com/

* Take your Excel development skills to the next level.
* Professional Excel Development
http://www.appspro.com/Books/Books.htm

 

[Robert Martim, Excel]

Vic

Did you try Rob's last suggestion? You can also try pressing F9 to recalcute
the worksheet (in case it is manual). Here, it works fine whichever way I
go... unless there is something else going on which you have not disclosed.

--
Robert
Author of RibbonX: Customizing the Office 2007 Ribbon:
LATEST INTERACTIVE TUTORIAL:
http://www.msofficegurus.com/videos/StepByStep_HT_Chart.asp
FORUM: http://www.msofficegurus.com/forum/

 

[Vic Abrahamian]

But the problem is some of the columns work and some don't in the same
worksheet.

 

[Vic Abrahamian]

The problemis some columns in the same worksheet work and some dont'. Some
calculate and some don't update and don't work

 

[Rob Bovey]

The problemis some columns in the same worksheet work and some dont'. Some
calculate and some don't update and don't work

Hi Vic,

Check the input cells for the formulas that don't update and make sure
they are not formatted as text. The Format > Cells > Number tab will have
the Text entry selected if this is the case.

--
Rob Bovey, Excel MVP
Application Professionals
http://www.appspro.com/

* Take your Excel development skills to the next level.
* Professional Excel Development
http://www.appspro.com/Books/Books.htm