I need a formula that will look at one cell G7 and if it is a 1, 2,3, 4, 5,
6, 7, or a then I need it to display a 1.

The formula I am using is this:

=IF(G7="1","1",IF(G7="2","1",IF(G7="3","1",IF(G7=" 4","1",IF(G7="5","1",IF(G7="6","1",IF(G7="A","1"," 0")))))))

All the cells are formatted as general text. The formula is working when it
sees "a" but not any of the numbers? Any ideas on how I can make the whole
thing work?

You don't need all the IFs. You don't state what you want if G7 is not any
of 1 - 7, but try

=IF(TRUNC(G7)=G7,IF(AND(G7=1,G7<=7),1,FALSE),FALS E)


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting
www.cpearson.com
(email on the web site)


"mndpy" wrote in message
...
I need a formula that will look at one cell G7 and if it is a 1, 2,3, 4, 5,
6, 7, or a then I need it to display a 1.

The formula I am using is this:

=IF(G7="1","1",IF(G7="2","1",IF(G7="3","1",IF(G7=" 4","1",IF(G7="5","1",IF(G7="6","1",IF(G7="A","1"," 0")))))))

All the cells are formatted as general text. The formula is working when
it
sees "a" but not any of the numbers? Any ideas on how I can make the whole
thing work?

If it isn't one of those I'd like it to display 0. Any other suggestions?
Thanks!

"Chip Pearson" wrote:

You don't need all the IFs. You don't state what you want if G7 is not any
of 1 - 7, but try

=IF(TRUNC(G7)=G7,IF(AND(G7=1,G7<=7),1,FALSE),FALS E)


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting
www.cpearson.com
(email on the web site)


"mndpy" wrote in message
...
I need a formula that will look at one cell G7 and if it is a 1, 2,3, 4, 5,
6, 7, or a then I need it to display a 1.

The formula I am using is this:

=IF(G7="1","1",IF(G7="2","1",IF(G7="3","1",IF(G7=" 4","1",IF(G7="5","1",IF(G7="6","1",IF(G7="A","1"," 0")))))))

All the cells are formatted as general text. The formula is working when
it
sees "a" but not any of the numbers? Any ideas on how I can make the whole
thing work?

On Sat, 8 Sep 2007 14:38:01 -0700, mndpy
wrote:

I need a formula that will look at one cell G7 and if it is a 1, 2,3, 4, 5,
6, 7, or a then I need it to display a 1.

The formula I am using is this:

=IF(G7="1","1",IF(G7="2","1",IF(G7="3","1",IF(G7= "4","1",IF(G7="5","1",IF(G7="6","1",IF(G7="A","1", "0")))))))

All the cells are formatted as general text. The formula is working when it
sees "a" but not any of the numbers? Any ideas on how I can make the whole
thing work?


=--OR(G7={1,2,3,4,5,6,7})

This will display a 0 if G7 does not equal [1-7]
--ron

try this
at cell G7
formatcellnumber,text only,
and i guess your formula will give the sensitive text result "1" or "0".

"mndpy" wrote:

If it isn't one of those I'd like it to display 0. Any other suggestions?
Thanks!

"Chip Pearson" wrote:

You don't need all the IFs. You don't state what you want if G7 is not any
of 1 - 7, but try

=IF(TRUNC(G7)=G7,IF(AND(G7=1,G7<=7),1,FALSE),FALS E)


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting
www.cpearson.com
(email on the web site)


"mndpy" wrote in message
...
I need a formula that will look at one cell G7 and if it is a 1, 2,3, 4, 5,
6, 7, or a then I need it to display a 1.

The formula I am using is this:

=IF(G7="1","1",IF(G7="2","1",IF(G7="3","1",IF(G7=" 4","1",IF(G7="5","1",IF(G7="6","1",IF(G7="A","1"," 0")))))))

All the cells are formatted as general text. The formula is working when
it
sees "a" but not any of the numbers? Any ideas on how I can make the whole
thing work?

Hi,
I'd still use Chip's formula, you just need to change the two FALSE
statements to 0.

The first FALSE is returned if the value is not between 1 and 7, so replace
this with a 0 and the second FALSE is returned if the value is a fraction so
replace this with a 0 if you want fractions to of a number.

In addition you might want to check the value is a number, as opposed to
text so update the formula to:

=IF(ISNUMBER(G7),IF(TRUNC(G7)=G7,IF(AND(G7=1,G7<= 7),1,0),0),0)

or if you want fractions of a number between 1 and 7 to return 1 then update
the formula to:

=IF(ISNUMBER(G7),IF(AND(G7=1,G7<=7),1,0),0)

Hey guys this one works great but how do I add in the letters I am looking
for too? Like with this string I also need a 1 displayed if the letter a is
displayed?

=IF(ISNUMBER(G7),IF(TRUNC(G7)=G7,IF(AND(G7=1,G7<= 7),1,0),0),0)

Any suggestions would be great!

Turn the last 0 (i.e the FALSE part of the ISNUMBER function) into another IF
statement:

=IF(ISNUMBER(G7),IF(TRUNC(G7)=G7,IF(AND(G7=1,G7<= 7),1,0),0),IF(G7="A",1,0))

This will return 1 for both 'a' and 'A'.

If you want to check other for other characters change G7="A" into something
like
OR(G7="A",G7="B",G7="C")

"mndpy" wrote:

Hey guys this one works great but how do I add in the letters I am looking
for too? Like with this string I also need a 1 displayed if the letter a is
displayed?

=IF(ISNUMBER(G7),IF(TRUNC(G7)=G7,IF(AND(G7=1,G7<= 7),1,0),0),0)

Any suggestions would be great!

Thanks! THat worked great! I appreciate the help!

"Darren Bartrup" wrote:

Turn the last 0 (i.e the FALSE part of the ISNUMBER function) into another IF
statement:

=IF(ISNUMBER(G7),IF(TRUNC(G7)=G7,IF(AND(G7=1,G7<= 7),1,0),0),IF(G7="A",1,0))

This will return 1 for both 'a' and 'A'.

If you want to check other for other characters change G7="A" into something
like
OR(G7="A",G7="B",G7="C")

"mndpy" wrote:

Hey guys this one works great but how do I add in the letters I am looking
for too? Like with this string I also need a 1 displayed if the letter a is
displayed?

=IF(ISNUMBER(G7),IF(TRUNC(G7)=G7,IF(AND(G7=1,G7<= 7),1,0),0),0)

Any suggestions would be great!

On Tue, 11 Sep 2007 09:10:04 -0700, mndpy
wrote:

Hey guys this one works great but how do I add in the letters I am looking
for too? Like with this string I also need a 1 displayed if the letter a is
displayed?

=IF(ISNUMBER(G7),IF(TRUNC(G7)=G7,IF(AND(G7=1,G7< =7),1,0),0),0)

Any suggestions would be great!


=--OR(G7={1,2,3,4,5,6,7,"a"})


--ron