|
returning zero on pct divided by zero
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 |
returning zero on pct divided by zero
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 |
returning zero on pct divided by zero
On Sat, 7 Jan 2006 06:22:09 -0600, 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% 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) |
| All times are GMT +1. The time now is 06:25 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com