|
Display single value for multiple ranges?
OK, I am not sure how to even begin to search for this because I am unsure of
the nomenclature I need to look for. That said, here is what I am looking to understand: I need to display a value of "1" if a total (manually entered in an adjacent cell) is 20-29; "2" if a total is 30-39; and "3" if a total is 40+. So if the entered value is 25, the value displayed in the adjacent cell would be "1". Can and how do I do this? Many thanks in advance! |
Hi Chris,
Option 1: Create a table, say in range A1:B3, like this: 20 1 30 2 40 3 if in cell C1 you manually introduce a serched value, then in cell D1 input the following formula: =VLOOKUP(C1,$A$1:$B$3,2) Option 2: No table necessary, just use the following formula: =VLOOKUP(C1,{20,1;30,2;40,3},2) Option 3: No table necessary, just use the following formula: =INDEX({1,2,3},MATCH(C1,{20;30;40})) Regards, KL "Chris McDannold" <Chris wrote in message ... OK, I am not sure how to even begin to search for this because I am unsure of the nomenclature I need to look for. That said, here is what I am looking to understand: I need to display a value of "1" if a total (manually entered in an adjacent cell) is 20-29; "2" if a total is 30-39; and "3" if a total is 40+. So if the entered value is 25, the value displayed in the adjacent cell would be "1". Can and how do I do this? Many thanks in advance! |
Hi!
Assume the value is entered in A1: =IF(A1=40,3,IF(A1=30,2,IF(A1=20,1,0))) You didn't specify what to do if A1 < 20 so I just used a zero for that condition. If instead, you want the cell to be blank: =IF(A1=40,3,IF(A1=30,2,IF(A1=20,1,""))) Biff "Chris McDannold" <Chris wrote in message ... OK, I am not sure how to even begin to search for this because I am unsure of the nomenclature I need to look for. That said, here is what I am looking to understand: I need to display a value of "1" if a total (manually entered in an adjacent cell) is 20-29; "2" if a total is 30-39; and "3" if a total is 40+. So if the entered value is 25, the value displayed in the adjacent cell would be "1". Can and how do I do this? Many thanks in advance! |
Wonderful! However, please see the response to Biff below as I am having the
same problem when entering your formula. Thanks again! "KL" wrote: Hi Chris, Option 1: Create a table, say in range A1:B3, like this: 20 1 30 2 40 3 if in cell C1 you manually introduce a serched value, then in cell D1 input the following formula: =VLOOKUP(C1,$A$1:$B$3,2) Option 2: No table necessary, just use the following formula: =VLOOKUP(C1,{20,1;30,2;40,3},2) Option 3: No table necessary, just use the following formula: =INDEX({1,2,3},MATCH(C1,{20;30;40})) Regards, KL "Chris McDannold" <Chris wrote in message ... OK, I am not sure how to even begin to search for this because I am unsure of the nomenclature I need to look for. That said, here is what I am looking to understand: I need to display a value of "1" if a total (manually entered in an adjacent cell) is 20-29; "2" if a total is 30-39; and "3" if a total is 40+. So if the entered value is 25, the value displayed in the adjacent cell would be "1". Can and how do I do this? Many thanks in advance! |
Good morning Chris McDannold This formula will do what you want by checking the cell A1 (you haven't specified). You didn't mention what is to return if the value of the cell being checked is less than 20. In the example below a zero is returned. =IF(A1=40,3,IF(A1=30,2,IF(A1=20,1,0))) HTH DominicB -- dominicb ------------------------------------------------------------------------ dominicb's Profile: http://www.excelforum.com/member.php...o&userid=18932 View this thread: http://www.excelforum.com/showthread...hreadid=395528 |
Can't see the response to Biff :-(
Have you already posted it? KL "Chris McDannold" wrote in message ... Wonderful! However, please see the response to Biff below as I am having the same problem when entering your formula. Thanks again! "KL" wrote: Hi Chris, Option 1: Create a table, say in range A1:B3, like this: 20 1 30 2 40 3 if in cell C1 you manually introduce a serched value, then in cell D1 input the following formula: =VLOOKUP(C1,$A$1:$B$3,2) Option 2: No table necessary, just use the following formula: =VLOOKUP(C1,{20,1;30,2;40,3},2) Option 3: No table necessary, just use the following formula: =INDEX({1,2,3},MATCH(C1,{20;30;40})) Regards, KL "Chris McDannold" <Chris wrote in message ... OK, I am not sure how to even begin to search for this because I am unsure of the nomenclature I need to look for. That said, here is what I am looking to understand: I need to display a value of "1" if a total (manually entered in an adjacent cell) is 20-29; "2" if a total is 30-39; and "3" if a total is 40+. So if the entered value is 25, the value displayed in the adjacent cell would be "1". Can and how do I do this? Many thanks in advance! |
This is great! I have a problem though. The desired value does not display in
the cell this formula is entered into. If I enter 23 in A1, all I see in A2 (where the forumla is located) is the forumla itself. Looking at the formula's logical test, the only time "Value_if_false" is reported with a value is when A1<20. How do I set it so it only shows what is true, and disregards a false result EXCEPT when A1<20? Does that make sense? "Biff" wrote: Hi! Assume the value is entered in A1: =IF(A1=40,3,IF(A1=30,2,IF(A1=20,1,0))) You didn't specify what to do if A1 < 20 so I just used a zero for that condition. If instead, you want the cell to be blank: =IF(A1=40,3,IF(A1=30,2,IF(A1=20,1,""))) Biff "Chris McDannold" <Chris wrote in message ... OK, I am not sure how to even begin to search for this because I am unsure of the nomenclature I need to look for. That said, here is what I am looking to understand: I need to display a value of "1" if a total (manually entered in an adjacent cell) is 20-29; "2" if a total is 30-39; and "3" if a total is 40+. So if the entered value is 25, the value displayed in the adjacent cell would be "1". Can and how do I do this? Many thanks in advance! |
Chris,
Try going the menu ToolsOptions, selecting the 'View' tab, and uncheking the 'Formulas' option at the bottom part of the dialog. Regards, KL "Chris McDannold" wrote in message ... Wonderful! However, please see the response to Biff below as I am having the same problem when entering your formula. Thanks again! "KL" wrote: Hi Chris, Option 1: Create a table, say in range A1:B3, like this: 20 1 30 2 40 3 if in cell C1 you manually introduce a serched value, then in cell D1 input the following formula: =VLOOKUP(C1,$A$1:$B$3,2) Option 2: No table necessary, just use the following formula: =VLOOKUP(C1,{20,1;30,2;40,3},2) Option 3: No table necessary, just use the following formula: =INDEX({1,2,3},MATCH(C1,{20;30;40})) Regards, KL "Chris McDannold" <Chris wrote in message ... OK, I am not sure how to even begin to search for this because I am unsure of the nomenclature I need to look for. That said, here is what I am looking to understand: I need to display a value of "1" if a total (manually entered in an adjacent cell) is 20-29; "2" if a total is 30-39; and "3" if a total is 40+. So if the entered value is 25, the value displayed in the adjacent cell would be "1". Can and how do I do this? Many thanks in advance! |
Yes, but here is the text of the response. The formula he suggested is as
follows: =IF(A1=40,3,IF(A1=30,2,IF(A1=20,1,0))) Response: This is great! I have a problem though. The desired value does not display in the cell this formula is entered into. If I enter 23 in A1, all I see in A2 (where the forumla is located) is the forumla itself. Looking at the formula's logical test, the only time "Value_if_false" is reported with a value is when A1<20. How do I set it so it only shows what is true, and disregards a false result EXCEPT when A1<20? Does that make sense? "KL" wrote: Can't see the response to Biff :-( Have you already posted it? KL "Chris McDannold" wrote in message ... Wonderful! However, please see the response to Biff below as I am having the same problem when entering your formula. Thanks again! "KL" wrote: Hi Chris, Option 1: Create a table, say in range A1:B3, like this: 20 1 30 2 40 3 if in cell C1 you manually introduce a serched value, then in cell D1 input the following formula: =VLOOKUP(C1,$A$1:$B$3,2) Option 2: No table necessary, just use the following formula: =VLOOKUP(C1,{20,1;30,2;40,3},2) Option 3: No table necessary, just use the following formula: =INDEX({1,2,3},MATCH(C1,{20;30;40})) Regards, KL "Chris McDannold" <Chris wrote in message ... OK, I am not sure how to even begin to search for this because I am unsure of the nomenclature I need to look for. That said, here is what I am looking to understand: I need to display a value of "1" if a total (manually entered in an adjacent cell) is 20-29; "2" if a total is 30-39; and "3" if a total is 40+. So if the entered value is 25, the value displayed in the adjacent cell would be "1". Can and how do I do this? Many thanks in advance! |
Yeah, I thought of that too.
"KL" wrote: Chris, Try going the menu ToolsOptions, selecting the 'View' tab, and uncheking the 'Formulas' option at the bottom part of the dialog. Regards, KL "Chris McDannold" wrote in message ... Wonderful! However, please see the response to Biff below as I am having the same problem when entering your formula. Thanks again! "KL" wrote: Hi Chris, Option 1: Create a table, say in range A1:B3, like this: 20 1 30 2 40 3 if in cell C1 you manually introduce a serched value, then in cell D1 input the following formula: =VLOOKUP(C1,$A$1:$B$3,2) Option 2: No table necessary, just use the following formula: =VLOOKUP(C1,{20,1;30,2;40,3},2) Option 3: No table necessary, just use the following formula: =INDEX({1,2,3},MATCH(C1,{20;30;40})) Regards, KL "Chris McDannold" <Chris wrote in message ... OK, I am not sure how to even begin to search for this because I am unsure of the nomenclature I need to look for. That said, here is what I am looking to understand: I need to display a value of "1" if a total (manually entered in an adjacent cell) is 20-29; "2" if a total is 30-39; and "3" if a total is 40+. So if the entered value is 25, the value displayed in the adjacent cell would be "1". Can and how do I do this? Many thanks in advance! |
Hi Chris,
Does your answer mean it worked or not? Regards, KL "Chris McDannold" wrote in message ... Yeah, I thought of that too. "KL" wrote: Chris, Try going the menu ToolsOptions, selecting the 'View' tab, and uncheking the 'Formulas' option at the bottom part of the dialog. Regards, KL "Chris McDannold" wrote in message ... Wonderful! However, please see the response to Biff below as I am having the same problem when entering your formula. Thanks again! "KL" wrote: Hi Chris, Option 1: Create a table, say in range A1:B3, like this: 20 1 30 2 40 3 if in cell C1 you manually introduce a serched value, then in cell D1 input the following formula: =VLOOKUP(C1,$A$1:$B$3,2) Option 2: No table necessary, just use the following formula: =VLOOKUP(C1,{20,1;30,2;40,3},2) Option 3: No table necessary, just use the following formula: =INDEX({1,2,3},MATCH(C1,{20;30;40})) Regards, KL "Chris McDannold" <Chris wrote in message ... OK, I am not sure how to even begin to search for this because I am unsure of the nomenclature I need to look for. That said, here is what I am looking to understand: I need to display a value of "1" if a total (manually entered in an adjacent cell) is 20-29; "2" if a total is 30-39; and "3" if a total is 40+. So if the entered value is 25, the value displayed in the adjacent cell would be "1". Can and how do I do this? Many thanks in advance! |
Sorry...no that had no effect.
"KL" wrote: Hi Chris, Does your answer mean it worked or not? Regards, KL "Chris McDannold" wrote in message ... Yeah, I thought of that too. "KL" wrote: Chris, Try going the menu ToolsOptions, selecting the 'View' tab, and uncheking the 'Formulas' option at the bottom part of the dialog. Regards, KL "Chris McDannold" wrote in message ... Wonderful! However, please see the response to Biff below as I am having the same problem when entering your formula. Thanks again! "KL" wrote: Hi Chris, Option 1: Create a table, say in range A1:B3, like this: 20 1 30 2 40 3 if in cell C1 you manually introduce a serched value, then in cell D1 input the following formula: =VLOOKUP(C1,$A$1:$B$3,2) Option 2: No table necessary, just use the following formula: =VLOOKUP(C1,{20,1;30,2;40,3},2) Option 3: No table necessary, just use the following formula: =INDEX({1,2,3},MATCH(C1,{20;30;40})) Regards, KL "Chris McDannold" <Chris wrote in message ... OK, I am not sure how to even begin to search for this because I am unsure of the nomenclature I need to look for. That said, here is what I am looking to understand: I need to display a value of "1" if a total (manually entered in an adjacent cell) is 20-29; "2" if a total is 30-39; and "3" if a total is 40+. So if the entered value is 25, the value displayed in the adjacent cell would be "1". Can and how do I do this? Many thanks in advance! |
Then try the following:
1) select the cell that contain the formula 2) go to menu FormatCells... 3) on the 'Number' tab choose 'General' from the 'Category' list and hit 'OK' 4) clic inside the formula bar and hit 'Enter' Any changes? Regards, KL "Chris McDannold" wrote in message ... Sorry...no that had no effect. "KL" wrote: Hi Chris, Does your answer mean it worked or not? Regards, KL "Chris McDannold" wrote in message ... Yeah, I thought of that too. "KL" wrote: Chris, Try going the menu ToolsOptions, selecting the 'View' tab, and uncheking the 'Formulas' option at the bottom part of the dialog. Regards, KL "Chris McDannold" wrote in message ... Wonderful! However, please see the response to Biff below as I am having the same problem when entering your formula. Thanks again! "KL" wrote: Hi Chris, Option 1: Create a table, say in range A1:B3, like this: 20 1 30 2 40 3 if in cell C1 you manually introduce a serched value, then in cell D1 input the following formula: =VLOOKUP(C1,$A$1:$B$3,2) Option 2: No table necessary, just use the following formula: =VLOOKUP(C1,{20,1;30,2;40,3},2) Option 3: No table necessary, just use the following formula: =INDEX({1,2,3},MATCH(C1,{20;30;40})) Regards, KL "Chris McDannold" <Chris wrote in message ... OK, I am not sure how to even begin to search for this because I am unsure of the nomenclature I need to look for. That said, here is what I am looking to understand: I need to display a value of "1" if a total (manually entered in an adjacent cell) is 20-29; "2" if a total is 30-39; and "3" if a total is 40+. So if the entered value is 25, the value displayed in the adjacent cell would be "1". Can and how do I do this? Many thanks in advance! |
That got it. Thanks so much!
"KL" wrote: Then try the following: 1) select the cell that contain the formula 2) go to menu FormatCells... 3) on the 'Number' tab choose 'General' from the 'Category' list and hit 'OK' 4) clic inside the formula bar and hit 'Enter' Any changes? Regards, KL "Chris McDannold" wrote in message ... Sorry...no that had no effect. "KL" wrote: Hi Chris, Does your answer mean it worked or not? Regards, KL "Chris McDannold" wrote in message ... Yeah, I thought of that too. "KL" wrote: Chris, Try going the menu ToolsOptions, selecting the 'View' tab, and uncheking the 'Formulas' option at the bottom part of the dialog. Regards, KL "Chris McDannold" wrote in message ... Wonderful! However, please see the response to Biff below as I am having the same problem when entering your formula. Thanks again! "KL" wrote: Hi Chris, Option 1: Create a table, say in range A1:B3, like this: 20 1 30 2 40 3 if in cell C1 you manually introduce a serched value, then in cell D1 input the following formula: =VLOOKUP(C1,$A$1:$B$3,2) Option 2: No table necessary, just use the following formula: =VLOOKUP(C1,{20,1;30,2;40,3},2) Option 3: No table necessary, just use the following formula: =INDEX({1,2,3},MATCH(C1,{20;30;40})) Regards, KL "Chris McDannold" <Chris wrote in message ... OK, I am not sure how to even begin to search for this because I am unsure of the nomenclature I need to look for. That said, here is what I am looking to understand: I need to display a value of "1" if a total (manually entered in an adjacent cell) is 20-29; "2" if a total is 30-39; and "3" if a total is 40+. So if the entered value is 25, the value displayed in the adjacent cell would be "1". Can and how do I do this? Many thanks in advance! |
Assuming that 29.8 is still mapped onto 1 and disregarding a simpler
rounding solution for a moment... =LOOKUP(A2,{-9.99999999999999E+307,0;20,1;30,2;40,3;"",0}) would cover a bit more that is desirable to have. Note that "" is the result of =CHAR(1). KL wrote: Hi Chris, Option 1: Create a table, say in range A1:B3, like this: 20 1 30 2 40 3 if in cell C1 you manually introduce a serched value, then in cell D1 input the following formula: =VLOOKUP(C1,$A$1:$B$3,2) Option 2: No table necessary, just use the following formula: =VLOOKUP(C1,{20,1;30,2;40,3},2) Option 3: No table necessary, just use the following formula: =INDEX({1,2,3},MATCH(C1,{20;30;40})) Regards, KL "Chris McDannold" <Chris wrote in message ... OK, I am not sure how to even begin to search for this because I am unsure of the nomenclature I need to look for. That said, here is what I am looking to understand: I need to display a value of "1" if a total (manually entered in an adjacent cell) is 20-29; "2" if a total is 30-39; and "3" if a total is 40+. So if the entered value is 25, the value displayed in the adjacent cell would be "1". Can and how do I do this? Many thanks in advance! -- [1] The SumProduct function should implicitly coerce the truth values to their Excel numeric equivalents. [2] The lookup functions should have an optional argument for the return value, defaulting to #N/A in its absence. |
| All times are GMT +1. The time now is 01:51 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com