returning zero on pct divided by zero

  • Thread starter Thread starter dave99
  • Start date Start date
D

dave99

formatting goes to pot here so I cant represent the tables as I'd like,
but hopefully you get the gist! User entry actual figure is only
entered obviously when there is a figure so alot of the time will be
blank or zero the fixed value target is always on the sheet and the
percentage is basically how much percent of the target has been
acheived which is a pct value.

Is there anyway of substituting a zero in when you have a pct divided
by zero as normally it just comes up with the error message

ie

USER ENTRY ACTUAL FIGURE FIXED VALUE TARGET PERCENTAGE
5 B1/A1%

There is no user entry until a value needs to be input and until that
point the pct always shows up with the error message, I'd prefer a
zero, cosmetics I know but would be curious to know.

Currently get:

USER ENTRY ACTUAL FIGURE FIXED VALUE TARGET PERCENTAGE
5
NULLyadayadacannotdividebyzeroerrormsg

Would prefer:
USER ENTRY ACTUAL FIGURE FIXED VALUE TARGET PERCENTAGE
5
0%

Obviously as soon as a user entry does appear then the formula should
alter as usual and display standard percentage

USER ENTRY ACTUAL FIGURE FIXED VALUE TARGET PERCENTAGE
5 5 100%
 
Anytime you have a potential divide by zero:

=A1/B1

you can use =IF(B1=0,0,A1/B1) to avoid the error
 
formatting goes to pot here so I cant represent the tables as I'd like,
but hopefully you get the gist! User entry actual figure is only
entered obviously when there is a figure so alot of the time will be
blank or zero the fixed value target is always on the sheet and the
percentage is basically how much percent of the target has been
acheived which is a pct value.

Is there anyway of substituting a zero in when you have a pct divided
by zero as normally it just comes up with the error message

ie

USER ENTRY ACTUAL FIGURE FIXED VALUE TARGET PERCENTAGE
5 B1/A1%

There is no user entry until a value needs to be input and until that
point the pct always shows up with the error message, I'd prefer a
zero, cosmetics I know but would be curious to know.

Currently get:

USER ENTRY ACTUAL FIGURE FIXED VALUE TARGET PERCENTAGE
5
NULLyadayadacannotdividebyzeroerrormsg

Would prefer:
USER ENTRY ACTUAL FIGURE FIXED VALUE TARGET PERCENTAGE
5
0%

Obviously as soon as a user entry does appear then the formula should
alter as usual and display standard percentage

USER ENTRY ACTUAL FIGURE FIXED VALUE TARGET PERCENTAGE
5 5 100%
Click to expand...

Try: =IF(ISNUMBER(A1),A1/B1, 0) or =IF(ISNUMBER(A1),A1/B1,"") to get
either 0 or a blank in the result field.
 
You must log in or register to reply here.

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Dividing - Negative Number by Zero 5
divide by zero 1
Average Forumula 4
Divide by zero error 3
Dividing by zero 2
Percentage with a zero involved 8
#DIV/O! help to show zero. 2
Hide the decimal "." point in chart 0

Back
Top