Converting the number 0

[Bob]

I am counting a lottery tickets and there are tickets with
the number 0 that need to be counted as 1. Is there a way
to convert the number 000 when entered in the cell, to
actually think that 000 is the number 1. I know very
little programing. Thanks.

 

[Anne Troy]

You'll be better off doing this in another formula (I assume you're using).

Something like =if(a1="000",1+a2,a1+a2)
That would make it a 1 and add it to a2.
If it's not 000, then it'll just add a1 and a2.

<-*-><-*-><-*-><-*-><-*-><-*-><-*-><-*->
Hope this helps!
Anne Troy (better known as Dreamboat)
Author: Dreamboat on Word
Email: Dreamboat*at*Piersontech.com
Web: www.TheOfficeExperts.com

 

[Norman Harker]

Hi Bob!

If counting the number of lottery tickets inclusive of the ticket
number 0:

Try:
=COUNTIF(A1:A6,">="&0)

Works as long as the data are numbers and not text.

I think that this will avoid the need for you to convert the number on
entry, which requires VBA.
--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.