Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Multiple Number Ranges Results in Text ('A' if 1-5, 'B' if 6-8, et
Ex. A2 is a bunch of numbers that have been conditionally formatted to show
up as green if between 30 and 34, yellow if between 35 and 45 and red if 46 and over. However, I need B2 to display the word 'green', 'yellow' or 'red' depending on the number range in A2. Intuitively? Easy. In reality? I'm completely stumped :-P Help? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Multiple Number Ranges Results in Text ('A' if 1-5, 'B' if 6-8, et
One way:
=IF(A2<30,"",IF(A2<35,"green",IF(A2<=45,"yellow"," red"))) In article , MS Questionnairess wrote: Ex. A2 is a bunch of numbers that have been conditionally formatted to show up as green if between 30 and 34, yellow if between 35 and 45 and red if 46 and over. However, I need B2 to display the word 'green', 'yellow' or 'red' depending on the number range in A2. Intuitively? Easy. In reality? I'm completely stumped :-P Help? |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Multiple Number Ranges Results in Text ('A' if 1-5, 'B' if 6-8, et
=IF(A2<30,"",IF(A2<35,"Green",IF(A2<46,"Blue","Red ")))
-- --- HTH Bob (change the xxxx to gmail if mailing direct) "MS Questionnairess" wrote in message ... Ex. A2 is a bunch of numbers that have been conditionally formatted to show up as green if between 30 and 34, yellow if between 35 and 45 and red if 46 and over. However, I need B2 to display the word 'green', 'yellow' or 'red' depending on the number range in A2. Intuitively? Easy. In reality? I'm completely stumped :-P Help? |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Multiple Number Ranges Results in Text ('A' if 1-5, 'B' if 6-8, et
You had a number of conditions for which your required result was undefined
in your question, but what about =IF(A2=46,"Red",IF(A2=35,"Yellow",IF(A2=30,"Gre en",""))) ? -- David Biddulph "MS Questionnairess" wrote in message ... Ex. A2 is a bunch of numbers that have been conditionally formatted to show up as green if between 30 and 34, yellow if between 35 and 45 and red if 46 and over. However, I need B2 to display the word 'green', 'yellow' or 'red' depending on the number range in A2. Intuitively? Easy. In reality? I'm completely stumped :-P Help? |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Multiple Number Ranges Results in Text ('A' if 1-5, 'B' if 6-8
It's the darndest thing... .
It *mostly* works... but a couple of the number values in column 'A' don't seem to correspond correctly. So that in one case, I have my lovely yellow '36' in column 'A', appearing in column 'B' as 'Red' (in one case), 'Yellow' (in another case) and 'Green' (in another case). It's really bizarre. 'Green' should be like 30<'Green'35 'Yellow' should be like 36<'Yellow'45 'Red' should be like 'Red'46 I'm excited by getting this far but now doubley-stumped as to why it isn't *quite* working. But, Bob, you got me farther than I ever got with my torturous ponderings so I thank you very much for getting me this far :-) Any idea how I can just. Get. A. Little. Farther? Much obliged! "Bob Phillips" wrote: =IF(A2<30,"",IF(A2<35,"Green",IF(A2<46,"Blue","Red "))) -- --- HTH Bob (change the xxxx to gmail if mailing direct) "MS Questionnairess" wrote in message ... Ex. A2 is a bunch of numbers that have been conditionally formatted to show up as green if between 30 and 34, yellow if between 35 and 45 and red if 46 and over. However, I need B2 to display the word 'green', 'yellow' or 'red' depending on the number range in A2. Intuitively? Easy. In reality? I'm completely stumped :-P Help? |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Multiple Number Ranges Results in Text ('A' if 1-5, 'B' if 6-8
Darned if I'm having the same problems with mismatched colours and the alert
text as mentioned above. A2:A1000 is derived from another forumla: =DATEDIF(A3,$C$1, "d") [which ever date I submit as the date of the report - Date Received of Document] Could this be screwing up the forumlas? Or am I just cursed by the Excel gods? <-- entirely possible as our tech guys are convinced I can single-handedly bring down the entire internet with one press of a button :-) "David Biddulph" wrote: You had a number of conditions for which your required result was undefined in your question, but what about =IF(A2=46,"Red",IF(A2=35,"Yellow",IF(A2=30,"Gre en",""))) ? -- David Biddulph "MS Questionnairess" wrote in message ... Ex. A2 is a bunch of numbers that have been conditionally formatted to show up as green if between 30 and 34, yellow if between 35 and 45 and red if 46 and over. However, I need B2 to display the word 'green', 'yellow' or 'red' depending on the number range in A2. Intuitively? Easy. In reality? I'm completely stumped :-P Help? |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Multiple Number Ranges Results in Text ('A' if 1-5, 'B' if 6-8, et
"MS Questionnairess" skrev i
en meddelelse ... Ex. A2 is a bunch of numbers that have been conditionally formatted to show up as green if between 30 and 34, yellow if between 35 and 45 and red if 46 and over. However, I need B2 to display the word 'green', 'yellow' or 'red' depending on the number range in A2. Intuitively? Easy. In reality? I'm completely stumped :-P Help? Here's one more way to do it. In B2: =INDEX({"No color","Green","Yellow","Red"},SUM((A2={46,35,30, 0})+0)) -- Best regards Leo Heuser Followup to newsgroup only please. |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Multiple Number Ranges Results in Text ('A' if 1-5, 'B' if 6-8, et
I am a *complete* moron.
I figured out why things were just. A. Bit. Off. I am terribly embarrassed to say, but... um... The formulas given had 'A2' and, in fact, they should have had 'A3'. So every answer was off one cell. I am ashamed and embarrassed and highly grateful for all the help people have offered. All the formulas work intelligently except I, apparently, don't :-} This is great, though! I have a couple of people who are really interested in how I'm going to come up with this solution so I'll pass on these wonderous formulas to them, too. Many, *many* thanks and I'd use 'it's Monday, after all' as a lame excuse? "MS Questionnairess" wrote: Ex. A2 is a bunch of numbers that have been conditionally formatted to show up as green if between 30 and 34, yellow if between 35 and 45 and red if 46 and over. However, I need B2 to display the word 'green', 'yellow' or 'red' depending on the number range in A2. Intuitively? Easy. In reality? I'm completely stumped :-P Help? |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Multiple Number Ranges Results in Text ('A' if 1-5, 'B' if 6-8, et
"MS Questionnairess" wrote: Ex. A2 is a bunch of numbers that have been conditionally formatted to show up as green if between 30 and 34, yellow if between 35 and 45 and red if 46 and over. However, I need B2 to display the word 'green', 'yellow' or 'red' depending on the number range in A2. Intuitively? Easy. In reality? I'm completely stumped :-P Help? "MS Questionnairess" skrev i en meddelelse ... I am a *complete* moron. I figured out why things were just. A. Bit. Off. I am terribly embarrassed to say, but... um... The formulas given had 'A2' and, in fact, they should have had 'A3'. So every answer was off one cell. I am ashamed and embarrassed and highly grateful for all the help people have offered. All the formulas work intelligently except I, apparently, don't :-} This is great, though! I have a couple of people who are really interested in how I'm going to come up with this solution so I'll pass on these wonderous formulas to them, too. Many, *many* thanks and I'd use 'it's Monday, after all' as a lame excuse? You're welcome and please, don't go to pieces :-) We all make mistakes. Leo Heuser |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Setting the number of decimal places for a text box. | Excel Discussion (Misc queries) | |||
Change number format from text to number? | New Users to Excel | |||
Justify text across multiple columns | Excel Discussion (Misc queries) | |||
How do I look up a number within a string of text | Excel Worksheet Functions | |||
Formulas dealing with text data | Excel Worksheet Functions |