[Apinun]

Hi there!

I would like a formula that checks through several levels of criteria before
returning the value.
So, for example, I have the following:

1 A B C D E
2 High 50 A
3 Med 40 B
4 Low 30 C
5 Others

Column A contains Labels that I want the formula to return after checking
the values in columns B and C.

Example 1:
An item has score of 50 (column B) and rating of "A" (column C). Therefore,
the formula will return the label of "High" (column A) for this item.

Example 2:
An item has score of 45 and rating of "B". As the score of Therefore, the
formula returns label of "Med" for this item.

Example 3:
An item has score of 50 and rating of "B". The item does have the score in
the "High" level. But since it has rating of "B", the formula will return
"Med" for this item.

Example 4:
An item has score of 55 and rating of "C". The formula will return label of
"Low" for this item.

Example 5:
An item has score of 35 and rating of "D". Since the item does not have a
rating of at least "C" in order to be labeled "Low". The formula will return
"others"

Hope U guys understand my question.
Cheers.

[Melissa]

Gee... how should I know??? That's why I asked you to post it on the Message
Board!
:-D
[To other readers: this is a running joke between Apinun and I]

"Apinun" wrote:

Hi there!

I would like a formula that checks through several levels of criteria before
returning the value.
So, for example, I have the following:

1 A B C D E
2 High 50 A
3 Med 40 B
4 Low 30 C
5 Others

Column A contains Labels that I want the formula to return after checking
the values in columns B and C.

Example 1:
An item has score of 50 (column B) and rating of "A" (column C). Therefore,
the formula will return the label of "High" (column A) for this item.

Example 2:
An item has score of 45 and rating of "B". As the score of Therefore, the
formula returns label of "Med" for this item.

Example 3:
An item has score of 50 and rating of "B". The item does have the score in
the "High" level. But since it has rating of "B", the formula will return
"Med" for this item.

Example 4:
An item has score of 55 and rating of "C". The formula will return label of
"Low" for this item.

Example 5:
An item has score of 35 and rating of "D". Since the item does not have a
rating of at least "C" in order to be labeled "Low". The formula will return
"others"

Hope U guys understand my question.
Cheers.

[Kevin B]

You can create a lookup table and reference the lookup table to return a
rating. The following table was placed in F1 through G4 :

0 Others
30 Low
40 Med
50 High

The following formula was placed in column A:

=VLOOKUP(B1,$F$1:$G$4,2)

--
Kevin Backmann


"Apinun" wrote:

Hi there!

I would like a formula that checks through several levels of criteria before
returning the value.
So, for example, I have the following:

1 A B C D E
2 High 50 A
3 Med 40 B
4 Low 30 C
5 Others

Column A contains Labels that I want the formula to return after checking
the values in columns B and C.

Example 1:
An item has score of 50 (column B) and rating of "A" (column C). Therefore,
the formula will return the label of "High" (column A) for this item.

Example 2:
An item has score of 45 and rating of "B". As the score of Therefore, the
formula returns label of "Med" for this item.

Example 3:
An item has score of 50 and rating of "B". The item does have the score in
the "High" level. But since it has rating of "B", the formula will return
"Med" for this item.

Example 4:
An item has score of 55 and rating of "C". The formula will return label of
"Low" for this item.

Example 5:
An item has score of 35 and rating of "D". Since the item does not have a
rating of at least "C" in order to be labeled "Low". The formula will return
"others"

Hope U guys understand my question.
Cheers.

[Apinun]

Thks for yr answer. However, in this case vlookup will not work as i m
looking for the formula that check more than one criteria before returning
the value.

Still waiting for more suggestions...Thks.
-------------------------------
"Kevin B" wrote:

You can create a lookup table and reference the lookup table to return a
rating. The following table was placed in F1 through G4 :

0 Others
30 Low
40 Med
50 High

The following formula was placed in column A:

=VLOOKUP(B1,$F$1:$G$4,2)

--
Kevin Backmann


"Apinun" wrote:

Hi there!

I would like a formula that checks through several levels of criteria before
returning the value.
So, for example, I have the following:

1 A B C D E
2 High 50 A
3 Med 40 B
4 Low 30 C
5 Others

Column A contains Labels that I want the formula to return after checking
the values in columns B and C.

Example 1:
An item has score of 50 (column B) and rating of "A" (column C). Therefore,
the formula will return the label of "High" (column A) for this item.

Example 2:
An item has score of 45 and rating of "B". As the score of Therefore, the
formula returns label of "Med" for this item.

Example 3:
An item has score of 50 and rating of "B". The item does have the score in
the "High" level. But since it has rating of "B", the formula will return
"Med" for this item.

Example 4:
An item has score of 55 and rating of "C". The formula will return label of
"Low" for this item.

Example 5:
An item has score of 35 and rating of "D". Since the item does not have a
rating of at least "C" in order to be labeled "Low". The formula will return
"others"

Hope U guys understand my question.
Cheers.

[Melissa]

Hi Kevin,
I'm working with Aipnun on this. Let me clarify this further:
vlookup won't work as our scores are not always 50, 40, 30, etc. but can
fall between these numbers. If you will, column B contains values which are
the MINIMUM an item must score before it gets the label from column A (High,
Med, Low, etc.).
Now, the additional complication is that, before the label in column A is
returned, a second check is made by comparing with column C. So, even if I
get a score of 55, I won't always get "High" label if my rating (column C) is
not equal to "A".

Hope this clarifies our problem better. Thanks!


"Kevin B" wrote:

You can create a lookup table and reference the lookup table to return a
rating. The following table was placed in F1 through G4 :

0 Others
30 Low
40 Med
50 High

The following formula was placed in column A:

=VLOOKUP(B1,$F$1:$G$4,2)

--
Kevin Backmann


"Apinun" wrote:

Hi there!

I would like a formula that checks through several levels of criteria before
returning the value.
So, for example, I have the following:

1 A B C D E
2 High 50 A
3 Med 40 B
4 Low 30 C
5 Others

Column A contains Labels that I want the formula to return after checking
the values in columns B and C.

Example 1:
An item has score of 50 (column B) and rating of "A" (column C). Therefore,
the formula will return the label of "High" (column A) for this item.

Example 2:
An item has score of 45 and rating of "B". As the score of Therefore, the
formula returns label of "Med" for this item.

Example 3:
An item has score of 50 and rating of "B". The item does have the score in
the "High" level. But since it has rating of "B", the formula will return
"Med" for this item.

Example 4:
An item has score of 55 and rating of "C". The formula will return label of
"Low" for this item.

Example 5:
An item has score of 35 and rating of "D". Since the item does not have a
rating of at least "C" in order to be labeled "Low". The formula will return
"others"

Hope U guys understand my question.
Cheers.