Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
IF STATEMENT HELP
Ok here is what i am neededing help with i have to write a nested IF formula
that meets the following conditions, and after working on it for hours i have decided i need help. Companies are categorized according to their market capitalization. Microcaps are companies with market value below $300 million. Small caps are the ones between $300 mil and $2 billion, Mid caps are the ones between $2 billion and $10 billion. Large caps are the ones between $10 billion and $200 billion, and Mega caps are the ones above $200 billion, if there is no market cap then they are blank. can someone please tell me the best way/formula for this equation, i know that i need to have 4 IF statements im just confused on exactly how to do the ones that are one amount between another. thanks so much for any help! |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
IF STATEMENT HELP
With the values in A1
=IF(A1="","",VLOOKUP(A1,{0,"Micro Caps";300000000,"Small Caps";2000000000,"Mid Caps";10000000000,"Large Caps";200000000000,"Mega Caps"},2)) -- Regards, Peo Sjoblom "Jennifer" wrote in message ... Ok here is what i am neededing help with i have to write a nested IF formula that meets the following conditions, and after working on it for hours i have decided i need help. Companies are categorized according to their market capitalization. Microcaps are companies with market value below $300 million. Small caps are the ones between $300 mil and $2 billion, Mid caps are the ones between $2 billion and $10 billion. Large caps are the ones between $10 billion and $200 billion, and Mega caps are the ones above $200 billion, if there is no market cap then they are blank. can someone please tell me the best way/formula for this equation, i know that i need to have 4 IF statements im just confused on exactly how to do the ones that are one amount between another. thanks so much for any help! |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
IF STATEMENT HELP
On Nov 20, 7:20 am, Jennifer
wrote: can someone please tell me the best way/formula for this equation, i know that i need to have 4 IF statements im just confused on exactly how to do the ones that are one amount between another. It might help you to have a better understanding of the Excel "language". First, there is no "IF statement". IF() is a __function__. And just like any function, it can be used as an element in any expression (where the results make sense, of course). Second, learn to parse the Help page. Click on Help = Microsoft Excel Help, type "IF function" (without quotes) in the Search For field, and click on IF Worksheet Function. You will find: IF(logical_test, value_if_true, value_if_false) The three arguments describe expressions. Any function, including IF(), can appear in any of those expressions. Most importantly, the value_if_false expression can be an IF() function call. The biggest difficulty with nested function calls -- IF() or whatever -- is balancing the function parentheses ("(...)") correctly. Don't fret: we all struggle with that one. Excel tries to help by bouncing the cursor between balanced parentheses as you type. I tend to type both parentheses immediately, then move the cursor to the left to fill in the arguments in between. The other thing to note with nested function calls -- IF() or whatever -- is that Excel has a limit. For Excel 2003, that limit is 7; 8 if you count the outermost function. Companies are categorized according to their market capitalization. Microcaps are companies with market value below $300 million. Small caps are the ones between $300 mil and $2 billion, Mid caps are the ones between $2 billion and $10 billion. Large caps are the ones between $10 billion and $200 billion, and Mega caps are the ones above $200 billion, if there is no market cap then they are blank. If the market cap is in A1 and is expressed in billions (i.e. 0.3 means 300 million, and 2 means 2 billion): =if(A1="", "", if(A1<0.3, "Micro", if(A1<=2, "Small", if(A1<=10, "Mid", if(A1<=200, "Large", "Mega"))))) Notice that you do not actually need to test a range (e.g. AND(0.3<=A1,A1<=200)). The lower end of the range is implied by failing the first test. For example, if A1<0.3 is false, that implies 0.3<=A1 is true. So we only need to test A1<=200. Note: If the market cap in A1 is in millions, change 0.3 to 300 and change 2 to 2000, etc. If the market cap in A1 is the full number (e.g. 300000000), change 0.3 to 300E6 and change 2 to 2E9. Here, "E6" means "10 to the power of 6", which appends 6 zeros and makes "300E6" 300 million. After you type "300E6", Excel might change its appearance. If this is not a classroom assignment, you might eschew the nested IF function calls by relying on VLOOKUP(). Post back if you need help with that. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Can an If statement answer an If statement? | Excel Discussion (Misc queries) | |||
appending and IF statement to an existing IF statement | Excel Worksheet Functions | |||
Help with an if statement | Excel Worksheet Functions | |||
If statement and Isblank statement | Excel Worksheet Functions | |||
Help please, IF statement/SUMIF statement | Excel Worksheet Functions |