Multiple If functions

M

Mark

What is the best way to put multiple if function's is single cell. I am trying to say is if(c5>=1,1,0),if(c5<=18,1,0),if(c5>=19,2,0),if(c5<=48,2,0). So if a value of c5 is between 1 and 18 enter a one in the deisgnated cell, but if c5 is between 19 and 48 then enter 2
 
L

Leo Heuser

Mark

Two ways:

=IF(AND(C5>=1,C5<=18),1,IF(AND(C5>=19,C5<=48),2,0))

or

=(C5>=1)*(C5<=18)*1+(C5>=19)*(C5<=48)*2

In the second example you don't bang into the limit
of 7 nested IF-functions.

--
Best Regards
Leo Heuser

Followup to newsgroup only please.

Mark said:
What is the best way to put multiple if function's is single cell. I am
Click to expand...
trying to say is if(c5>=1,1,0),if(c5<=18,1,0),if(c5>=19,2,0),if(c5<=48,2,0).
So if a value of c5 is between 1 and 18 enter a one in the deisgnated cell,
but if c5 is between 19 and 48 then enter 2
 
J

Jazzer

Hi,

Try something like this:

=IF(AND(C5>=1,C5<=18),1,IF(AND(C5>=19,C5<=48),2,0))

Other usefull functions are NOT and OR.

Problem with IF function is that, you can only nest seven of them. If
you need more, there are many ways to go around that problem. Just ask
here and someone will cladly help you.

- Asser
 
N

Norman Harker

Hi Mark!

One way:

=IF(AND(C5>=1,C5<=18),1,IF(AND(C5>18,C5<=48),2,""))

If C5 is outside your range 1-48 it defaults to ""

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
[email protected]
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
 
N

Norman Harker

Hi All!

Probably not relevant, but >18 <19?

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
[email protected]
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
 
H

Harlan Grove

Mark said:
What is the best way to put multiple if function's is single cell.
I am trying to say is if(c5>=1,1,0),if(c5<=18,1,0),if(c5>=19,2,0),
if(c5<=48,2,0). So if a value of c5 is between 1 and 18 enter a
one in the deisgnated cell, but if c5 is between 19 and 48 then
enter 2
Click to expand...

Yet another suggestion.

=IF(C5<1,"",IF(C5<=18,1,IF(C5<=48,2,"")))

which assumes you'd want C5 = 18.3 returning 2.
 
Top