#DIV/0
- Thread starter GN0001
- Start date
NARAYANK991
Excel Ninja
Hi ,
If you expect a formula to give you an error value , just change it to take care of this possibility , by :
=IFERROR(Original formula,0)
Narayan
If you expect a formula to give you an error value , just change it to take care of this possibility , by :
=IFERROR(Original formula,0)
Narayan
NARAYANK991
Excel Ninja
Hi ,
In today's scenario of powerful high-speed processors , buses and optimized applications , your worksheet would need to be extremely complicated / complex to slow down because of a thousand formulae.
You need to find out whether the inclusion of this will slow down the worksheet by a significant amount.
Narayan
In today's scenario of powerful high-speed processors , buses and optimized applications , your worksheet would need to be extremely complicated / complex to slow down because of a thousand formulae.
You need to find out whether the inclusion of this will slow down the worksheet by a significant amount.
Narayan
NARAYANK991
Excel Ninja
Hi ,
I am sorry but I cannot think of any possibility other than a VBA procedure , to make your of changing all the CF formulae easier.
However , as I understand it , both the cell formulae and the CF formulae need to be changed ; since the CF formulae will be based on the cell values , if the incorporation of the IFERROR functions in the formulae return 0 , then the CF formulae may not need to be changed. Have you tried changing one cell formula and seeing this ?
Narayan
I am sorry but I cannot think of any possibility other than a VBA procedure , to make your of changing all the CF formulae easier.
However , as I understand it , both the cell formulae and the CF formulae need to be changed ; since the CF formulae will be based on the cell values , if the incorporation of the IFERROR functions in the formulae return 0 , then the CF formulae may not need to be changed. Have you tried changing one cell formula and seeing this ?
Narayan
Naryan,
My formula is: =0/0 and then it gives me #DIV/0!, I used conditional formatting by entering: if the call value contains =0/0, then do this. I couldn't get the result I wanted.
I can use IFERROR, but I have to go into cells one by one and incorporate this formula, I am looking for easy way.
Thanks a lot for your help.
My formula is: =0/0 and then it gives me #DIV/0!, I used conditional formatting by entering: if the call value contains =0/0, then do this. I couldn't get the result I wanted.
I can use IFERROR, but I have to go into cells one by one and incorporate this formula, I am looking for easy way.
Thanks a lot for your help.
vijaySharma
Member
Guity,
This will not happen, what you are trying to achieve is to replace the value of the cell by 0 by using conditional formatting, however this is not the purpose of the feature.
It can help you to identify which cells contains error and when the condition (is there any error) is met you may highlight those cells.
However the original issue to replace the value with a ZERO can only be done as suggested by Narayan above,
There are two options at hand that can be looked at.
1. Update your formulas to include Error Handlers.
2. Make a copy of the raw data and then Copy Paste Values, and replace all #DIV!0 with 0 and then update your dashboard.
HTH
~VijaySharma
This will not happen, what you are trying to achieve is to replace the value of the cell by 0 by using conditional formatting, however this is not the purpose of the feature.
It can help you to identify which cells contains error and when the condition (is there any error) is met you may highlight those cells.
However the original issue to replace the value with a ZERO can only be done as suggested by Narayan above,
There are two options at hand that can be looked at.
1. Update your formulas to include Error Handlers.
2. Make a copy of the raw data and then Copy Paste Values, and replace all #DIV!0 with 0 and then update your dashboard.
HTH
~VijaySharma
I used the IFError suggested by Naryan and it worked, which is great and I am happy that I took DIV/0 away. I don't know how to Use Error Handlers, anyway.
As you say the Conditional Formatting should be able to do this:
"It can help you to identify which cells contains error and when the condition (is there any error) is met you may highlight those cells. "
I say if the cell contains =0/0, fill it with red color, This should work, but I don't know which part of that is not done properly, so it doesn't work for me.
Thank you for the help.
As you say the Conditional Formatting should be able to do this:
"It can help you to identify which cells contains error and when the condition (is there any error) is met you may highlight those cells. "
I say if the cell contains =0/0, fill it with red color, This should work, but I don't know which part of that is not done properly, so it doesn't work for me.
Thank you for the help.
vijaySharma
Member
Guity,
Error Handler are functions that will allow us to write a formula and also put an On Error Do This clause.
=IFERROR(1/0,"There was Division by Zero, sorry cannot do this")
Is an example of Error Handler... In previous versions of Excel, ISERROR was used to tackle these.
To get what you need, select the cell which contains the formula =0/0
Now go to Conditional Formatting, Manage Rules (Excel 2007).
Click on New Rule
Click on Use a forumula to determine which cells to format
Assuming you have put =0/0 in cell A1
Put this formula =iserror($A$1)
and then select the format
Click on Ok and Ok again to come out of CF.
now the cell A1 shoud have the color you selected in CF.
This will NOT remove the #DIV/0! from the cell (which I think is your original intent)
~VijaySharma
Error Handler are functions that will allow us to write a formula and also put an On Error Do This clause.
=IFERROR(1/0,"There was Division by Zero, sorry cannot do this")
Is an example of Error Handler... In previous versions of Excel, ISERROR was used to tackle these.
To get what you need, select the cell which contains the formula =0/0
Now go to Conditional Formatting, Manage Rules (Excel 2007).
Click on New Rule
Click on Use a forumula to determine which cells to format
Assuming you have put =0/0 in cell A1
Put this formula =iserror($A$1)
and then select the format
Click on Ok and Ok again to come out of CF.
now the cell A1 shoud have the color you selected in CF.
This will NOT remove the #DIV/0! from the cell (which I think is your original intent)
~VijaySharma