Multiple IF formula needed....

[mndpy]

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?

 

[Chip Pearson]

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),FALSE)


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

 

[mndpy]

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

 

[Ron Rosenfeld]

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

 

[DumbLittleMan]

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

 

[Darren Bartrup]

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)

 

[mndpy]

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!

 

[Darren Bartrup]

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]

Thanks! THat worked great! I appreciate the help!

 

[Ron Rosenfeld]

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