| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Distinguishing 0 formatted as "na" and as 0
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. |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Distinguishing 0 formatted as "na" and as 0
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. |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Distinguishing 0 formatted as "na" and as 0
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. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Excel - Golf - how to display "-2" as "2 Under" or "4"as "+4" or "4 Over" in a calculation cell | Excel Discussion (Misc queries) | |||
| Can cells be formatted so that if A2 says "Joan", G2 says "$25" | Excel Worksheet Functions | |||
| now get message "disk is not formatted or formatted for Mac" | Excel Discussion (Misc queries) | |||
| In Excel a cell formatted "currency" shows "######" help! | Excel Worksheet Functions | |||
| cells formatted to tick when text value "Y" if or null if "N" | Excel Discussion (Misc queries) | |||
Linear Mode
