ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Worksheet Functions (https://www.excelbanter.com/excel-worksheet-functions/)
-   -   IF function (https://www.excelbanter.com/excel-worksheet-functions/178624-if-function.html)

JP

IF function
 
Is it possible to put multiple IF functions to one cell? For examples, say I
want to assign different descriptions depending on what value shows up in a
cell.

T. Valko

IF function
 
You can use something like this if you have a "few" conditions:

=IF(A1=90,"Excellent",IF(A1=80,"Good",IF(A1=70, "Average","Need
Improvement")))

If you have more than a "few" conditions then your best approach is to
create a 2 column lookup table. Post back if that might be the case.


--
Biff
Microsoft Excel MVP


"JP" wrote in message
...
Is it possible to put multiple IF functions to one cell? For examples, say
I
want to assign different descriptions depending on what value shows up in
a
cell.




JP

IF function
 
Yes, I need about 8 functions....I tried the equation below and it excepted
the formula, but it wouldn't change the answer for each variable, it just
posted the first.

"T. Valko" wrote:

You can use something like this if you have a "few" conditions:

=IF(A1=90,"Excellent",IF(A1=80,"Good",IF(A1=70, "Average","Need
Improvement")))

If you have more than a "few" conditions then your best approach is to
create a 2 column lookup table. Post back if that might be the case.


--
Biff
Microsoft Excel MVP


"JP" wrote in message
...
Is it possible to put multiple IF functions to one cell? For examples, say
I
want to assign different descriptions depending on what value shows up in
a
cell.





JP

IF function
 
Another factor is a range...per your example:
=IF(95<A190,"Good",IF(90<A185,"Okay")) Total of 7.....

Now knowing all the details, can this be done?



"JP" wrote:

Yes, I need about 8 functions....I tried the equation below and it excepted
the formula, but it wouldn't change the answer for each variable, it just
posted the first.

"T. Valko" wrote:

You can use something like this if you have a "few" conditions:

=IF(A1=90,"Excellent",IF(A1=80,"Good",IF(A1=70, "Average","Need
Improvement")))

If you have more than a "few" conditions then your best approach is to
create a 2 column lookup table. Post back if that might be the case.


--
Biff
Microsoft Excel MVP


"JP" wrote in message
...
Is it possible to put multiple IF functions to one cell? For examples, say
I
want to assign different descriptions depending on what value shows up in
a
cell.





T. Valko

IF function
 
Create a 2 column table like this:

...........F..........G.....
1........0..........Poor
2........40........Below Average
3........50........Average
4........60........Above Average
5........70........Good
6........80........Very Good
7........90........Outstanding

A1 = some number

=IF(A1="","",VLOOKUP(A1,F1:G7,2))

0 - 39 = Poor
40 - 49 = Below Average
50 - 59 = Average
60 - 69 = Above Average
70 - 79 = Good
80 - 89 = Very Good
90+ = Outstanding


--
Biff
Microsoft Excel MVP


"JP" wrote in message
...
Another factor is a range...per your example:
=IF(95<A190,"Good",IF(90<A185,"Okay")) Total of 7.....

Now knowing all the details, can this be done?



"JP" wrote:

Yes, I need about 8 functions....I tried the equation below and it
excepted
the formula, but it wouldn't change the answer for each variable, it just
posted the first.

"T. Valko" wrote:

You can use something like this if you have a "few" conditions:

=IF(A1=90,"Excellent",IF(A1=80,"Good",IF(A1=70, "Average","Need
Improvement")))

If you have more than a "few" conditions then your best approach is to
create a 2 column lookup table. Post back if that might be the case.


--
Biff
Microsoft Excel MVP


"JP" wrote in message
...
Is it possible to put multiple IF functions to one cell? For
examples, say
I
want to assign different descriptions depending on what value shows
up in
a
cell.






JP

IF function
 
Where do I create the table? On a seperate sheet?

"T. Valko" wrote:

Create a 2 column table like this:

...........F..........G.....
1........0..........Poor
2........40........Below Average
3........50........Average
4........60........Above Average
5........70........Good
6........80........Very Good
7........90........Outstanding

A1 = some number

=IF(A1="","",VLOOKUP(A1,F1:G7,2))

0 - 39 = Poor
40 - 49 = Below Average
50 - 59 = Average
60 - 69 = Above Average
70 - 79 = Good
80 - 89 = Very Good
90+ = Outstanding


--
Biff
Microsoft Excel MVP


"JP" wrote in message
...
Another factor is a range...per your example:
=IF(95<A190,"Good",IF(90<A185,"Okay")) Total of 7.....

Now knowing all the details, can this be done?



"JP" wrote:

Yes, I need about 8 functions....I tried the equation below and it
excepted
the formula, but it wouldn't change the answer for each variable, it just
posted the first.

"T. Valko" wrote:

You can use something like this if you have a "few" conditions:

=IF(A1=90,"Excellent",IF(A1=80,"Good",IF(A1=70, "Average","Need
Improvement")))

If you have more than a "few" conditions then your best approach is to
create a 2 column lookup table. Post back if that might be the case.


--
Biff
Microsoft Excel MVP


"JP" wrote in message
...
Is it possible to put multiple IF functions to one cell? For
examples, say
I
want to assign different descriptions depending on what value shows
up in
a
cell.







JP

IF function
 
Ok did that, but gives me "#N/A"

"JP" wrote:

Where do I create the table? On a seperate sheet?

"T. Valko" wrote:

Create a 2 column table like this:

...........F..........G.....
1........0..........Poor
2........40........Below Average
3........50........Average
4........60........Above Average
5........70........Good
6........80........Very Good
7........90........Outstanding

A1 = some number

=IF(A1="","",VLOOKUP(A1,F1:G7,2))

0 - 39 = Poor
40 - 49 = Below Average
50 - 59 = Average
60 - 69 = Above Average
70 - 79 = Good
80 - 89 = Very Good
90+ = Outstanding


--
Biff
Microsoft Excel MVP


"JP" wrote in message
...
Another factor is a range...per your example:
=IF(95<A190,"Good",IF(90<A185,"Okay")) Total of 7.....

Now knowing all the details, can this be done?



"JP" wrote:

Yes, I need about 8 functions....I tried the equation below and it
excepted
the formula, but it wouldn't change the answer for each variable, it just
posted the first.

"T. Valko" wrote:

You can use something like this if you have a "few" conditions:

=IF(A1=90,"Excellent",IF(A1=80,"Good",IF(A1=70, "Average","Need
Improvement")))

If you have more than a "few" conditions then your best approach is to
create a 2 column lookup table. Post back if that might be the case.


--
Biff
Microsoft Excel MVP


"JP" wrote in message
...
Is it possible to put multiple IF functions to one cell? For
examples, say
I
want to assign different descriptions depending on what value shows
up in
a
cell.







T. Valko

IF function
 
Where do I create the table? On a seperate sheet?

You can create the table anywhere you want. In my example I put it on the
same sheet as the cell that you're checking. A lot of folks like to put
lookup tables on a separate dedicated sheet just for tables.

Let's say you put the table on Sheet2 in the range A1:B7.

Sheet1 A1 is the number you want to lookup and you want the result in Sheet1
B1. So, you'd enter this formula on Sheet1 in cell B1:

=IF(A1="","",VLOOKUP(A1,Sheet2!A1:B7,2))

Or, you can give the table a defined name and use that name in the formula.

Assume the table is on Sheet2 in the range A1:B7.

Navigate to Sheet2
Select the range A1:B7
In the name box, that little box directly above column A, type in a name for
the table. Example: MyTable
Then hit ENTER

Now you can use that name in the formula:

=IF(A1="","",VLOOKUP(A1,MyTable,2))


--
Biff
Microsoft Excel MVP


"JP" wrote in message
...
Where do I create the table? On a seperate sheet?

"T. Valko" wrote:

Create a 2 column table like this:

...........F..........G.....
1........0..........Poor
2........40........Below Average
3........50........Average
4........60........Above Average
5........70........Good
6........80........Very Good
7........90........Outstanding

A1 = some number

=IF(A1="","",VLOOKUP(A1,F1:G7,2))

0 - 39 = Poor
40 - 49 = Below Average
50 - 59 = Average
60 - 69 = Above Average
70 - 79 = Good
80 - 89 = Very Good
90+ = Outstanding


--
Biff
Microsoft Excel MVP


"JP" wrote in message
...
Another factor is a range...per your example:
=IF(95<A190,"Good",IF(90<A185,"Okay")) Total of 7.....

Now knowing all the details, can this be done?



"JP" wrote:

Yes, I need about 8 functions....I tried the equation below and it
excepted
the formula, but it wouldn't change the answer for each variable, it
just
posted the first.

"T. Valko" wrote:

You can use something like this if you have a "few" conditions:

=IF(A1=90,"Excellent",IF(A1=80,"Good",IF(A1=70, "Average","Need
Improvement")))

If you have more than a "few" conditions then your best approach is
to
create a 2 column lookup table. Post back if that might be the case.


--
Biff
Microsoft Excel MVP


"JP" wrote in message
...
Is it possible to put multiple IF functions to one cell? For
examples, say
I
want to assign different descriptions depending on what value
shows
up in
a
cell.









JP

IF function
 
Got the 2 column table created, but it won't read the 6th spot in the range????
Also, how do you lock in the range so it doesn't move when "draging" the
equation down?
My columns are as follows:

...........F..............G
1........1...........C1
2........2...........C2
3........3..........P1
4........4..........P2
5........5..........P3
6........6..........F
7........7..........T


"JP" wrote:

Ok did that, but gives me "#N/A"

"JP" wrote:

Where do I create the table? On a seperate sheet?

"T. Valko" wrote:

Create a 2 column table like this:

...........F..........G.....
1........0..........Poor
2........40........Below Average
3........50........Average
4........60........Above Average
5........70........Good
6........80........Very Good
7........90........Outstanding

A1 = some number

=IF(A1="","",VLOOKUP(A1,F1:G7,2))

0 - 39 = Poor
40 - 49 = Below Average
50 - 59 = Average
60 - 69 = Above Average
70 - 79 = Good
80 - 89 = Very Good
90+ = Outstanding


--
Biff
Microsoft Excel MVP


"JP" wrote in message
...
Another factor is a range...per your example:
=IF(95<A190,"Good",IF(90<A185,"Okay")) Total of 7.....

Now knowing all the details, can this be done?



"JP" wrote:

Yes, I need about 8 functions....I tried the equation below and it
excepted
the formula, but it wouldn't change the answer for each variable, it just
posted the first.

"T. Valko" wrote:

You can use something like this if you have a "few" conditions:

=IF(A1=90,"Excellent",IF(A1=80,"Good",IF(A1=70, "Average","Need
Improvement")))

If you have more than a "few" conditions then your best approach is to
create a 2 column lookup table. Post back if that might be the case.


--
Biff
Microsoft Excel MVP


"JP" wrote in message
...
Is it possible to put multiple IF functions to one cell? For
examples, say
I
want to assign different descriptions depending on what value shows
up in
a
cell.







JP

IF function
 
Okay, I found what was wrong with the value, but how do I lock in the range
to keep in from moving t I copy the formula downward?

"T. Valko" wrote:

Where do I create the table? On a seperate sheet?


You can create the table anywhere you want. In my example I put it on the
same sheet as the cell that you're checking. A lot of folks like to put
lookup tables on a separate dedicated sheet just for tables.

Let's say you put the table on Sheet2 in the range A1:B7.

Sheet1 A1 is the number you want to lookup and you want the result in Sheet1
B1. So, you'd enter this formula on Sheet1 in cell B1:

=IF(A1="","",VLOOKUP(A1,Sheet2!A1:B7,2))

Or, you can give the table a defined name and use that name in the formula.

Assume the table is on Sheet2 in the range A1:B7.

Navigate to Sheet2
Select the range A1:B7
In the name box, that little box directly above column A, type in a name for
the table. Example: MyTable
Then hit ENTER

Now you can use that name in the formula:

=IF(A1="","",VLOOKUP(A1,MyTable,2))


--
Biff
Microsoft Excel MVP


"JP" wrote in message
...
Where do I create the table? On a seperate sheet?

"T. Valko" wrote:

Create a 2 column table like this:

...........F..........G.....
1........0..........Poor
2........40........Below Average
3........50........Average
4........60........Above Average
5........70........Good
6........80........Very Good
7........90........Outstanding

A1 = some number

=IF(A1="","",VLOOKUP(A1,F1:G7,2))

0 - 39 = Poor
40 - 49 = Below Average
50 - 59 = Average
60 - 69 = Above Average
70 - 79 = Good
80 - 89 = Very Good
90+ = Outstanding


--
Biff
Microsoft Excel MVP


"JP" wrote in message
...
Another factor is a range...per your example:
=IF(95<A190,"Good",IF(90<A185,"Okay")) Total of 7.....

Now knowing all the details, can this be done?



"JP" wrote:

Yes, I need about 8 functions....I tried the equation below and it
excepted
the formula, but it wouldn't change the answer for each variable, it
just
posted the first.

"T. Valko" wrote:

You can use something like this if you have a "few" conditions:

=IF(A1=90,"Excellent",IF(A1=80,"Good",IF(A1=70, "Average","Need
Improvement")))

If you have more than a "few" conditions then your best approach is
to
create a 2 column lookup table. Post back if that might be the case.


--
Biff
Microsoft Excel MVP


"JP" wrote in message
...
Is it possible to put multiple IF functions to one cell? For
examples, say
I
want to assign different descriptions depending on what value
shows
up in
a
cell.










T. Valko

IF function
 
To "lock" the range use absolute references:

=IF(A1="","",VLOOKUP(A1,$F$1:$G$7,2))

Is this your *REAL* data?

..........F..............G
1........1...........C1
2........2...........C2
3........3..........P1
4........4..........P2
5........5..........P3
6........6..........F
7........7..........T


If so, you can do this without creating a table. It's *always* a good idea
to explain what you want and post examples of your *REAL* data. It makes
things a lot easier!



--
Biff
Microsoft Excel MVP


"JP" wrote in message
...
Got the 2 column table created, but it won't read the 6th spot in the
range????
Also, how do you lock in the range so it doesn't move when "draging" the
equation down?
My columns are as follows:

..........F..............G
1........1...........C1
2........2...........C2
3........3..........P1
4........4..........P2
5........5..........P3
6........6..........F
7........7..........T


"JP" wrote:

Ok did that, but gives me "#N/A"

"JP" wrote:

Where do I create the table? On a seperate sheet?

"T. Valko" wrote:

Create a 2 column table like this:

...........F..........G.....
1........0..........Poor
2........40........Below Average
3........50........Average
4........60........Above Average
5........70........Good
6........80........Very Good
7........90........Outstanding

A1 = some number

=IF(A1="","",VLOOKUP(A1,F1:G7,2))

0 - 39 = Poor
40 - 49 = Below Average
50 - 59 = Average
60 - 69 = Above Average
70 - 79 = Good
80 - 89 = Very Good
90+ = Outstanding


--
Biff
Microsoft Excel MVP


"JP" wrote in message
...
Another factor is a range...per your example:
=IF(95<A190,"Good",IF(90<A185,"Okay")) Total of 7.....

Now knowing all the details, can this be done?



"JP" wrote:

Yes, I need about 8 functions....I tried the equation below and it
excepted
the formula, but it wouldn't change the answer for each variable,
it just
posted the first.

"T. Valko" wrote:

You can use something like this if you have a "few" conditions:

=IF(A1=90,"Excellent",IF(A1=80,"Good",IF(A1=70, "Average","Need
Improvement")))

If you have more than a "few" conditions then your best approach
is to
create a 2 column lookup table. Post back if that might be the
case.


--
Biff
Microsoft Excel MVP


"JP" wrote in message
...
Is it possible to put multiple IF functions to one cell? For
examples, say
I
want to assign different descriptions depending on what value
shows
up in
a
cell.










All times are GMT +1. The time now is 08:24 PM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com