Multiple If functions

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
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
 
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
 
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
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
 
Hi All!

Probably not relevant, but >18 <19?

--
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.
 
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.
 
Back
Top