returning zero on pct divided by zero

[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%

[Gary''s Student]

Anytime you have a potential divide by zero:

=A1/B1

you can use =IF(B1=0,0,A1/B1) to avoid the error
--
Gary''s Student


"dave99" wrote:

>
> 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%
>
>
> --
> dave99
> ------------------------------------------------------------------------
> dave99's Profile: http://www.excelforum.com/member.php...o&userid=30221
> View this thread: http://www.excelforum.com/showthread...hreadid=498993
>
>

[Jay Somerset]

On Sat, 7 Jan 2006 06:22:09 -0600, dave99
<[email protected]> wrote:

>
> 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%

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

--
Jay.
(remove dashes for legal email address)