Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
complex formula
i have a list of random numbers in column A1 0-75. i am trying to make a
formula that will predict results based on the number in column A that will appear in column B up to 29 should result in a letter W in column B 30 - 37 should result in a number 1 38 - 44 should result in 2C 45 - 55 should result in 2B 56 -65 should result in 2A 66 and over should result in 3 Have been trying all day, can get the numbers but as soon as I try to add letters I am lost. any help would be greatly appreciated. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
complex formula
You should try a VLOOKUP table. See:
http://www.contextures.com/xlFunctions02.html -- Gary's Student "Lou" wrote: i have a list of random numbers in column A1 0-75. i am trying to make a formula that will predict results based on the number in column A that will appear in column B up to 29 should result in a letter W in column B 30 - 37 should result in a number 1 38 - 44 should result in 2C 45 - 55 should result in 2B 56 -65 should result in 2A 66 and over should result in 3 Have been trying all day, can get the numbers but as soon as I try to add letters I am lost. any help would be greatly appreciated. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
complex formula
Not really that hard. Look in the help index for LOOKUP
try =LOOKUP(G8,{0,30,38,45,56,66},{"w",1,"2c","2b","2a ",3}) -- Don Guillett SalesAid Software "Lou" wrote in message ... i have a list of random numbers in column A1 0-75. i am trying to make a formula that will predict results based on the number in column A that will appear in column B up to 29 should result in a letter W in column B 30 - 37 should result in a number 1 38 - 44 should result in 2C 45 - 55 should result in 2B 56 -65 should result in 2A 66 and over should result in 3 Have been trying all day, can get the numbers but as soon as I try to add letters I am lost. any help would be greatly appreciated. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
complex formula
Not really hard when you know what you are doing! I unfortunately am still a
novice. Thanks for trying to help me much appreciated, but I seem to get w's with every result when I tried this one. Don't use any more of your valuable time on this one though -another kind person has helped me too. sorted! "Don Guillett" wrote: Not really that hard. Look in the help index for LOOKUP try =LOOKUP(G8,{0,30,38,45,56,66},{"w",1,"2c","2b","2a ",3}) -- Don Guillett SalesAid Software "Lou" wrote in message ... i have a list of random numbers in column A1 0-75. i am trying to make a formula that will predict results based on the number in column A that will appear in column B up to 29 should result in a letter W in column B 30 - 37 should result in a number 1 38 - 44 should result in 2C 45 - 55 should result in 2B 56 -65 should result in 2A 66 and over should result in 3 Have been trying all day, can get the numbers but as soon as I try to add letters I am lost. any help would be greatly appreciated. |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
complex formula
Try:
=IF(A165,"3",IF(A155,"A2",IF(A144,"2B",IF(A137 ,"2C",IF(A129,"1","W"))))) -- HTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings with @tiscali.co.uk "Lou" wrote in message ... i have a list of random numbers in column A1 0-75. i am trying to make a formula that will predict results based on the number in column A that will appear in column B up to 29 should result in a letter W in column B 30 - 37 should result in a number 1 38 - 44 should result in 2C 45 - 55 should result in 2B 56 -65 should result in 2A 66 and over should result in 3 Have been trying all day, can get the numbers but as soon as I try to add letters I am lost. any help would be greatly appreciated. |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
complex formula
What can I say? thank you so much, now I can sleep. :)
"Sandy Mann" wrote: Try: =IF(A165,"3",IF(A155,"A2",IF(A144,"2B",IF(A137 ,"2C",IF(A129,"1","W"))))) -- HTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings with @tiscali.co.uk "Lou" wrote in message ... i have a list of random numbers in column A1 0-75. i am trying to make a formula that will predict results based on the number in column A that will appear in column B up to 29 should result in a letter W in column B 30 - 37 should result in a number 1 38 - 44 should result in 2C 45 - 55 should result in 2B 56 -65 should result in 2A 66 and over should result in 3 Have been trying all day, can get the numbers but as soon as I try to add letters I am lost. any help would be greatly appreciated. |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
complex formula
You're welcome, glad you got it to work.
Don & Gord's formulas work just as well and are shorter but at first sight it is not as obvious how they work as it is with my simple formula. Thanks for posting back. -- Regards, Sandy In Perth, the ancient capital of Scotland and the crowning place of kings with @tiscali.co.uk "Lou" wrote in message ... What can I say? thank you so much, now I can sleep. :) "Sandy Mann" wrote: Try: =IF(A165,"3",IF(A155,"A2",IF(A144,"2B",IF(A137 ,"2C",IF(A129,"1","W"))))) -- HTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings with @tiscali.co.uk "Lou" wrote in message ... i have a list of random numbers in column A1 0-75. i am trying to make a formula that will predict results based on the number in column A that will appear in column B up to 29 should result in a letter W in column B 30 - 37 should result in a number 1 38 - 44 should result in 2C 45 - 55 should result in 2B 56 -65 should result in 2A 66 and over should result in 3 Have been trying all day, can get the numbers but as soon as I try to add letters I am lost. any help would be greatly appreciated. |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
complex formula
Lou
Try this formula in B1 =LOOKUP(A1,{0,30,38,45,56,66},{"W",1,"2C","2B","2A ",3}) Double-click to copy down if you have data in A1:A75 Gord Dibben MS Excel MVP On Sat, 16 Sep 2006 12:38:01 -0700, Lou wrote: i have a list of random numbers in column A1 0-75. i am trying to make a formula that will predict results based on the number in column A that will appear in column B up to 29 should result in a letter W in column B 30 - 37 should result in a number 1 38 - 44 should result in 2C 45 - 55 should result in 2B 56 -65 should result in 2A 66 and over should result in 3 Have been trying all day, can get the numbers but as soon as I try to add letters I am lost. any help would be greatly appreciated. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
complex average formula | Excel Worksheet Functions | |||
A formula that is too complex for Excel? | Excel Worksheet Functions | |||
Complex Formula Getting Error.. | Excel Discussion (Misc queries) | |||
Formula Problem - interrupted by #VALUE! in other cells!? | Excel Worksheet Functions | |||
Complex formula | Excel Discussion (Misc queries) |