Formula to display "na"

[vbahelp07]

i have this formula:

column AE

=IF(AC399="na",0,IF(AC399=0,0,IF(AC399<I399,I399,(ROUND(AC399/I399,0)*I399))))

and
column AF

=IF(AE399="n/a","n/a",IF(AE399="na","na",(AE399*H399)))

but b/c you can't divide by 0 the result ends up being #DIV/0!

in this field i get "na" in column AG

=IF(I399=0,"na",SUM(AE399/I399))

what can i edit so that it'll show "na" as the result if it's not divided by 0?

Column AC is

=IF(AB399<=Z399,0,IF(AB399="na","na",(AB399-Z399)))

Column H and I sometimes has 0 and the issue with the divide by 0.

 

[zahead31]

You can have Excel check for calculation errors and display "na" if any error exists. This will catch the divide by zero errors.

=IF(ISERROR([i][blue]Your Formula[/blue][/i])=TRUE,"na",[i][blue]Your Formula[/blue][/i])

 

[vbahelp07]

Awesome! this worked great!

 

[Gavona]

Dependent on your purpose, rather than "na" you might want to use na().

Returns the error value #N/A. #N/A is the error value that means "no value is available." Use NA to mark empty cells. By entering #N/A in cells where you are missing information, you can avoid the problem of unintentionally including empty cells in your calculations. (When a formula refers to a cell containing #N/A, the formula returns the #N/A error value.)

Gavin