IF STATEMENT HELP

[Jennifer]

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!

 

[Peo Sjoblom]

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

 

[joeu2004]

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.