With this standard format, a zero will appear as as 0:

#,##0;-#,##0

However, you can set zero to appear as "na" using the following
format:

#,##0;-#,##0;"na"

My question is whether it is possible to test whether the cell is
showing a 0 as "na", even though the underlying value is still a 0.
For example, I cannot use IF(A10="na",... because the cell A10
actually still contains a 0; it just looks like "na" because of the
formatting.

You'll need VBA to make the distinction.



--
Wigi
http://www.wimgielis.be = Excel/VBA, soccer and music


"jayray" wrote:

> With this standard format, a zero will appear as as 0:
>
> #,##0;-#,##0
>
> However, you can set zero to appear as "na" using the following
> format:
>
> #,##0;-#,##0;"na"
>
> My question is whether it is possible to test whether the cell is
> showing a 0 as "na", even though the underlying value is still a 0.
> For example, I cannot use IF(A10="na",... because the cell A10
> actually still contains a 0; it just looks like "na" because of the
> formatting.
>
>
>
>

Why can't you use if(a1=0 because as you correctly save that value displays
as 'na' so if a1=0 evatuates as TRUE then the cell is displaying na

Mike

"jayray" wrote:

> With this standard format, a zero will appear as as 0:
>
> #,##0;-#,##0
>
> However, you can set zero to appear as "na" using the following
> format:
>
> #,##0;-#,##0;"na"
>
> My question is whether it is possible to test whether the cell is
> showing a 0 as "na", even though the underlying value is still a 0.
> For example, I cannot use IF(A10="na",... because the cell A10
> actually still contains a 0; it just looks like "na" because of the
> formatting.
>
>
>
>