Simple error handling question !

lharcus · May 17th, 2002, 06:01 AM

I have a simple spreadsheet function which is dividing the value in one cell by another and displaying it as a percentage.

This works fine except when the two values are zero - I get a #DIV/0! message.

How can I get this situation to display zero percent, yet allow the correct display of other results.

Thank you in advance

Mudface · May 17th, 2002, 06:13 AM

If you are dividing A1 by B1, use the following formula (adjust to your actual cells): -

=IF(B1=0,0,A1/B1)

Hope that helps.

Ian Mac · May 17th, 2002, 06:19 AM

Quote:

On 2002-05-17 05:01, lharcus wrote:
I have a simple spreadsheet function which is dividing the value in one cell by another and displaying it as a percentage.

This works fine except when the two values are zero - I get a #DIV/0! message.

How can I get this situation to display zero percent, yet allow the correct display of other results.

Thank you in advance

I see where your coming from BUT 0/0 is a divide by 0 error. (try it on a calculator)

Consider the following

33 divided by 0 can't be done, an number won't divide by 0
But 0 divided by 33 = 0.

the second answer would be 0 because it's a valid sum.

so, going back to your original question if you display 0% for two 0's you would be giving incorrect infomation:

=if(iserr(A1),0%,B1/A1) would be the formula BUT as said would be wrong (it's like saying =if(iserr(A1),1000%,B1/A1), the answer is simply wrong)

you need to use something like:

=if(A1,B1/A1,"")

which will return a blank if the number in A1 = 0 or if it is blank.

Sorry if I'm rambling, but hope this helps.

_________________
Share the wealth!!
Ian Mac

[ This Message was edited by: Ian Mac on 2002-05-17 05:20 ]

[ This Message was edited by: Ian Mac on 2002-05-17 05:21 ]

Ian Mac · May 17th, 2002, 06:22 AM

Quote:

On 2002-05-17 05:13, Mudface wrote:
If you are dividing A1 by B1, use the following formula (adjust to your actual cells): -

=IF(B1=0,0,A1/B1)

see my answer

Hope that helps.

Mudface · May 17th, 2002, 06:24 AM

Yeah, I appreciate what you're saying Ian, but I just answered according to what was asked

.

lharcus · May 17th, 2002, 06:45 AM

Thanks both the solution works.

Ian Mac · May 17th, 2002, 07:01 AM

Pressed the button twice
_________________
Share the wealth!!
Ian Mac

[ This Message was edited by: Ian Mac on 2002-05-17 06:02 ]

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

May 17th, 2002, 06:01 AM	#1
lharcus New Member Join Date: May 2002 Posts: 4	I have a simple spreadsheet function which is dividing the value in one cell by another and displaying it as a percentage. This works fine except when the two values are zero - I get a #DIV/0! message. How can I get this situation to display zero percent, yet allow the correct display of other results. Thank you in advance

May 17th, 2002, 06:13 AM	#2
Mudface MrExcel MVP Join Date: Feb 2002 Location: Sunny, spring-like Hull Posts: 3,339	If you are dividing A1 by B1, use the following formula (adjust to your actual cells): - =IF(B1=0,0,A1/B1) Hope that helps.

May 17th, 2002, 06:24 AM	#5
Mudface MrExcel MVP Join Date: Feb 2002 Location: Sunny, spring-like Hull Posts: 3,339	Yeah, I appreciate what you're saying Ian, but I just answered according to what was asked .

May 17th, 2002, 06:45 AM	#6
lharcus New Member Join Date: May 2002 Posts: 4	Thanks both the solution works.

May 17th, 2002, 07:01 AM	#7
Ian Mac MrExcel MVP Join Date: Feb 2002 Location: Newcastle, UK Posts: 1,174	Pressed the button twice _________________ Share the wealth!! Ian Mac [ This Message was edited by: Ian Mac on 2002-05-17 06:02 ]