NA=0 as custom format 1

[geagle]

I want to be able to enter NA in a cell and then later use this cell in a formula. So, I think I would like NA to be a number format of 0. If I just enter NA, Excel considers it text and I get an error when I use the cell in a formula. I do this so I can copy the formulas down. Sometimes there is a value in the cell and sometimes it isn't applicable (the person in a payroll spreadsheet isn't eligible for certain hours like sick)I use NA so that I know not to imput anything in that cell for that person as he/she is not eligible. However they might be eligible in the future, and if they are, all I would have to do is put in the proper value and the proper calculation would pop out. I know I can protect or lock the cells, but I'd rather have the visual NA reminder.

 

[luceze]

In your formula change the NA to a 0 using an if function.
For example:
=if(a1="NA",0,a1)
Of course you would have to include this within your other formula.

HTH,
Eric

 

[GlennUK]

You could have the cell formatted with a custom format ( menu command Format/Cells/Number/Custom ) of :

#,##0;-#,##0;"NA"

and enter a zero into the cell. This will show as "NA" when there is zero in the cell, but show the number when it is non-zero.

This means that you don't even have to change any of your fomulae.

Cheers, Glenn.

 

[geagle]

The reply from GlenUK for the custom format is what I was looking for. I tried this and think it will work for my payroll person. Thanks Glen!