Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formula that checks several criteria before returing the value???
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. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formula that checks several criteria before returing the value???
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. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formula that checks several criteria before returing the value???
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. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formula that checks several criteria before returing the value
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. |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formula that checks several criteria before returing the value
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Advanced Filter criteria (formula) | Excel Worksheet Functions | |||
Match then lookup | Excel Worksheet Functions | |||
Specify DCOUNTA Criteria in the Formula | Excel Discussion (Misc queries) | |||
SUMPRODUCT Formula to Count Row of data Below Matched Criteria | Excel Worksheet Functions | |||
Need formula to check values of data in several cells as criteria | Excel Worksheet Functions |