Results 1 to 5 of 5

20030806, 20:38 #1
 Join Date
 Feb 2003
 Location
 Los Angeles, California, USA
 Posts
 18
 Thanks
 0
 Thanked 0 Times in 0 Posts
Help on IF statements and nested IF statements (Excel97)
The worksheet has 11 stocks. The criteria are: a) At least 80% of the equities must be equal to or greater than a market capitalization of 5 Billion; [img]/forums/images/smilies/cool.gif[/img] if the stock has a market capitalization that is between 5 Billion and 50 Billion, then that position must not be greater than 20% of the total market value; c) and, no stock with a market capitalization of greater than 50 Billion may be greater than 25% of the total market value

20030806, 22:28 #2
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
Re: Help on IF statements and nested IF statements (Excel97)
I don't see a question and don't really understand what you are asking.
What do you want of the if statement?
Steve

20030806, 23:05 #3
 Join Date
 Feb 2003
 Location
 Los Angeles, California, USA
 Posts
 18
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Help on IF statements and nested IF statements (Excel97)
Steve,
My apologies. If I may add a few more lines . . .
The way I setup the IF statements in the work sheet to in order to carry out the daily test, I think, is somewhat convoluted. And, I was wondering if there would be a way to cut a few steps in the process. I have used IF and IF AND.
GCB

20030807, 00:30 #4
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
Re: Help on IF statements and nested IF statements (Excel97)
I don't completely understand your spreadsheet.
Crit A this seems true for 9 of the col J values are >5 (out of 11) so this is 81.8%) so this is true. This criteria is NOT for an indi stock but for ALL so how does this criteria combine with the others which are based on ind stocks. All stocks could be in compliance, but if this is not what do you want to do?
I don't understand the numbers from Q. When I check all your criteria, I get them ALL compliant so maybe I don't understand. I see that:
If J<=5 Compliant no matter what (you don't stipulate any other crit for <=5 billion (row 13 & 21)
If 5<Col J <=50 then G must be <20% (row 15 and 22 meet both) Crit B
If Col J>50 then G must be <25% (this is true for all the rest) Crit C
You seem to use Col Q, where some are compliant and others are NOT, but I don't see how that relates to all the 3?
SOme more explanation, please.
Steve

20030807, 12:04 #5
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
Re: Help on IF statements and nested IF statements (Excel97)
A few formulas I came up with (Assuming my last note was correct!)
Crit A "At least 80% of the equities must be equal to or greater than a market capitalization of 5 Billion"
I assume this means that you want 80% of the values in Col J to be >5
This will test that condition:
<pre>=(COUNTIF(J12:J22,">5")/COUNT(J12:J22))>0.8</pre>
The other crit test the individual rows is to AND at the end you can AND all 11 of the statements from each row to see compliance:
Crit B: "if the stock has a market capitalization that is between 5 Billion and 50 Billion, then that position must not be greater than 20% of the total market value"
Means to me that If 5<Col J <=50 then G must be <20%
Crit C: "no stock with a market capitalization of greater than 50 Billion may be greater than 25% of the total market value"
To me means that If Col J>50 then G must be <25%
Not stated, (but seems obvious from your statments that:
If J<=5 Compliant no matter what the value in Col G
You can calculate the T/F of ALL 11 of these conditions and then AND that col or you can make an array formula if you do NOT need the individual results. The combined formula for cond B, C, and (my "D" = unstated) (this is an ARRAY formula confirm with ctrlshiftenter):
=AND(IF((J12:J22<=5)+(J12:J22>5)*(J12:J22<=50)*(F1 2:F22/SUM(F12:F22)<0.2)+(J12:J22>50)*(F12:F22/SUM(F12:F22)<0.25),TRUE,FALSE))
This will be TRUE if ALL the 11 stocks meet the crit BD. If ANY do NOT then it will be false. YOu can also combine this with crit A if desired into one formula(again this is an ARRAY formula, confirm with ctrlshiftenter):
=AND(IF((J12:J22<=5)+(J12:J22>5)*(J12:J22<=50)*(F1 2:F22/SUM(F12:F22)<0.2)+(J12:J22>50)*(F12:F22/SUM(F12:F22)<0.25),TRUE,FALSE),(COUNTIF(J12:J22,"> 5")/COUNT(J12:J22))>0.8)
You used the value in Q23 (=Sum(Q12:Q22)) by bringing it to E4. This number (based on YOUR CALCS) is the sum of the MarketValues (col F) when EITHER Col J was between 5 and 50 OR Col G is <0.2. This sum is: (this formula can replace the current E4, again ARRAY confirm with ctrlshiftenter):
<pre>=SUM(IF((J12:J22>5)*(J12:J22<50)+(F12:F22/SUM(F12:F22)<0.2),F12:F22,0))</pre>
With these formulas I list, you can get rid of any col >K and also the items in G11:I23. J11:J23 could be moved to col G (if desired), but the formulas above should be in place first since MOST USE this column.
Hope this helps,
Steve